Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire table and all concurrent connections showed a hung display till the time this INSERT finished.

Why does the entire table get locked and how do I overcome this problem in SQL Server 2005 Express?

Need more @RPK. With the table DDL and a sample of the inserts we can give you an accurate explanation of whats happening. Without it we're just guessing.
–
Mark Storey-SmithSep 30 '11 at 16:23

This question is too vague. I'm removing any reference to other DBMS's and limiting the responses to SqlServer. If the OP or any other reader wants to discuss the merits of this core concept on other platforms, then we should have it discussed once per platform. It is detrimental to make this a cartesian join, there will be too many different threads of conversation on one page.
–
jcolebrand♦Sep 30 '11 at 17:55

2 Answers
2

This answer may prove helpful to the original question but is primarily to address inaccurate information in other posts. It also highlights a section of nonsense in BOL.

And as stated for the INSERT documentation, it will acquire an
exclusive lock on the table. The only way a SELECT can be made against
the table is to use NOLOCK or set the isolation level of the
transaction.

The linked section of BOL states:

An INSERT statement always acquires an exclusive (X) lock on the table
it modifies, and holds that lock until the transaction completes. With
an exclusive (X) lock, no other transactions can modify data; read
operations can take place only with the use of the NOLOCK hint or read
uncommitted isolation level. For more information, see Locking in the
Database Engine.

NB: As of 2014-8-27 BOL has been updated to remove the incorrect statements quoted above.

Thankfully this is not the case. If it were so inserts to a table would occur serially and all readers would be blocked from the entire table until the insert transaction completes. That would make SQL Server as efficient a database server as NTFS. Not very.

Common sense suggests it cannot be so but as Paul Randall points out, "Do yourself a favour, trust no-one". If you can't trust anyone, including BOL, I guess we'll just have to prove it.

Create a database and populate a dummy table with a bunch of rows, noting the DatabaseId returned.

RangeInsert-NullResource on the clustered index entry for the value being inserted

Exclusive lock on key

The locks are then released in reverse order. At no point has an exclusive lock been acquired on the table.

But this is just one batch inserting! That's not the same as two,
three or dozens running in parallel.

Yes it is. SQL Server (and arguably any relational database engine) has no foresight as to what other batches may be running when it processes a statement and/or batch, so the sequence of lock acquisition does not vary.

What about higher isolation levels e.g. Serializable?

For this particular example exactly the same locks are taken. Don't trust me, try it!

Depending on the current transaction isolation level settings, many
resources acquired to support the Transact-SQL statements issued by
the connection are locked by the transaction until it is completed
with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement.

And as stated for the INSERT documentation, it will acquire an exclusive lock on the table. The only way a SELECT can be made against the table is to use NOLOCK or set the isolation level of the transaction.

Hadn't noticed that rather badly worded statement in BOL before. An exclusive lock on something within the resource hierarchy will be required but it most definitely is not always the table.
–
Mark Storey-SmithSep 30 '11 at 16:20

6

-1 for the docs (not your fault) - it is easy to prove this is not true in snapshot isolation so the blanket "always acquires an exclusive (X) lock" is wrong. Not sure about other isolation levels.
–
Jack Douglas♦Oct 3 '11 at 9:54