INF: Tunable Lock Escalation in SQL Server 6.0 and 6.5 (151116)

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" occurs 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.