Preventing entire table from locking while bulk INSERT

Question

I have a stored procedure that performs a bulk insert in a table. I added BEGIN TRANSACTION command just above the INSERT query to enable ROLL BACK if something goes wrong. When the bulk insert initiated, it locked the entire table and other users were unable to execute SELECT on the same table.

I am not following why SQL Server locks entire table for even a SELECT.

I am using SQL Server 2005 Express. Is this a problem with this version or it persists in 2008 as well? How to overcome this situation? Writers should not block Readers.

Solution 1

You have an open transaction. That means SQL Server needs to preserve the state of the table, and any changes you are in the process of making are "dirty" and uncommitted.

If you SELECT from a table that is currently being altered with an open (explicit) transaction, the SELECT will wait until the table is in a stable state and the transaction has been either committed or rolled back.

To get around this, you can alter the transaction isolation level on the SELECT query.

Solution 2

Writers should not block Readers

This is true only for snapshot isolation, all other isolation levels require both readers to block writes and writers to block readers (dirty reads not considered, since they are inconsistent and should never be used). If you need this behavior, then use row versioning (the link contains the solution).

Why does bulk insert lock the entire table?

This actually may or may not be true. The behavior is under your control:

TABLOCK

Specifies that a table-level lock is acquired for the duration of the bulk-import operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load.

For more details, read the product specifications: Controlling Locking Behavior for Bulk Import.