If many locks of the same granularity are held during a transaction, the Database Engine automatically upgrades these locks into a table lock. This process of converting many page-, row-, or index-level locks into one table lock is called lock escalation. The escalation threshold is the boundary at which the database system applies the lock escalation. Escalation thresholds are determined dynamically by the system and require no configuration. (Currently, the threshold boundary is 5000 locks.)
The general problem with lock escalation is that the database server decides when to escalate a particular lock, and this decision might be suboptimal for applications with different requirements.
You can use the ALTER TABLE statement to change the lock escalation mechanism. This statement supports the TABLE option with the following syntax:
The TABLE option is the default value and specifies that lock escalation will be done at table-level granularity. The AUTO option allows the Database Engine to select the lock escalation granularity that is appropriate for the table schema. Finally, the DISABLE option allows you to disable lock escalation in most cases. (There are some cases in which the Database Engine must take a table lock to protect data integrity.)
Example 1 disables the lock escalation for the employee table.
EXAMPLE 1
Leave a Reply