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.

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.