Resolve blocking problems caused by lock escalation in SQL Server

Applies to:   SQL Server

Original KB number:   323630

Summary

Lock escalation converts many fine-grained locks (such as row or page locks) into a single table lock. SQL Server triggers escalation dynamically based on lock count or lock memory, and its default behavior improves throughput in most workloads. However, some query or transaction designs cause escalation at times when the resulting table lock blocks other users. This article explains how to determine whether lock escalation is causing blocking in SQL Server, how to prevent it, and how (and when) to disable it.

Determine whether lock escalation is causing blocking

Lock escalation doesn't cause most blocking problems. To determine whether lock escalation is occurring at or near the time when you experience blocking problems, start an Extended Events session that includes the lock_escalation event. If you don't see any lock_escalation events, lock escalation isn't occurring on your server, and the information in this article doesn't apply to your situation.

If lock escalation is occurring, verify that the escalated table lock is blocking other users.

For more information about how to identify the head blocker and the lock resource that's held by the head blocker and that's blocking other server process IDs (SPIDs), see Understand and resolve SQL Server blocking problems.

If the lock that's blocking other users is anything other than a TAB (table-level) lock that has a lock mode of S (shared) or X (exclusive), lock escalation isn't the problem. In particular, if the TAB lock is an intent lock (such as a lock mode of IS, IU, or IX), it isn't caused by lock escalation. If your blocking problems aren't caused by lock escalation, see the Understand and resolve SQL Server blocking problems troubleshooting steps.

Prevent lock escalation

The simplest and safest method to prevent lock escalation is to keep transactions short and reduce the lock footprint of expensive queries so that the lock escalation thresholds aren't exceeded. Use one or more of the following strategies.

Break large operations into smaller transactions

Break up large batch operations into several smaller operations. For example, you run the following query to remove more than 100,000 old records from an audit table, and then you determine that the query caused a lock escalation that blocked other users:

DELETE FROM LogMessages WHERE LogDate < '20020102';

By removing these records a few hundred at a time, you can dramatically reduce the number of locks that accumulate per transaction. This approach prevents lock escalation. For example, you run the following query:

DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;

Optimize queries and indexes to reduce locks

Reduce the query's lock footprint by making the query as efficient as possible. Large scans or many bookmark lookups can increase the chance of lock escalation. Additionally, they increase the chance of deadlocks, and adversely affect concurrency and performance. After you identify the query that causes lock escalation, look for opportunities to create new indexes or to add columns to an existing index to remove index or table scans and to maximize the efficiency of index seeks. Review the execution plan and potentially create new nonclustered indexes to improve query performance. For more information, see SQL Server Index Architecture and Design Guide.

One goal of this optimization is to make index seeks return as few rows as possible to minimize the cost of bookmark lookups (maximize the selectivity of the index for the query). If SQL Server estimates that a Bookmark Lookup logical operator will return many rows, it might use a PREFETCH clause to do the bookmark lookup. If SQL Server does use PREFETCH for a bookmark lookup, it must increase the transaction isolation level of a portion of the query to "repeatable read" for a portion of the query. This means that what may look like a SELECT statement at a "read-committed" isolation level might acquire many thousands of key locks (on both the clustered index and one nonclustered index). This can cause such a query to exceed the lock escalation thresholds. This is especially important if you find that the escalated lock is a shared table lock, although these are not commonly seen at the default "read-committed" isolation level. If a Bookmark Lookup WITH PREFETCH clause is causing the escalation, consider adding columns to the nonclustered index that appears in the Index Seek, or the Index Scan logical operator below the Bookmark Lookup logical operator in the query plan. It might be possible to create a covering index (an index that includes all columns in a table that were used in the query), or at least an index that covers the columns that were used for join criteria or in the WHERE clause if it's impractical to include everything in the "select column" list.

A Nested Loop join might also use PREFETCH, and this causes the same locking behavior.

Hold a compatible table-level lock on a separate connection

Lock escalation can't occur if a different SPID is currently holding an incompatible table lock. Lock escalation always escalates to a table lock, and never to a page lock. Additionally, if a lock escalation attempt fails because another SPID holds an incompatible TAB lock, the query that tried the escalation doesn't block while waiting for a TAB lock. Instead, it continues to acquire locks at its original, more granular level (row, key, or page), periodically making additional escalation attempts.

Therefore, one method to prevent lock escalation on a particular table is to acquire and hold a lock on a different connection that isn't compatible with the escalated lock type. An IX (intent exclusive) lock at the table level doesn't lock any rows or pages, but it's still not compatible with an escalated S (shared) or X (exclusive) TAB lock. For example, assume that you must run a batch job that modifies many rows in the mytable table and that caused blocking because of lock escalation. If this job always finishes in less than one hour, you might create a Transact-SQL job that contains the following code, and schedule the new job to start several minutes before the batch job start time:

BEGIN TRAN;
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;

This query acquires and holds an IX lock on mytable for one hour. This lock prevents lock escalation on the table during that time. This batch doesn't modify any data or block other queries (unless the other query forces a table lock by using the TABLOCK hint or if an administrator has disabled page or row locks by using ALTER INDEX).

Ensure predicates are SARGable

Eliminate lock escalation caused by lack of SARGability, a relational database term used to describe whether a query can use indexes for predicates and join columns. For more information on SARGability, see Inside Design Guide Query Considerations. For example, a fairly simple query that doesn't appear to be requesting many rows - or perhaps a single row - may still end up scanning an entire table or index. This situation might occur if there's a function or computation in the left side of a WHERE clause. Such examples that lack SARGability include implicit or explicit data type conversions, the ISNULL() system function, a user-defined function with the column passed as a parameter, or a computation on the column, such as WHERE CONVERT(INT, column1) = @a or WHERE Column1*Column2 = 5. In such cases, the query can't SEEK the existing index, even if it contains the appropriate columns, because all column values must be retrieved first and passed to the function. This leads to a scan of the entire table or index and results in acquisition of a large number of locks. In such circumstances, SQL Server can reach the lock count escalation threshold. The solution is to avoid using functions against columns in the WHERE clause, ensuring SARGable conditions.

Disable lock escalation

Although it's possible to disable lock escalation in SQL Server, we don't recommended it. Instead, use the prevention strategies that are described in the Prevent Lock Escalation section.

• Table level: You can disable lock escalation at the table level. See ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). To determine which table to target, examine the T-SQL queries. If that's not possible, use Extended events, enable the lock_escalation event, and examine the object_id column. Alternatively, use the Lock:Escalation event and examine the ObjectID2 column by using SQL Profiler.
• Instance Level: You can disable lock escalation by enabling either of the trace flags 1211 or 1224 or both for the instance. However, these trace flags disable all lock escalation globally in the instance of SQL Server. Lock escalation serves a useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite. Therefore, if you disable lock escalation, and the lock memory grows large enough, any attempt to allocate additional locks for any query might fail and generate the following error entry:

Error: 1204, Severity: 19, State: 1
The SQL Server can't obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

You can add these trace flags (-T1211 or -T1224) by using SQL Server Configuration Manager. You must restart the SQL Server service for a new startup parameter to take effect. If you run the DBCC TRACEON (1211, -1) or DBCC TRACEON (1224, -1) query, the trace flag takes effect immediately.
However, if you don't add the -T1211 or -T1224 as a startup parameter, the effect of a DBCC TRACEON command is lost when the SQL Server service is restarted. Turning on the trace flag prevents any future lock escalations, but it doesn't reverse any lock escalations that have already occurred in an active transaction.

If you use a lock hint, such as ROWLOCK, this only alters the initial lock plan. Lock hints don't prevent lock escalation.

Lock escalation thresholds

Lock escalation may occur under one of the following conditions:

• Memory threshold is reached - A memory threshold of 40 percent of lock memory is reached. When lock memory exceeds 24 percent of the buffer pool, a lock escalation can be triggered. Lock memory is limited to 60 percent of the visible buffer pool. The lock escalation threshold is set at 40 percent of the lock memory. This is 40 percent of 60 percent of the buffer pool, or 24 percent. If lock memory exceeds the 60 percent limit (this is much more likely if lock escalation is disabled), all attempts to allocate additional locks fail, and 1204 errors are generated.

• A lock threshold is reached - After memory threshold is checked, the number of locks acquired on the current table or index is assessed. If the number exceeds 5,000, a lock escalation is triggered.

To understand which threshold was reached, use Extended events, enable the lock_escalation event, and examine the escalated_lock_count and escalation_cause columns. Alternatively, use the Lock:Escalation event, and examine the EventSubClass value, where "0 - LOCK_THRESHOLD" indicates that the statement exceeded the lock threshold, and "1 - MEMORY_THRESHOLD" indicates that the statement exceeded the memory threshold. Also, examine the IntegerData and IntegerData2 columns.

Recommendations

The methods discussed in the Prevent Lock Escalation section are better options than disabling escalation at the table or instance level. Additionally, the preventive methods generally produce better performance for the query than disabling lock escalation. Microsoft recommends that you enable this trace flag only to mitigate severe blocking that lock escalation causes while other options, such as those discussed in this article, are being investigated.

Related content

• Understand and resolve SQL Server blocking problems
• Troubleshoot slow-running queries on SQL Server
• Transaction Locking and Row Versioning Guide
• ALTER TABLE (Transact-SQL)