Write Skew and Serialization Anomalies
What you'll learn: You'll discover subtle concurrency bugs that snapshot isolation can't prevent, and when you need to escalate to serializable isolation.
The Phantom Menace of Concurrent Writes
Even with snapshot isolation (which prevents lost updates and dirty reads), there's a sneaky class of bugs called write skew. This happens when two transactions read overlapping data, make decisions based on what they read, then write to different records — both commits succeed, but the combined result violates a business constraint.
Real-World Example
Imagine an on-call scheduling system where at least one doctor must always be on-call:
Initial state: Alice and Bob are both on-call.
Transaction 1: Alice checks if anyone else is on-call (sees Bob), then removes herself.
Transaction 2: Bob checks if anyone else is on-call (sees Alice), then removes himself.
Both transactions read different snapshots showing the other person on-call. Both write to different rows (Alice's record vs Bob's record). Both commit successfully. Result? Nobody is on-call — the constraint is violated.
Why Snapshot Isolation Fails Here
Snapshot isolation prevents conflicts when transactions write to the same row. But write skew involves reading shared data to make a decision, then writing to different places. The transactions don't directly conflict, so both succeed.
When to Escalate to Serializable
Use serializable isolation when:
- Business rules depend on the absence of rows or aggregate constraints
- Decisions in one transaction depend on data another transaction might modify
- You can't easily model the constraint as a database-level check
Key Takeaway: Write skew exposes how snapshot isolation allows transactions that don't directly conflict to violate application-level invariants; serializable isolation prevents these anomalies but at a performance cost.