What Lock Escalation Is and How to Stop It

A large change can trigger lock escalation and affect sessions that touch different rows in the same table. Confirm that escalation actually occurred before treating every blocking problem as the same issue.

Understand the Change in Lock Granularity

SQL Server can replace many fine-grained locks with a coarser lock to reduce lock-management overhead. Traditional escalation commonly moves row or page locks to a table lock. Supported partitioned-table configurations can use partition-level escalation.

The familiar 5,000 figure is a clue, not a promise that the 5,000th changed row triggers escalation. Locks and rows are different counts. Memory pressure and the statement's locking behavior also matter.

An attempted escalation can fail when an incompatible lock prevents it. SQL Server can continue using finer locks and retry later. Do not infer the complete sequence from the final number of rows affected.

Confirm the Event and the Blocking

Use an Extended Events session with the lock_escalation event to capture evidence during the relevant workload. Filter and retain the session appropriately for the investigation. A table lock alone does not prove that escalation created it.

SELECT name, description
FROM sys.dm_xe_objects
WHERE object_type = 'event'
  AND name = N'lock_escalation';
SELECT session_id, blocking_session_id, wait_type, wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

The first query discovers the event; it does not capture occurrences. The second shows current blocked requests. Correlate an actual captured escalation with the affected object, session, and time window.

SELECT request_session_id, resource_type,
       request_mode, request_status,
       resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
ORDER BY request_session_id, resource_type;

Monitoring other sessions requires suitable permissions. This snapshot can be large on a busy database, so narrow it to known sessions when possible. Interpret intent locks separately from shared or exclusive object locks.

Reduce the Work Inside Each Transaction

When the business operation allows partial progress, commit bounded batches instead of one enormous change. The commit boundary matters more than placing TOP inside one outer transaction. A long outer transaction can retain the problem across all batches.

CREATE TABLE #EscalationBatchDemo
(
    RowId int PRIMARY KEY,
    Processed bit NOT NULL
);
INSERT #EscalationBatchDemo VALUES
(1,0),(2,0),(3,0),(4,0),(5,0);
-- Run with no surrounding transaction and implicit transactions OFF.
WHILE 1 = 1
BEGIN
    UPDATE TOP (2) #EscalationBatchDemo
    SET Processed = 1
    WHERE Processed = 0;
    IF @@ROWCOUNT = 0 BREAK;
END;
SELECT * FROM #EscalationBatchDemo ORDER BY RowId;

This tiny example demonstrates independently committed batch structure, not an escalation reproduction. A production process also needs a suitable key, efficient predicate, and restart behavior. Choose a batch size from measurement rather than copying the example's value.

Batching changes atomicity when it replaces one transaction with several. Confirm that the business accepts intermediate states. Some operations must remain atomic and need a different solution.

Improve the Access Path

A statement may examine or lock far more rows than it ultimately changes. Review the actual plan, predicates, and indexes before altering locking settings. Better access paths can reduce both elapsed time and the lock footprint.

Make predicates searchable and keep statistics appropriate for the workload. Consider lookup-heavy plans and unnecessary scans during the investigation. An index is justified by the complete workload, including its write and storage costs.

SELECT SCHEMA_NAME(schema_id) AS schema_name,
       name, lock_escalation_desc
FROM sys.tables
WHERE is_ms_shipped = 0
ORDER BY schema_name, name;

This reads each table's escalation setting. It does not show whether escalation occurred or whether changing the setting would help. Keep configuration evidence separate from event evidence.

Check Modern Locking Behavior

SQL Server 2025 offers optimized locking, which reduces lock memory and escalation pressure for supported workloads. It requires accelerated database recovery and is disabled by default on SQL Server. Review its prerequisites and behavior before testing adoption.

Database settings and isolation choices therefore matter when comparing two servers. A traditional locking demonstration may not describe a database using optimized locking. Record the actual configuration with the evidence.

Do not assume ROWLOCK prevents escalation or NOLOCK solves write contention. Broadly disabling escalation can increase lock-memory pressure. Use targeted diagnosis instead of replacing one resource problem with another.

Verify the Fix Under Concurrency

Repeat the representative workload with concurrent readers and writers after the proposed change. Compare escalation events, blocking duration, throughput, and correctness. A faster isolated update does not prove that the shared workload improved.

Keep the smallest change that resolves the demonstrated problem. Document any altered transaction semantics or operating requirements. The goal is dependable concurrency, not eliminating every table lock.

Lock escalation is not proof that SQL Server chose badly, it is a signal to examine transaction size and access patterns.

This post was rewritten from scratch in September 2026. The original, published on 2011-09-01, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.