Lock Modes, Granularity, Intent Locks, Escalation, Key-Range Locks, and Metadata Locks

Learning outcomes

A dispatcher updates a ServiceHub inventory row and leaves the transaction open. Another session reads that row and waits. A third engineer proposes ROWLOCK without checking what the engine actually locked. This lesson treats blocking as an observable owner/waiter relationship and uses two sessions plus Dynamic Management Views (DMVs) to expose the lock footprint.

1. Lock modes encode compatibility

Shared (S) locks support locking reads; exclusive (X) locks protect modifications; update (U) locks are used in update-oriented access to reduce certain conversion-deadlock patterns. Intent locks such as IS and IX describe lower-level intent at higher levels. Schema stability (Sch-S) and schema modification (Sch-M) locks protect metadata. The exact combination depends on isolation, statement, index/access path, and engine decisions.

USE ServiceHubLab;GOBEGIN TRANSACTION;UPDATE lab07.Inventory SET on_hand=on_hand-1 WHERE part_id=10;SELECT @@SPID AS session_a;-- leave open

USE ServiceHubLab;GOSET LOCK_TIMEOUT 10000;SELECT @@SPID AS session_b;SELECT part_id,on_hand FROM lab07.Inventory WHERE part_id=10;GO

Under locking READ COMMITTED, Session B can wait for Session A. This is blocking, not deadlock, because Session A can still commit or roll back and release the resource.

2. Observe the waiter and lock owner

SELECT session_id,status,blocking_session_id,wait_type,wait_time,wait_resourceFROM sys.dm_exec_requestsWHERE session_id IN (<session_A>,<session_B>);SELECT request_session_id,resource_type,resource_description,request_mode,request_statusFROM sys.dm_tran_locksWHERE request_session_id IN (<session_A>,<session_B>)ORDER BY request_session_id,resource_type,request_mode;GO

A DMV row is a sample of current state, not a historical proof of every lock the statement acquired. Capture SQL text, transaction age, and timestamps with the lock sample during a real incident.

3. Granularity and intent locks form a hierarchy

SQL Server can lock keys/rows, pages, HoBTs, objects, metadata, and other resources. Intent locks let higher levels know lower-level locking exists without enumerating all child locks. One statement may use multiple granularities. ROWLOCK is a hint, not a guarantee that only row locks will be taken, and forcing finer granularity can increase lock-memory cost.

4. Escalation is dynamic

Lock escalation replaces many fine-grained locks with fewer coarse-grained locks to reduce lock-memory overhead. SQL Server considers lock count and memory pressure, and an escalation attempt can fail when another session holds an incompatible object lock. This is why “it always escalates at exactly N rows” is poor operational guidance. Use Extended Events and current lock evidence before changing batching or escalation settings.

-- Session AIF @@TRANCOUNT>0 ROLLBACK TRANSACTION;GO-- Session BSET LOCK_TIMEOUT -1;GO

5. SERIALIZABLE protects ranges; metadata has its own locks

SERIALIZABLE can use key-range locks so another transaction cannot insert a phantom into a qualifying indexed interval. Range shape depends on the predicate and access path. Separately, ordinary queries take Sch-S locks while DDL commonly requires Sch-M, so even a NOLOCK query can participate in metadata blocking.

-- Session ASET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION;SELECT * FROM lab07.Inventory WHERE part_id BETWEEN 10 AND 15;-- keep open-- Session B in another window:-- INSERT lab07.Inventory(part_id,on_hand) VALUES(12,1);-- Observe the wait, then ROLLBACK Session A and remove part_id 12 if inserted.

Inspect the lock resources and plan rather than memorizing one key-range label. Index design affects which range must be protected.

6. Production judgment

Build a blocking timeline: transaction start, statement, lock owner, waiter, wait type/resource, cancellation/timeout, and release. Prefer fixing oversized transactions, poor access paths, inconsistent write order, or isolation mismatch before scattering hints. Full DMV visibility can require VIEW SERVER PERFORMANCE STATE.

Authoritative references

• Locking and row versioning guide — lock modes and escalation
• sys.dm_tran_locks — lock manager state
• sys.dm_exec_requests — wait/blocking evidence
• Table hints — ROWLOCK semantics
• Isolation level — SERIALIZABLE range behavior
• SQL Server 2025 builds — CU baseline