Roadmap › SQL Server & T-SQL › Module: Indexes and Performance
Lessons in this course (19)
17. Transactions, isolation levels and locking
Advanced★ Must knowLesson 17 of 19
ACID: Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions don't interfere), Durability (committed = survives crashes).
| Isolation level | Dirty read | Non-repeatable read | Phantom | Notes |
|---|---|---|---|---|
READ UNCOMMITTED (NOLOCK) | ✓ | ✓ | ✓ | Can read uncommitted, even skip/duplicate rows |
| READ COMMITTED (default) | ✗ | ✓ | ✓ | Shared locks released after each read |
| REPEATABLE READ | ✗ | ✗ | ✓ | Holds shared locks till end of transaction |
| SERIALIZABLE | ✗ | ✗ | ✗ | Range locks; most blocking |
| SNAPSHOT | ✗ | ✗ | ✗ | Row versions in tempdb; readers don't block writers; update conflicts detected |
| READ COMMITTED SNAPSHOT (RCSI) | ✗ | ✓ | ✓ | Database option; statement-level versioning — default in Azure SQL Database |
Lock modes: Shared (S), Exclusive (X), Update (U), Intent (IS/IX), schema locks. Granularity: row → page → table (lock escalation at ~5,000 locks).
Blocking = one session waits for another's lock. Deadlock = two sessions wait on each other; SQL Server kills one (the victim, error 1205). Reduce deadlocks: access objects in the same order, keep transactions short, add proper indexes (fewer locks), use RCSI/SNAPSHOT, and retry on 1205.