A Subtle Anomaly
Write skew is trickier than a lost update because the two transactions write to different rows. Each reads a shared invariant, decides its action is safe, and writes. Individually each is fine, but together they violate the invariant.
The On Call Example
Suppose a rule requires at least one doctor on call. Two doctors are on call and both feel sick.
- Doctor A's transaction reads that two are on call, so it is safe to remove herself, and removes A.
- Doctor B's transaction concurrently reads the same two on call, so it removes B.
- Both commit. Now zero doctors are on call, breaking the invariant.
No row was overwritten, so a lost update fix does not help.
Why Snapshot Isolation Misses It
Under snapshot isolation each transaction reads a consistent snapshot taken at its start, so neither sees the other's change. The conflict lives in the predicate both read, not in a single row.
Prevention
- Serializable isolation, especially SSI, detects the read write dependency and aborts one.
- Materializing the conflict by locking a summary row or using SELECT FOR UPDATE on the rows both read.
Key idea
Write skew lets disjoint writes jointly break an invariant under snapshot isolation, fixed by serializable isolation or by materializing the shared conflict.