Lock Escalation (Database Engine)
Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.
Applies to: SQL Server 2008 R2 and higher versions.
As the SQL Server Database Engine acquires low-level locks, it also places intent locks on the objects that contain the lower-level objects:
When locking rows or index key ranges, the Database Engine places an intent lock on the pages that contain the rows or keys.
When locking pages, the Database Engine places an intent lock on the higher level objects that contain the pages. In addition to intent lock on the object, intent page locks are requested on the following objects:
Leaf-level pages of nonclustered indexes
Data pages of clustered indexes
Heap data pages
The Database Engine might do both row and page locking for the same statement to minimize the number of locks and reduce the likelihood that lock escalation will be necessary. For example, the Database Engine could place page locks on a nonclustered index (if enough contiguous keys in the index node are selected to satisfy the query) and row locks on the data.
To escalate locks, the Database Engine attempts to change the intent lock on the table to the corresponding full lock, for example, changing an intent exclusive (IX) lock to an exclusive (X) lock, or an intent shared (IS) lock to a shared (S) lock). If the lock escalation attempt succeeds and the full table lock is acquired, then all heap or B-tree, page (PAGE), or row-level (RID) locks held by the transaction on the heap or index are released. If the full lock cannot be acquired, no lock escalation happens at that time and the Database Engine will continue to acquire row, key, or page locks.
The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.
Note
HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better.
If a lock escalation attempt fails because of conflicting locks held by concurrent transactions, the Database Engine will retry the lock escalation for each additional 1,250 locks acquired by the transaction.
Each escalation event operates primarily at the level of a single Transact-SQL statement. When the event starts, the Database Engine attempts to escalate all the locks owned by the current transaction in any of the tables that have been referenced by the active statement provided it meets the escalation threshold requirements. If the escalation event starts before the statement has accessed a table, no attempt is made to escalate the locks on that table. If lock escalation succeeds, any locks acquired by the transaction in a previous statement and still held at the time the event starts will be escalated if the table is referenced by the current statement and is included in the escalation event.
For example, assume that a session performs these operations:
Begins a transaction.
Updates TableA. This generates exclusive row locks in TableA that are held until the transaction completes.
Updates TableB. This generates exclusive row locks in TableB that are held until the transaction completes.
Performs a SELECT that joins TableA with TableC. The query execution plan calls for the rows to be retrieved from TableA before the rows are retrieved from TableC.
The SELECT statement triggers lock escalation while it is retrieving rows from TableA and before it has accessed TableC.
If lock escalation succeeds, only the locks held by the session on TableA are escalated. This includes both the shared locks from the SELECT statement and the exclusive locks from the previous UPDATE statement. While only the locks the session acquired in TableA for the SELECT statement are counted to determine if lock escalation should be done, once escalation is successful all locks held by the session in TableA are escalated to an exclusive lock on the table, and all other lower-granularity locks, including intent locks, on TableA are released.
No attempt is made to escalate locks on TableB because there was no active reference to TableB in the SELECT statement. Similarly no attempt is made to escalate the locks on TableC, which are not escalated because it had not yet been accessed when the escalation occurred.
Lock Escalation Thresholds
Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option, and when either of the following conditions exists:
A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.
A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO.
The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.
Escalation Threshold for a Transact-SQL Statement
When the Database Engine checks for possible escalations at every 1250 newly acquired locks, a lock escalation will occur if and only if a Transact-SQL statement has acquired at least 5000 locks on a single reference of a table. Lock escalation is triggered when a Transact-SQL statement acquires at least 5,000 locks on a single reference of a table. For example, lock escalation is not triggered if a statement acquires 3,000 locks in one index and 3,000 locks in another index of the same table. Similarly, lock escalation is not triggered if a statement has a self join on a table, and each reference to the table only acquires 3,000 locks in the table.
Lock escalation only occurs for tables that have been accessed at the time the escalation is triggered. Assume that a single SELECT statement is a join that accesses three tables in this sequence: TableA, TableB, and TableC. The statement acquires 3,000 row locks in the clustered index for TableA and at least 5,000 row locks in the clustered index for TableB, but has not yet accessed TableC. When the Database Engine detects that the statement has acquired at least 5,000 row locks in TableB, it attempts to escalate all locks held by the current transaction on TableB. It also attempts to escalate all locks held by the current transaction on TableA, but since the number of locks on TableA is < 5000, the escalation will not succeed. No lock escalation is attempted for TableC because it had not yet been accessed when the escalation occurred.
Escalation Threshold for an Instance of the Database Engine
Whenever the number of locks is greater than the memory threshold for lock escalation, the Database Engine triggers lock escalation. The memory threshold depends on the setting of the locks configuration option:
If the locks option is set to its default setting of 0, then the lock escalation threshold is reached when the memory used by lock objects is 24 percent of the memory used by the Database Engine, excluding AWE memory. The data structure used to represent a lock is approximately 100 bytes long. This threshold is dynamic because the Database Engine dynamically acquires and frees memory to adjust for varying workloads.
If the locks option is a value other than 0, then the lock escalation threshold is 40 percent (or less if there is a memory pressure) of the value of the locks option.
The Database Engine can choose any active statement from any session for escalation, and for every 1,250 new locks it will choose statements for escalation as long as the lock memory used in the instance remains above the threshold.
Escalating Mixed Lock Types
When lock escalation occurs, the lock selected for the heap or index is strong enough to meet the requirements of the most restrictive lower level lock.
For example, assume a session:
Begins a transaction.
Updates a table containing a clustered index.
Issues a SELECT statement that references the same table.
The UPDATE statement acquires these locks:
Exclusive (X) locks on the updated data rows.
Intent exclusive (IX) locks on the clustered index pages containing those rows.
An IX lock on the clustered index and another on the table.
The SELECT statement acquires these locks:
Shared (S) locks on all data rows it reads, unless the row is already protected by an X lock from the UPDATE statement.
Intent Share locks on all clustered index pages containing those rows, unless the page is already protected by an IX lock.
No lock on the clustered index or table because they are already protected by IX locks.
If the SELECT statement acquires enough locks to trigger lock escalation and the escalation succeeds, the IX lock on the table is converted to an X lock, and all the row, page, and index locks are freed. Both the updates and reads are protected by the X lock on the table.
Reducing Locking and Escalation
In most cases, the Database Engine delivers the best performance when operating with its default settings for locking and lock escalation. If an instance of the Database Engine generates a lot of locks and is seeing frequent lock escalations, consider reducing the amount of locking by:
Using an isolation level that does not generate shared locks for read operations.
READ COMMITTED isolation level when the READ_COMMITTED_SNAPSHOT database option is ON.
SNAPSHOT isolation level.
READ UNCOMMITTED isolation level. This can only be used for systems that can operate with dirty reads.
Note
Changing the isolation level affects all tables on the instance of the Database Engine.
Using the PAGLOCK or TABLOCK table hints to have the Database Engine use page, heap, or index locks instead of row locks. Using this option, however, increases the problems of users blocking other users attempting to access the same data and should not be used in systems with more than a few concurrent users.
For partitioned tables, use the LOCK_ESCALATION option of ALTER TABLEto escalate locks to the HoBT level instead of the table or to disable lock escalation.
You can also use trace flags 1211 and 1224 to disable all or some lock escalations. For more information, see Trace Flags (Transact-SQL). Also, monitor lock escalation by using the SQL Server Profiler Lock:Escalation event; and see Using SQL Server Profiler.