Lock Escalation

Hi Manoj,

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.

As the 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:
        

1.Leaf-level pages of nonclustered indexes
       
2.Data pages of clustered indexes
       
3.Heap data pages


Refer

http://msdn.microsoft.com/en-us/library/ms184286.aspx

http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/291/sql-server-2008-lock-escalation-explained

http://blogs.msdn.com/b/axinthefield/archive/2011/06/25/sql-server-lock-escalation-and-blocking.aspx


Thanks