Locks and Blocking: Lock Modes, Granularity, Lock Escalation and Finding the Blocking Chain
Even a one-row UPDATE holds four locks, and SQL Server queues lock requests, so you look for the root of the chain with sys.dm_exec_requests and sys.dm_exec_sessions. Lock modes and the compatibility matrix, escalation measured at 6,249 locks in the lab, a safe upsert with UPDLOCK/SERIALIZABLE, a queue table with READPAST, LOCK_TIMEOUT, and a migration whose Sch-M stops every reader, with real output.
8 Nov 2026
·
14 min
·
5
