SQL Server Locking: Lock Types, Escalation and RCSI (2026)

Lock Modes: S, X, U, IS, IX, SIX

Lock Compatibility Matrix

A ✓ means the two locks can coexist on the same resource. An ✗ means the requesting lock must wait:

Lock Granularity: Row, Page, Table, Database

SQL Server can lock resources at multiple granularities:

• RID — a single row in a heap (8-byte physical row identifier)
• KEY — a single row in a B-tree index (identified by hash of the key value)
• PAGE — an 8 KB data or index page (8 rows on average for 1 KB rows)
• EXTENT — 8 pages; used during space allocation
• HoBT — Heap or B-Tree; the entire allocation unit for a partition
• TABLE — the entire table including all indexes
• DATABASE — the entire database; used during ALTER DATABASE operations

SQL Server automatically selects the appropriate granularity. A query scanning 5 rows takes row-level locks. A query scanning 10,000 rows will likely trigger lock escalation to a table lock (see below).

Intent Locks: Why They Exist

Before acquiring a row-level X lock, SQL Server acquires an IX (Intent Exclusive) lock on the containing page, and an IX on the table. This allows a competing operation that wants a TABLE-level S lock to detect the conflict instantly — by checking the table-level IX lock — rather than scanning every row in the table for row-level X locks.

Without intent locks, granting a schema-modification lock (Sch-M) on a table would require checking every page and every row to see if any session holds any row-level locks — impossibly expensive at scale.

-- See intent locks held by active sessions
SELECT
    tl.resource_type,
    tl.resource_description,
    tl.request_mode,
    tl.request_status,
    tl.request_session_id
FROM sys.dm_tran_locks tl
WHERE tl.resource_database_id = DB_ID()
  AND tl.request_mode IN ('IS','IX','SIX')
ORDER BY tl.request_session_id, tl.resource_type;

Update Locks: Preventing Deadlocks in Read-Modify-Write

A common deadlock pattern without U locks:

1. Session A acquires S lock on row 1 (reading before update)
2. Session B acquires S lock on row 1 (also reading)
3. Session A tries to upgrade S → X (blocked by Session B's S)
4. Session B tries to upgrade S → X (blocked by Session A's S)
5. Deadlock.

SQL Server prevents this by acquiring an Update (U) lock during the read phase of an UPDATE. U is compatible with S (readers don't block U) but U is not compatible with another U. So at most one session can be in the "read before write" phase at a time.

-- Force an update lock on a SELECT (useful in explicit transactions)
SELECT * FROM dbo.Orders WITH (UPDLOCK)
WHERE OrderID = 12345;
-- Now safely update — no deadlock with other UPDLOCK sessions

Key-Range Locks: Preventing Phantoms

Under Serializable isolation, SQL Server must prevent phantom reads — rows that appear in a second read of the same range that weren't there in the first. Key-range locks accomplish this by locking the gaps between index keys as well as the keys themselves.

-- Session A (Serializable)
BEGIN TRAN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM dbo.Orders WHERE OrderDate BETWEEN '2026-01-01' AND '2026-01-31';
-- Acquires RangeS-S locks on every key in range AND the gaps between them

-- Session B (concurrent)
INSERT INTO dbo.Orders (...) VALUES ('2026-01-15', ...);
-- Blocked by Session A's key-range lock on the 2026-01 gap

Key-range locks are only acquired at Serializable isolation level. They significantly increase locking overhead on range-heavy workloads — another reason Snapshot Isolation (which uses row versions instead of range locks) is preferred for read-heavy analytics.

Lock Escalation

Maintaining thousands of individual row locks consumes memory (each lock entry is ~64–96 bytes in the lock manager). When a single statement acquires more than 5,000 locks on a single table, or when total lock memory exceeds 40% of the buffer pool, SQL Server attempts to escalate row/page locks to a single table lock.

Escalation fires a table-level X lock request. If granted (no conflicting locks), all the individual row locks are dropped and replaced by one table lock — dramatically reducing memory. If a conflicting lock exists, escalation fails and the row locks are retained.

-- Monitor lock escalation events
SELECT
    cntr_value AS lock_escalations_per_sec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Lock Escalations/sec'
  AND instance_name = '_Total';

-- Table-level lock escalation setting
SELECT name, lock_escalation_desc
FROM sys.tables
WHERE name = 'Orders';

Disabling Lock Escalation

Lock escalation to TABLE can block all concurrent readers while a large UPDATE or DELETE runs. On partitioned tables, the default escalation target is AUTO (partition-level), which is much less disruptive. You can also disable escalation entirely:

-- Escalate to partition level (recommended for partitioned tables)
ALTER TABLE dbo.Orders SET (LOCK_ESCALATION = AUTO);

-- Disable escalation entirely (may cause lock memory pressure)
ALTER TABLE dbo.Orders SET (LOCK_ESCALATION = DISABLE);

-- Restore default (table-level)
ALTER TABLE dbo.Orders SET (LOCK_ESCALATION = TABLE);

Viewing Current Locks

-- All locks currently held
SELECT
    tl.resource_type,
    tl.resource_description,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_status,      -- GRANT, WAIT, CONVERT
    tl.request_session_id,
    s.login_name,
    s.host_name,
    OBJECT_NAME(p.object_id) AS table_name
FROM sys.dm_tran_locks tl
LEFT JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
LEFT JOIN sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id
WHERE tl.resource_database_id = DB_ID()
ORDER BY tl.request_session_id, tl.resource_type;

-- Locks on a specific table
SELECT tl.*, OBJECT_NAME(p.object_id)
FROM sys.dm_tran_locks tl
JOIN sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id
WHERE p.object_id = OBJECT_ID('dbo.Orders');

Table Hints: NOLOCK, ROWLOCK, UPDLOCK, TABLOCK

RCSI: Eliminating Read-Write Lock Conflicts

The fundamental tension in SQL Server locking is that S (shared read) locks conflict with X (exclusive write) locks. The default READ COMMITTED isolation level takes S locks, so readers block writers and writers block readers.

Read Committed Snapshot Isolation (RCSI) replaces S locks with row-version reads from TempDB. Readers never take S locks; writers never block readers. This eliminates the vast majority of blocking on OLTP systems:

-- Enable RCSI (covered in detail in the blocking/deadlocks guide)
ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

-- Verify
SELECT name, is_read_committed_snapshot_on
FROM sys.databases WHERE name = 'YourDB';

After enabling RCSI, your lock waits profile will shift: LCK_M_S waits drop to near zero, and only writer-writer conflicts (LCK_M_X) remain. These are addressed by shortening transactions and ensuring consistent lock ordering.

Read Next

• Isolation Levels: READ UNCOMMITTED to Snapshot
• Blocking and Deadlocks: Detection and Resolution
• Transaction Log Internals
• Wait Statistics: sys.dm_os_wait_stats
• All SQL Server Articles