Lock Escalation

SQL Server Database Engine acquires low-level locks, it also places intent locks on the objects that contain the lower-level objects:

•When locking rows or index key ranges, the Database Engine places an intent lock on the pages that contain the rows or keys.

•When locking pages, the Database Engine places an intent lock on the higher level objects that contain the pages. In addition to intent lock on the object, intent page locks are requested on the following objects:

◦Leaf-level pages of nonclustered indexes

◦Data pages of clustered indexes

◦Heap data pages

Reducing Lock Escalation

•Using an isolation level that does not generate shared locks for read operations like Read Commited
•Using trace flags 1211 and 1224 to disable all or some lock escalations