Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
Isolation Levels and Row Versioning: READ COMMITTED, RCSI, SNAPSHOT, REPEATABLE READ and SERIALIZABLE
Isolation levels decide, through locking or row versioning, which anomalies concurrent transactions can see: the default READ COMMITTED makes readers wait for writers, RCSI returns the last committed version, and SNAPSHOT keeps the view from the start of the transaction and fails with 3960 on conflict. Two-session experiments on dirty reads, non-repeatable reads, phantoms, lost updates and write skew, the 14-byte cost of versioning and choosing an IsolationLevel from .NET.
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.