Notestransactions🌱 seedling
Write Skew
Also: Write Skew Anomaly · G2-item · A5B · Check-Then-Act Race
Two transactions read the same data, make a decision from it, and then write different rows, each invalidating the premise of the other. No row is written twice, so snapshot isolation sees no conflict, yet the result matches no serial order.
Why it exists
Many invariants span several rows: "checking + savings ≥ 0", "at least one engineer stays on call", "no two bookings overlap". Each transaction preserves the invariant alone; interleaved, they break it.
How it works
- A
SELECTchecks a condition across some rows. - The application decides based on the result.
- It writes a row, which changes the answer the other transaction got in step 1.
- Each transaction has a read-write dependency on the other (it changed what the other read), forming a cycle. That cycle is exactly what Serializability forbids.
- A Lost Update is the special case where both write the same row.
Trade-offs
| Fix | Cost |
|---|---|
| Serializable isolation | aborts and retries, or blocking |
SELECT … FOR UPDATE on every row the check read |
blocking; only works if those rows exist |
| a constraint (unique, check, exclusion) | only for rules a constraint can express |
| materialize the conflict as a lockable row | concurrency control leaks into the schema |
In practice
- Snapshot isolation everywhere allows it: Postgres Repeatable Read, Oracle Serializable, SQL Server
SNAPSHOT. - Postgres Serializable detects it with Serializable Snapshot Isolation and aborts one transaction with SQLSTATE 40001.
Pitfalls
- Locking only the row you write. The danger is in the rows you read.
- When the check is about rows that don't exist yet, there is nothing to lock: that's a Phantom Read.
Connections
- Part of
- Isolation Level
- Builds on this
- Phantom ReadSerializability
Recall
These cards are also in your review deck, scheduled with spaced repetition.