INF: How Tunable Lock Escalation Works

• Microsoft SQL Server, versions 6.0 & 6.5

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 max LEmin : LE threshold min 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

   TABesc = min { max ( LEmin, LEpercent*Total) , min ( LEmax,

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.