INF: How Tunable Lock Escalation Works

ID: Q151116

The information in this article applies to:

SUMMARY

Microsoft SQL Server versions 6.0 and 6.5 provide tunable lock escalation. This means that when SQL Server has to use more page locks for executing a single statement, it will automatically escalate to a table lock based on the lock escalation configuration parameters.

This article helps explain how many page locks will be acquired before "lock escalation" will occur for a given set of run time configuration values as defined by sp_configure.


MORE INFORMATION

The following is the decision tree which is used to determine when the number of page locks held on a table should be escalated to a TABLE lock.

Abbreviations used are :
LEmax : LE threshold maximum
LEmin : LE threshold minimum
LEpercent : LE threshold percent
TABesc : number of pages when lock escalation occurs
Total : Number of table pages 


if ( LEpercent = 0 )   then
   TABesc = LEmax -- LE threshold min has no significance
else
   TABesc = min { max ( LEmin, LEpercent*Total) , min ( LEmax,
LEpercent*Total) }

NOTES:

In the algorithm discussed above, extent locks do not count toward the "lock escalation."

Also, the above applies to individual statements and not to transactions.

Additional query words: 


Keywords          : kbusage SSrvLock 
Version           : winnt:6.0,6.5
Platform          : winnt 
Issue type        : kbhowto

Last Reviewed: August 12, 1999