Lock Sequences In SQL Server

Question

Does anyone know of a resource that will tell me the sequence of locks that will be taken out on a table/page/row/index during a select/insert/update/delete in SQL Server 2005 AND how different table hints and isolation levels will impact the locks taken?

I know I am asking a lot here, but surely this information must be documented somewhere?

Thanks in advance.

Answer 1

SQL Server locking is based on the concepts in Transaction Processing: Concepts and Techniques. This book explains in great detail how locks are to be acquired, what locks are needed and why things must be the way they are.

The resources Marc linked are good coverage on the topic, but the details are scattered and you need to know where to look. Here is a primer to start you up:

The transaction isolation levels only affect read locks. Under normal read committed when reading a row an S-lock is acquired that is released immediately after the read. If the isolation level is elevated to repeatable read then the S-locks are held until the transaction ends. On higher serializable level range locks are placed instead of simple row locks, and they are held until the transaction commits. The snapshot modes are different in that they don't necessarily affect the type of lock, but the source of the read: rows are retrieved from the version store instead.

Lock order/hierarchy is always the same:

• an Sch-S lock is placed on the metadata at the start of any DML operation. DDL operations require Sch-M locks and thus conflict, so DML can be assured of the 'stability' of the schema on which it operates (object schema, not database schema...).
• The lock hierarchy path to a row is table-page-row. The actual granularity decided by the engine is dynamic. Typically is row.
• No matter the granularity, the path to the actual locked resource is protected with intent locks. Ie. to S-lock a row, the reader must acquire IS-lock on the table and the page. To S-lock a page, it needs an IS-lock on table.
• Single partition operations acquiring more that 5000 locks on a scan may trigger lock escalation. Escalation is always an attempt (ie. will never block if failed). Escalation in practice goes always from row-level locking to table (partition in 2008) level locking.

The lock hints can never change the order of locks, they can only change:

• the type of lock (U-lock or X-lock when an S-lock would be required)
• the granularity (enforce table, or page or row)
• the duration (hold S-locks)
• the blocking behavior (readpast to skip incompatible rows).

I did not talk too much about insert/update/deletes since they are quite uninteresting: they require X locks, that's it. The only interesting thing about it is the way update works because it first acquire an U-lock that is later converted to an X-lock. This behavior is needed to leverage the U-lock asymmetry that allows pending S-locks to drain before the update proceeds.

With this I hope you can go and find all the details left out from the articles and books linked.