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.

Answer 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.