SQL Server Locks: A Performance and Deadlock Cheat Sheet

SQL Server uses locks to protect data while concurrent sessions read and modify it. Locks preserve transaction correctness, but they also create waiting, blocking, and deadlocks. When evaluating a slow query, ask: "Is it waiting for another transaction, and what resource does it need?"

This cheat sheet explains the lock types you are most likely to encounter, how they affect query execution, and how to diagnose locking problems quickly.

Locks are not latches. Locks protect logical data and transaction consistency. Latches are lightweight internal synchronization mechanisms that protect in-memory structures such as data pages. A lock problem commonly appears as an LCK_M_* wait; a latch problem uses waits such as PAGELATCH_*.

The basic locking flow

A lock request is granted when compatible with locks already held on the resource. Otherwise, the session waits. Lock duration depends on its mode, isolation level, statement, and transaction scope. A common issue is an application modifying data and then remaining idle before committing; the statement finished, but its locks remain.

Lock modes you should recognize

Lock	Name	Typical purpose	Performance implication
S	Shared	Reading data	Compatible with other shared locks, but blocked by exclusive locks
U	Update	Reading a row before updating it	Reduces conversion deadlocks; only one update lock can target a resource
X	Exclusive	Inserting, updating, or deleting data	Blocks both readers and writers under locking-based isolation
IS	Intent Shared	Signals shared locks at a lower level	Enables efficient compatibility checks
IX	Intent Exclusive	Signals exclusive or update locks below	Common while rows or pages are modified
SIX	Shared with Intent Exclusive	Reads the larger resource while modifying lower-level resources	Restrictive; can create broad blocking
Sch-S	Schema Stability	Compiling and executing queries	Compatible with normal data access, but conflicts with schema modification
Sch-M	Schema Modification	DDL and some metadata changes	Highly restrictive; can block nearly all access to the object
BU	Bulk Update	Bulk-loading data under qualifying options	Allows optimized bulk operations but restricts competing access
Range*	Key-range locks	Protects index ranges under SERIALIZABLE	Prevents phantom rows but can lock gaps and increase blocking

Shared, update, and exclusive locks

A shared lock allows concurrent readers. An exclusive lock prevents another session from reading or changing the resource under conventional READ COMMITTED locking. Update locks are an intermediate mode used when reading data that may be changed. They help avoid two sessions acquiring shared locks and both attempting to convert them to exclusive locks.

Intent locks

Intent locks describe activity lower in the lock hierarchy. An IX table lock usually means exclusive locks exist, or will exist, on pages, keys, or rows. It does not mean the whole table is exclusively locked. Intent modes quickly reveal whether a table-level request conflicts with lower-level activity.

Schema locks

Sch-S locks occur during query compilation and execution, even with NOLOCK. Sch-M locks occur during schema changes and conflict with Sch-S. A long query can delay DDL, while schema modification can block ordinary queries.

Key-range locks

Under SERIALIZABLE, SQL Server may lock index keys and gaps between them. This prevents inserts that would newly satisfy an earlier predicate. Key-range locks provide strong consistency but can greatly expand the protected area, especially without supporting indexes.

Lock granularity: what is being locked?

SQL Server can lock resources at several levels:

• KEY locks protect index entries. Even queries described as "row locking" often show KEY resources.
• RID locks protect rows in a heap, which is a table without a clustered index.
• PAGE locks protect an 8 KB data or index page.
• OBJECT locks protect a table or similar object.
• HoBT locks protect a heap or B-tree, including an individual partition.
• METADATA, DATABASE, and ALLOCATION_UNIT locks protect broader internal resources.

Fine-grained locks improve concurrency but consume memory. Coarser locks use fewer resources but block more operations.

Lock escalation

Lock escalation replaces many fine-grained locks with a coarser lock. SQL Server attempts escalation when a single statement acquires at least roughly 5,000 locks on one table or index reference, and it can also escalate under lock-memory pressure. Escalation is normally to a table lock; a partitioned table can escalate to a partition when LOCK_ESCALATION = AUTO. SQL Server does not escalate row or key locks to page locks.

If another session holds an incompatible lock that prevents escalation, the statement continues with fine-grained locks and SQL Server retries later. Escalation can turn a large modification into a whole-table blocker. Reduce its likelihood with shorter transactions, batched changes, and indexes that prevent unnecessary scans. Disabling escalation can exchange blocking for excessive lock memory.

Compatibility at a glance

The full compatibility matrix is extensive, but this reduced view covers common investigations:

Held \ Requested	S	U	X	IS	IX
S	Yes	Yes	No	Yes	No
U	Yes	No	No	Yes	No
X	No	No	No	No	No
IS	Yes	Yes	No	Yes	Yes
IX	No	No	No	Yes	Yes

Compatibility alone does not explain every wait. Locks may cover different resources, and a request can wait behind another waiter because SQL Server generally preserves queue fairness.

How isolation levels change locking

Isolation level	Important locking behavior
READ UNCOMMITTED	Allows dirty reads and usually avoids shared data locks; rows can be missing, duplicated, or inconsistent
READ COMMITTED	SQL Server default; shared row/page locks are generally released as data is read, allowing later rereads to change
REPEATABLE READ	Holds shared locks until transaction end, preventing changed or deleted rereads
SERIALIZABLE	Adds key-range protection and prevents phantoms; highest blocking risk
SNAPSHOT	Uses row versions for reads; update conflicts are possible; writes still use locks
READ COMMITTED SNAPSHOT	Changes READ COMMITTED reads to use row versions; writers still lock

SNAPSHOT requires ALLOW_SNAPSHOT_ISOLATION to be enabled. Azure SQL Database commonly enables READ_COMMITTED_SNAPSHOT by default, while boxed SQL Server does not. Row versioning reduces reader-writer blocking but does not eliminate writer-writer blocking, schema conflicts, or poor transaction design. It also adds version-store usage, and competing snapshot updates can fail with error 3960.

NOLOCK is not a performance fix. It uses READ UNCOMMITTED, permitting dirty, missing, or duplicated rows, and it still acquires schema-stability locks. Use it only when those correctness risks are explicitly acceptable.

Blocking versus deadlocks

Blocking is expected synchronization: one session waits until another releases an incompatible lock. A deadlock is a cycle in which sessions wait for one another and none can proceed.

SQL Server detects the cycle and chooses a victim, which receives error 1205 and is rolled back. Applications should use bounded retries for idempotent operations, but retries do not fix recurring deadlocks.

Typical fixes include consistent object access order, shorter transactions, indexes that reduce scanned rows, no user interaction inside transactions, and smaller batches. The deadlock graph identifies participating processes, resources, lock modes, and statements.

Reproducible locking labs

Run these examples only on a development SQL Server where you can create a disposable database. They were validated on SQL Server 2022 Developer Edition. Open two query windows and label them Session A and Session B. Run this setup once; DROP TABLE IF EXISTS requires SQL Server 2016 or later.

USE master;
GO

IF DB_ID(N'LockLab') IS NULL
    CREATE DATABASE LockLab;
GO

USE LockLab;
GO

DROP TABLE IF EXISTS dbo.LockDemo;

CREATE TABLE dbo.LockDemo
(
    Id int NOT NULL
        CONSTRAINT PK_LockDemo PRIMARY KEY,
    AccountName varchar(30) NOT NULL,
    Balance int NOT NULL,
    ReferenceCode int NOT NULL
);

CREATE INDEX IX_LockDemo_ReferenceCode
    ON dbo.LockDemo (ReferenceCode);

INSERT dbo.LockDemo (Id, AccountName, Balance, ReferenceCode)
VALUES
    (1, 'Alpha', 100, 10),
    (2, 'Beta', 200, 20),
    (3, 'Gamma', 300, 30);

SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();

Lab 1: an exclusive lock blocks a reader

In Session A, update a row but leave the transaction open:

BEGIN TRANSACTION;

UPDATE dbo.LockDemo
SET Balance = Balance + 50
WHERE Id = 1;

-- Leave this transaction open while running Session B.

In Session B, run:

SELECT *
FROM dbo.LockDemo WITH (READCOMMITTEDLOCK)
WHERE Id = 1;

READCOMMITTEDLOCK makes the example deterministic even when READ_COMMITTED_SNAPSHOT is enabled. Session B waits with LCK_M_S because Session A holds an exclusive key lock. In a third window, run the diagnostic queries in the next section to see the waiter and blocker. Release the lock in Session A:

ROLLBACK TRANSACTION;

Session B then completes and returns the original balance of 100 because Session A rolled back. To demonstrate row versioning, remove READCOMMITTEDLOCK on a database with READ_COMMITTED_SNAPSHOT enabled; the reader can return the previously committed version without waiting.

Lab 2: create a two-row deadlock

Run the following in Session A:

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.LockDemo SET Balance += 10 WHERE Id = 1;
WAITFOR DELAY '00:00:15';
UPDATE dbo.LockDemo SET Balance += 10 WHERE Id = 2;

COMMIT TRANSACTION;

Start this in Session B within 15 seconds:

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE dbo.LockDemo SET Balance += 20 WHERE Id = 2;
WAITFOR DELAY '00:00:15';
UPDATE dbo.LockDemo SET Balance += 20 WHERE Id = 1;

COMMIT TRANSACTION;

Each session owns an exclusive lock needed by the other. SQL Server detects the cycle and terminates one session with error 1205; either session can be the victim. DEADLOCK_PRIORITY is considered first, followed by estimated rollback cost. The survivor commits. The primary fix is to access rows in the same order—for example, always update Id = 1 before Id = 2.

In a third window, inspect the newest graph from the built-in system_health session. This requires VIEW SERVER STATE and applies to SQL Server; Azure SQL Database exposes Extended Events through database-scoped targets.

SET QUOTED_IDENTIFIER ON;

;WITH DeadlockEvents AS
(
    SELECT CAST(st.target_data AS xml) AS target_data
    FROM sys.dm_xe_session_targets AS st
    INNER JOIN sys.dm_xe_sessions AS s
        ON s.address = st.event_session_address
    WHERE s.name = N'system_health'
      AND st.target_name = N'ring_buffer'
)
SELECT
    event_xml.value(N'(@timestamp)[1]', N'datetime2') AS event_time,
    event_xml.query(N'(data/value/deadlock)[1]') AS deadlock_graph
FROM DeadlockEvents
CROSS APPLY target_data.nodes(
    N'//RingBufferTarget/event[@name="xml_deadlock_report"]'
) AS events(event_xml)
ORDER BY event_time DESC;

Reset the data and connection setting in both windows before continuing:

IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
SET XACT_ABORT OFF;

UPDATE dbo.LockDemo
SET Balance = Id * 100;

Lab 3: a serializable range blocks an insert

In Session A, protect a range that currently contains one row:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

SELECT *
FROM dbo.LockDemo
WHERE ReferenceCode BETWEEN 15 AND 25;

-- Leave the transaction open while running Session B.

In Session B, attempt to insert into the protected range:

INSERT dbo.LockDemo (Id, AccountName, Balance, ReferenceCode)
VALUES (4, 'Delta', 400, 18);

The insert waits on a range-insert lock. On SQL Server 2022 this appears as LCK_M_RIn_NL; older versions and tools may display related range-insert names differently. Session A holds a RangeS-S lock on the supporting index, protecting existing keys and gaps where qualifying rows could be inserted. Finish Session A:

COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Session B can now insert the row. Remove it before repeating the lab:

DELETE dbo.LockDemo WHERE Id = 4;

These labs illustrate three different diagnoses: incompatible locks causing ordinary blocking, a circular wait causing a deadlock, and an isolation-level guarantee deliberately expanding the locked resource range.

After completing the labs, clean up:

USE LockLab;
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
DROP TABLE IF EXISTS dbo.LockDemo;

-- Optional, from a window connected to master:
-- USE master;
-- DROP DATABASE LockLab;

Quick diagnostic queries

The following server-scoped DMVs generally require VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE for some DMVs on SQL Server 2022+) or the corresponding Azure SQL database permission. wait_time is reported in milliseconds.

Find waiting requests and their blockers:

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    t.text AS current_sql
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
   OR r.wait_type LIKE N'LCK_M_%';

Inspect current locks:

SELECT
    l.request_session_id,
    l.resource_type,
    l.resource_description,
    OBJECT_NAME(p.object_id, l.resource_database_id) AS object_name,
    l.request_mode,
    l.request_status
FROM sys.dm_tran_locks AS l
LEFT JOIN sys.partitions AS p
    ON l.resource_type IN (N'KEY', N'PAGE', N'RID', N'HOBT')
   AND p.hobt_id = l.resource_associated_entity_id
WHERE l.resource_database_id = DB_ID()
ORDER BY l.request_session_id, l.resource_type;

The head blocker is often sleeping and therefore absent from sys.dm_exec_requests. Find its open transaction and most recent SQL:

SELECT
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,
    at.transaction_begin_time,
    txt.text AS most_recent_sql
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_connections AS c
    ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
WHERE s.session_id IN
(
    SELECT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
);

Common waits provide a fast clue:

Wait	Usually means
LCK_M_S	Waiting for a shared lock, often a reader behind a writer
LCK_M_U / LCK_M_X	Waiting to update or exclusively modify data
LCK_M_SCH_M	Waiting for a schema modification lock
LCK_M_RS_S	Waiting for a serializable range shared lock
LCK_M_RIn_NL and related LCK_M_RI* waits	Waiting to insert into a protected key range; the exact name varies by engine version

Use the results together. A granted lock is not automatically a problem; identify the waiter, head blocker, transaction age, and SQL. sys.dm_os_waiting_tasks provides another resource-level view. For historical deadlocks, use system_health or a dedicated Extended Events session capturing xml_deadlock_report.

Performance review checklist

1. Confirm waiting: Look for LCK_M_* waits rather than assuming high duration means blocking.
2. Find the head blocker: Follow blocking_session_id until reaching the session that is not itself blocked.
3. Check transaction scope: Determine whether the blocker has an old open transaction or is idle while holding locks.
4. Identify the resource and mode: Use wait_resource, sys.dm_tran_locks, and the execution plan.
5. Review access paths: Scans lock more rows and hold locks longer than selective seeks.
6. Check isolation requirements: Use stronger isolation only when the business invariant requires it.
7. Capture the deadlock graph: Do not diagnose recurring deadlocks from the victim statement alone.
8. Fix the cause, not the symptom: Prefer indexing, consistent access order, batching, and shorter transactions over NOLOCK or killing sessions.

Locks are necessary and waiting can be normal. Prolonged blocking usually reflects transaction scope, access paths, or concurrency design. Diagnose the wait chain, then reduce the locked resource set or the time locks are held.