Transactions & Concurrency
Snapshot Isolation and Write Skew
Snapshot isolation gives a transaction a consistent historical view and usually rejects concurrent writes to the same item. It can nevertheless admit write skew when transactions read a shared predicate and update disjoint rows.
- A snapshot is a visibility rule.
- First-committer/updater-wins is row-conflict protection.
- Write skew preserves rows but breaks predicates.
- Phantoms can supply the stale premise.
- Fixes encode the conflict.
Start: doctors(A,on_call=true), (B,on_call=true). Invariant: COUNT(on_call) >= 1.
T1 snapshot: count=2. T2 snapshot: count=2.
T1 writes only A=false. T2 writes only B=false.
T1 commits; T2 commits (no same-row write conflict).
Final count=0: invariant violated.| Technique | How it creates safety | Trade-off |
|---|---|---|
| Serializable/SSI | Detects dependency structure and aborts | Transaction retry |
| Lock relevant rows/range | Makes decisions wait for concurrent writers | Blocking; predicate/index details |
| Guard row | Both transactions update one shared conflict | Hotspot |
| Declarative constraint | Commit cannot store invalid state | Cross-row assertions often unavailable |
Snapshot isolation is stronger than statement-level Read Committed for read skew and often lost updates, yet it is not serializability. “MVCC” identifies version storage, not the complete isolation contract.