Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
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.
SQL Server 2025 Optimized Locking: TID Locking, Lock After Qualification and Behavior Changes
With optimized locking on, a transaction that updates 1,000 rows holds a single XACT lock instead of 1,017 locks and never escalates; with RCSI also on, LAQ evaluates the UPDATE predicate on the last committed version without locks, removing blocking but possibly changing the result of some concurrent work. The ADR prerequisite, new LCK_M_S_XACT waits, hints that disable LAQ, xactlock in the deadlock graph and the 2019/2022 difference, with real measurements.