Write Skew
Write skew is an anomaly where two concurrent transactions read overlapping data, write disjoint rows, and together violate an invariant that neither saw break.
Definition
Write skew occurs under snapshot isolation when two transactions read a common set of rows, each decides it is safe to write, and each updates a different row. Because their writes do not conflict at the row level, both commit, yet the combined result violates a constraint that depended on the whole set.
Context and scope
Snapshot isolation prevents dirty reads and lost updates on the same row, so teams assume it is sufficient for correctness. Write skew is the counterexample: the transactions touch different rows, so no write-write conflict is detected.
Common misconceptions
- Snapshot isolation is equivalent to serializable because each transaction sees a consistent snapshot.
- Write skew requires a bug in the application, not a normal consequence of concurrent reads and writes.
Anti-patterns
- Enforcing a cross-row invariant with a read followed by an update under repeatable read or snapshot isolation.
- Assuming a unique constraint will catch a rule such as at least one doctor on call.
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/transaction-iso.html
How it works
Suppose a rule says at least one doctor must remain on call. Alice and Bob are both on call. Transaction A counts two doctors, decides it can remove Alice, and updates Alice’s row. Transaction B counts two doctors, decides it can remove Bob, and updates Bob’s row. Each write affects a different row, so snapshot isolation sees no conflict. Both commit, and now zero doctors are on call.
The anomaly exists because the decision depends on a predicate over multiple rows, while the writes are disjoint. Repeatable read gives each transaction a stable snapshot but does not serialize the predicate. Serializable isolation tracks read-write dependencies and aborts one transaction with a serialization failure, which the application must retry.
Fixes
Use serializable isolation and retry on serialization failures. Or materialize the conflict: lock a shared row representing the on-call group with SELECT ... FOR UPDATE, so the second transaction waits and re-reads. A database constraint can help only if the invariant is expressible over a single row or a unique index. Application-level locks work but add coordination and failure modes.
When to use this knowledge
Look for write skew wherever a transaction reads several rows to enforce a rule and then writes a subset: seat allocation, inventory reservations, account balances with overdraft limits, permission counts, and scheduling. Single-row counters and unique-key inserts are usually safe; multi-row invariants are the danger zone.
Trade-offs and example
Serializable isolation is the most robust fix but increases aborts, so every transaction needs a bounded retry loop and idempotent effects. Explicit locks reduce aborts but create contention and deadlock risk. A constraint is cheapest but only covers rules the schema can express.
Example: a booking system allows at most 100 active reservations per event. Two transactions each count 99 and insert different reservations. Without a predicate lock or serializable check, both commit and the event is oversold. Locking the event row before counting serializes the decision and preserves the invariant.