Export (0) Print
Expand All

Controlling the Locking Behavior

SQL Server 2000

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.

See Also

bcp Utility

BULK INSERT.

sp_tableoption

Understanding Locking in SQL Server

Show:
© 2014 Microsoft