Lessons in this course (11)
9. Locking, latches, isolation levels and row versioning
Locks protect logical consistency of data between transactions:
| Mode | Used for | Compatible with |
|---|---|---|
| Shared (S) | Reading | S, U, IS |
| Update (U) | Reading a row you intend to update (prevents a common deadlock) | S, IS |
| Exclusive (X) | Writing | Nothing |
| Intent (IS, IX, SIX) | Signal locks at a lower level (on table/page above a row) | Allows fast checks |
| Schema (Sch-S, Sch-M) | Compiling / altering objects | Sch-M blocks everything |
Granularity: row (key/RID) → page → partition → table (object). Lock escalation converts many row/page locks (~5,000 on one object) into a table lock to save memory — can cause sudden blocking.
Latches are lightweight internal locks protecting in-memory structures (pages in the buffer pool) for very short durations — e.g. PAGELATCH_EX contention on the last page of an ever-increasing key under heavy inserts (fix: OPTIMIZE_FOR_SEQUENTIAL_KEY, partitioning, hash keys).
Isolation levels:
| Level | Dirty read | Non-repeatable read | Phantom | How |
|---|---|---|---|---|
READ UNCOMMITTED (NOLOCK) | ✅ possible | ✅ | ✅ | No shared locks — may read uncommitted, duplicated or missing rows |
| READ COMMITTED (default) | ❌ | ✅ | ✅ | Shared locks released after each read |
| REPEATABLE READ | ❌ | ❌ | ✅ | Shared locks held until commit |
| SERIALIZABLE | ❌ | ❌ | ❌ | Key-range locks |
| READ COMMITTED SNAPSHOT (RCSI) | ❌ | ✅ | ✅ | Readers read the last committed version — readers don't block writers |
| SNAPSHOT | ❌ | ❌ | ❌ | Whole transaction sees a consistent snapshot; update conflicts raise an error |
Row versioning: with RCSI/SNAPSHOT, updates keep the previous row version in the version store (tempdb, or the persistent version store with ADR); readers follow the version chain. Benefit: far less blocking between readers and writers (Azure SQL Database uses RCSI by default). Cost: tempdb usage and a 14-byte versioning tag per row.
ALTER DATABASE Shop SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
-- Who is blocking whom right now?
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, t.text
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;