30-Second Glance
- Write skew = two txns each read an overlapping predicate, then write disjoint rows that jointly violate an invariant. Snapshots can't see it — no shared row, no conflict.
- Canonical case: on-call rota — both doctors see each other on call, both go off call, zero remain. Constraint spans rows, writes touch different rows.
- Phantoms = a concurrent insert/delete changes the set your predicate matched (double-booking a slot you verified was free).
- Fixes: real serializability (SSI/2PL),
SELECT FOR UPDATE, atomic constraints (exclusion, unique), or materialize the conflict onto one row (counter/lock row).
The On-Call Example (Memorize This)
Rota rule: at least one doctor on call at all times. Alice and Bob are both on call. Two concurrent transactions, one per doctor going off call:
- T1 reads "doctors on call" → {Alice, Bob}, decides Alice may go off call, sets
Alice.on_call = false. - T2 reads "doctors on call" → {Alice, Bob} (same snapshot — T1 uncommitted), sets
Bob.on_call = false. - Both commit. Zero doctors on call. Invariant dead.
Under Snapshot Isolation nothing aborts: T1 wrote only Alice's row, T2 only Bob's — no write-write conflict. Under Read Committed Isolation it fails identically. This is second-order: each txn is locally valid; only their combination breaks the rule.
loading diagram…
Phantom Writes: The Sibling Anomaly
Same root cause, insert flavor. Booking rule: no overlapping bookings per room.
- T1 checks
SELECT ... WHERE room=1 AND overlaps(slot)→ empty, inserts booking. - T2 concurrently checks the same predicate → also empty (T1 uncommitted), inserts overlapping booking.
- Both commit. Double-booked.
The phantom is the row that "appeared" between your check and your commit. Unique constraints catch exact-duplicate phantoms; range-overlap phantoms need exclusion constraints (EXCLUDE USING gist in Postgres), predicate locking, or serializable isolation.
Fix Menu (Cheapest First)
| Fix | How it works | Cost | Best for |
|---|---|---|---|
| Database constraint (unique / check / exclusion) | Make the invariant a schema rule; second committer gets constraint violation | Near-zero runtime cost; limited expressiveness | Uniqueness, non-overlap ranges, simple invariants — always prefer if expressible |
SELECT ... FOR UPDATE on the contested rows |
Upgrades predicate reads to row locks; second txn blocks then sees fresh state | Lock waits; deadlock risk if order varies; doesn't lock absent rows | Hot-row contention (balances, seat counts) |
| Materialize conflict (lock/counter row) | All txns FOR UPDATE one shared row (e.g. rota row, room row) before deciding |
Serializes the critical section; hotspot | Small predicate domains (per-room, per-rota locks) |
| Serializable isolation (SSI / 2PL) | Engine detects rw-antidependency cycles and aborts one txn | Abort/retry load; some tuning | General predicate invariants you can't encode as constraints |
| Application lock (Redis/ZK) | Coarse mutual exclusion outside the DB | Extra system + failure modes; easy to get wrong | Cross-shard or cross-system invariants |
The interview answer structure: "First try a constraint; if inexpressible, lock the conflict domain (FOR UPDATE or materialized row); if the invariant is general, escalate to SSI/serializable and handle 40001 retries."
Lost Update vs Write Skew (Don't Conflate)
| Lost update | Write skew | |
|---|---|---|
| Conflict shape | Same row written twice | Predicate read, disjoint rows written |
| Snapshot isolation | Aborts (write-write conflict) | Allows (no shared write) |
| Minimal fix | Atomic UPDATE / version check | Constraint, predicate lock, or serializable |
| Example | Two balance += 100 |
Two doctors off call |
Trade-offs
| Approach | Pros | Cons | Use when |
|---|---|---|---|
| Constraints | Cheapest, always-on, no app retry logic | Only expressible invariants | Uniqueness, ranges, FKs — default first move |
| Explicit locking | Precise, works at read-committed cost | Deadlocks, hotspots, misses phantoms unless predicate-locked | Known small conflict domain |
| Serializable (SSI/2PL/serial) | Covers all predicate cases uniformly | Throughput/abort overhead | Money, scheduling, rostering invariants |
| Hope + reconciliation | Zero upfront cost | Silent corruption found later, expensive repair | Never for invariants; only for reconcilable stats |
Interview One-Liners
- Q: Define write skew in one line? A: Concurrent txns read overlapping predicates but write disjoint rows, jointly violating an invariant no single txn broke.
- Q: Why doesn't snapshot isolation stop it? A: Its conflict detector watches rows written, not predicates read — disjoint writes never collide.
- Q: Walk me through the on-call example? A: {Alice, Bob} on call; T1 sees both, removes Alice; T2 sees both (stale snapshot), removes Bob; both commit → zero on call.
- Q: What is a phantom write? A: A concurrent insert/delete changing the row set your predicate matched — e.g. two txns each verify a slot is free, then both book it.
- Q: Cheapest fix for double-booking? A: A Postgres exclusion constraint on overlapping ranges — the DB rejects the second commit with no app locking.
- Q: When locking can't cover phantoms, what then? A: Row locks don't cover absent rows; use predicate locks / index-range locking (InnoDB next-key) or full serializable (SSI).
- Q: General-purpose fix if constraints can't express it? A: Serializable isolation (SSI or 2PL) + whole-transaction retry on serialization failure.