Controlling Locking Behavior for Bulk Import
The bcp command, BULK INSERT statement, and INSERT ... SELECT * FROM OPENROWSET(BULK...) statement let you specify that the table is to be locked during a bulk-import operation. Locking is used by the SQL Server Database Engine to synchronize simultaneous access by multiple users to the same piece of data. When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. When you specify table locking for a bulk import operation, a bulk update (BU) lock is taken on the table for the duration of the bulk-import operation. A BU lock allows multiple threads to bulk import data concurrently into the same table while preventing other processes that are not bulk importing data from accessing the table. Table locking can improve performance of the bulk-import operation by reducing lock contention on the table.
If table locking is not used, 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-import operation.
Table lock on bulk import |
Table locking behavior |
|---|---|
Off |
Row-level locks used |
On |
Table-level lock used |
If table locking is specified, the default setting for the table set with sp_tableoption is overridden for the duration of the bulk-import operation.
Note
It is not necessary to use table-locking to bulk-import data into a table from multiple clients in parallel, but doing so can improve performance.
Command Qualifiers
The following table summarizes the qualifiers for specifying table-locking in bulk-import commands.
Command |
Qualifier |
Qualifier type |
|---|---|---|
bcp |
-h"TABLOCK" |
Hint |
BULK INSERT |
TABLOCK |
Argument |
INSERT ... SELECT * FROM OPENROWSET(BULK...) |
WITH(TABLOCK) |
Table hint |
Note
In SQL Server 2005 and later versions, specifying TABLOCK on a table with a clustered index prevents bulk importing data in parallel. If you want to perform a parallel bulk import in this case, do not use TABLOCK. For more information about bulk loading in parallel, see Guidelines for Optimizing Bulk Import.