Write skew
Write skew is the anomaly with no smoking gun: two transactions each read an invariant, each write to a different row, and both commit. No write-write conflict ever happens — the rows they touched don't overlap — and the invariant both of them checked is broken anyway.
The canonical example: a hospital requires at least one doctor on call. Alice and Bob, both on call, each request the night off. Each transaction checks "is anyone else on call?" — sees the other — and proceeds:
Formally Adya's G2 (predicate) and G2-item. Snapshot-based REPEATABLE READ can't catch it: each transaction's snapshot really did contain another doctor, each UPDATE touched a different row, so there is nothing for a write-conflict check to object to. The decision each transaction made was invalidated by the other's write — a read-write dependency, not a write-write one.
Who prevents it
| Level | SQL standard | PostgreSQL | MySQL (InnoDB) |
|---|---|---|---|
| REPEATABLE READ | (not addressed) | happens — proof | happens — proof |
| SERIALIZABLE | prevented (by definition) | rejected with 40001 at COMMIT — proof | deadlock 1213, detected instantly — proof |
Same guarantee, opposite philosophies. PostgreSQL's SERIALIZABLE (SSI) is optimistic: both transactions run without blocking, and the dependency tracker aborts one at commit. MySQL's is pessimistic: every plain SELECT takes a shared lock, so the two UPDATEs collide with the other's read lock — a cycle the deadlock detector breaks on the spot. Either way your retry logic is not optional; only the error code differs.
Multi-row invariants — "at least one on call", "the sum stays positive", "unique-ish under concurrency" — are only automatic at SERIALIZABLE. Below it, the fix is making the conflict explicit: SELECT … FOR UPDATE on the rows the decision depends on, so the transactions collide on purpose.
Related anomalies
- Lost update — the single-row special case: there the two writes do overlap, which is why weaker mechanisms can catch it.
- Read-only anomaly — write skew's strangest consequence: even a transaction that writes nothing can observe an impossible state.
See it happen
- PostgreSQL: Serializable — write skew at REPEATABLE READ, then the same interleaving under SSI
- MySQL: Serializable — the same invariant dying, then saved by shared locks and a deadlock