Locking is a mechanism which is internally handled by SQL Server. This locking technology helps synchronize access of same piece of data by multiple users.
A piece of data is being modified by a transaction triggered by USER A will be accessible only to the USERA and not for others. For achieve this, SQL Server’s Database Engine’s LOCK Manager Component is performing locking methods with different level of locking. The Data can be locked in lowest level at Row level and be at the highest level at Database Level.
The hierarchy starts database level at the top and further drill down to schema, tables, table partitions, extents, pages and finally at the bottom level to the individual ROW. By default SQL Server starts lock from the bottom level starts with row level locking. Each locking uses some memory resources in SQL Server Box. If a heap or B-Tree’s locks threshold crosses 5000 Row level locks counts by a single transaction then SQL Server automatically locks the whole table that is instead of having 5000 different row level locks for a single table, It locks whole table with a single lock. This process of converting many fine-grain locks into fewer coarse-grain locks is calling Lock Escalation.
Lock Escalation reduces system overhead and also increase the probability of concurrency contention.
In practical we often use the table hints in our Select Update Or Delete Command with in transactions. The Table_hints like NOLOCK, HOLDLOCK, PAGLOCK, UPDLOCK and TABLOCK are specifying locking modes of a particular heap or B-Tree for the transaction.
As the life time of a lock structure is equivalent to the life time of a transaction, in-completed transactions may hold large number of locks and these locks may cause severe blockings. For information please visit :
Incomplete transaction may hold large number of locks and cause blocking
We can use DBCC OPENTRAN command to find out the open transactions in the instance.
For More detailed study about the lock escalation please go through the following links
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx
http://blogs.technet.com/mat_stephen/archive/2005/01/31/363803.aspx
http://support.microsoft.com/kb/323630
