Controlling the Locking Behavior
The bcp utility and BULK INSERT statement accept the TABLOCK hint, which allows the user to specify the locking behavior used. TABLOCK specifies that a bulk update table-level lock is taken for the duration of the bulk copy. Using TABLOCK can improve performance of the bulk copy operation due to reduced lock contention on the table. For example, to bulk copy data from the Authors.txt data file to the authors2 table in the pubs database, specifying a table-level lock, execute from the command prompt:
bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppassword -h "TABLOCK"
Alternatively, you can use the BULK INSERT statement from a query tool, such as SQL Query Analyzer, to bulk copy data:
BULK INSERT pubs..authors2 FROM 'c:\authors.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
TABLOCK
)
If TABLOCK is not specified, the default uses row-level locks, unless the table lock on bulk load option is set to on. Setting the table lock on bulk load option using sp_tableoption sets the locking behavior for a table during a bulk load.
| Table lock on bulk load | Table locking behavior |
|---|---|
| Off | Row-level locks used |
| On | Table-level lock used |
If the TABLOCK hint is specified, the default setting for the table set with sp_tableoption is overridden for the duration of the bulk load.
Note It is not necessary to use the TABLOCK hint to bulk load data into a table from multiple clients in parallel, but doing so can improve performance.