CODEWITHSUNDEEP
sql-serverlockingbulkinsert
mvillegascuellar
mvillegascuellar

Reputation: 31

bulk insert blocks nolock query

I have a SSIS package that is truncating and then inserting a whole table from serverA to serverB, however I am trying to view the progress of the job by performing a select with a NOLOCK hint on serverB but it gets lock by the "BULK INSERT" performed by the SSIS package.

I have review the "sys.dm_tran_locks" and found that the "BULK INSERT" is holding a "Sch-M" lock on the table, and I don't understand why.

Please can you help me with this?

Thanks.

Upvotes: 2

Views: 4119

Answers (2)

brian
brian

Reputation: 3695

The Sch-M lock indicates that you have at least one index on the destination table.

See "Table Locking and Logging During Bulk Import":

http://msdn.microsoft.com/en-us/library/ms177445%28v=sql.105%29.aspx

Based on the table in the reference, Sch-M will be used when you are bulk inserting into:

  1. A Heap with one non-clustered index and tablock is used
  2. An empty B-Tree (clustered index) and tablock is used

Also, if the load is done correctly, the truncate will be completed (and it's Sch-M lock gone) before the bulk insert begins.

The bulk update (BU) lock is the most optimal lock, but don't expect to be reading the table during the bulk insert:

Bulk update (BU) locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table. http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx

Use Remus suggestion (sys.allocation_units) for checking progress.

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294407

Truncate requires SCH-M for deallocating the table allocation units. Among other, this lock serves the very purpose of protecting against concurrent dirty reads. Had the truncate succeed in deallocating the table while dirty reads scanner are in it those scanner would all of a sudden find themselves reading pages that belong to no one (or worse, were reallocated to some other AU). As with any write lock, the 2PL protocol requires to be held until end of transaction. Subsequent dirty scanners will block behind it, as they should.

If you do not have the TRUNCATE/BULK INSERT wrapped in a transaction then there are other scenarios that could cause BULK INSERT itself to go SCH-M instead of X (I think table lock hint on bulk insert on an empty table (no AU root page) will cause this).

Too peek at the progress of the BULK INSERT check how many pages are allocated to your target in sys.allocation_units. This will give you a rough estimate of percent complete.

Upvotes: 1

Related Questions