Databases
Part 6 of 6 · MVCC & isolationPhantoms vs Write Skew
Phantoms are new rows matching a predicate; write skew is disjoint writes that break a multi-row invariant. SI allows both; SSI, gap/predicate locks, or constraints close them differently.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Overview
Phantom reads and write skew both show up when isolation is weaker than true serializability. They are not the same bug.
A phantom is a predicate membership change: you count status = pending twice and a new row appeared (or vanished). A write skew is a logic bug across rows: each txn’s writes look fine alone; together they break a rule no single row records.
SI (Postgres REPEATABLE READ) can allow both. SSI, InnoDB gap locks, EXCLUDE constraints, and careful FOR UPDATE close them differently. Interviews fail when the candidate uses one word for both.
Why the right closure wins
Always-2PL SERIALIZABLE stops both and stalls the database. The winning path names the anomaly first, then picks SSI, a predicate lock, or a constraint.
| Anomaly | What changed | SI | Cheap physical fix | SSI |
|---|---|---|---|---|
| Phantom | Set of rows matching a WHERE | Often allowed (Postgres RR) | UNIQUE / EXCLUDE / InnoDB gap | Predicate SIREAD + abort |
| Write skew | Disjoint writes, invariant dead | Allowed | Guard row, CHECK, FOR UPDATE on the read set | Dangerous rw-cycle abort |
| Non-repeatable | Same row, new committed version | Prevented | n/a | Prevented |
- 1
COUNT(*) changed → wrong tool
Treat every SI surprise as a phantom
FOR UPDATE on id = 1 does not lock the insert of id = 99. Doctors both going off call never inserted anything. One name, two holes, two fixes.
- 2
Winner: name the anomaly, then close it
Phantom of “at most one row for this key” → UNIQUE. Overlapping shifts → EXCLUDE. “At least one stays true” → SSI retry or FOR UPDATE on both rows / a guard count. Empty-shift double INSERT is both a phantom and write skew — UNIQUE is enough there.
- ?
SSI vs locks vs constraints
Scattered rules, modest contention: SSI + retry. Tiny hot set: FOR UPDATE in id order. Rule is relational: put it in the schema so every client is stuck with it.
Isolation taxonomy (short)
- READ UNCOMMITTED — dirty reads; no phantom protection.
- READ COMMITTED — no dirty; phantoms, non-repeatable, write skew all possible.
- REPEATABLE READ as SI (Postgres) — same-row stable; phantoms of new keys and write skew remain.
- REPEATABLE READ as next-key (InnoDB) — many phantoms blocked by gap locks; not automatically every skew.
- SERIALIZABLE — true serial order via SSI (Postgres) or locking reads (InnoDB). Full matrix: isolation levels.
Phantom read
T1 executes a range / predicate query. T2 inserts or deletes a row that satisfies that predicate and commits. T1 runs the same query again; the set changed.
Classic: SELECT COUNT(*) FROM orders WHERE status = 'pending'. T2 inserts another pending order. T1’s second count is larger.
Prevention:
- Predicate / range locks — lock the condition, not only found tuples. InnoDB next-key (record + gap) on RR locking reads.
- SSI predicate locks — non-blocking SIREAD on the scan; a conflicting INSERT aborts someone at commit.
- UNIQUE / EXCLUDE — the insert is illegal, so the phantom cannot commit.
Not prevention: SELECT * FROM doctors WHERE id = $alice FOR UPDATE. You locked Alice. Bob-the-insert still matches on_call = true.
Write skew
T1 and T2 read a set, each decide the invariant still holds, each write a different row, both commit. Any serial order would have had the second txn see the first’s write and refuse.
Classic (hub): at least one of Alice or Bob on call. Both see two-on; each turns themselves off.
Sibling shape: two transfers check a.bal + b.bal >= 0 then debit different accounts. Two inventory adjusters check total stock.
Prevention:
SERIALIZABLE(SSI) + retry on40001SELECT … FOR UPDATEon every row the decision read (ordered)- Materialize the invariant: guard row
on_call_count CHECK (>= 1), or EXCLUDE / UNIQUE when the rule is “no two rows like this”
Write skew is not a phantom: no new row is required. The doctors example updates existing rows. Empty-shift INSERT write skew is also a phantom (the second row appears in the predicate). Say both words when that happens.
Sequence
- 1
Client 1
Step 1 – both BEGIN on SI
- 2
Client 1 → Database
BEGIN
- 3
Client 2 → Database
BEGIN
- 4
Client 1
Step 2 – T1 reads predicate on_call = true
- 5
Client 1 → Database
SELECT id FROM doctors WHERE on_call = true
- 6
Database → Client 1
D1, D2
- 7
Client 2
Step 3 – T2 reads the same predicate
- 8
Client 2 → Database
SELECT id FROM doctors WHERE on_call = true
- 9
Database → Client 2
D1, D2
- 10
Client 1
Step 4 – T1 writes D1 off (disjoint from T2)
- 11
Client 1 → Database
UPDATE doctors SET on_call = false WHERE id = D1
- 12
Client 2
Step 5 – T2 writes D2 off
- 13
Client 2 → Database
UPDATE doctors SET on_call = false WHERE id = D2
- 14
Database
Step 6 – SI commits both; SSI aborts one
- 15
Client 1 → Database
COMMIT
- 16
Client 2 → Database
COMMIT
Lesson map
Phantoms vs Write Skew
Phantoms are new rows matching a predicate; write skew is disjoint writes that break a multi-row invariant. SI allows both; SSI, gap/predicate locks, or constraints close them differently.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB t1["Client 1"] t2["Client 2"] db["Database"] t1 -->|BEGIN| db t2 -->|BEGIN| db t1 -->|SELECT id FROM| db db -->|D1, D2| t1 t2 -->|SELECT id FROM| db db -->|D1, D2| t2
Berenson critique (1995)
Berenson, Bernstein, Gray, et al. showed that the ANSI SQL isolation definitions are both ambiguous and too weak. Preventing dirty, non-repeatable, and phantom reads does not imply conflict serializability. Snapshot Isolation, already shipping in engines, still allows write skew.
They (and later Adya / Cahill) talk in dependency graphs: ww, wr, and rw-anti-dependencies. Write skew is a cycle that uses rw-edges without a ww-edge. That graph is why SSI exists (Cahill; Ports and Grittner in Postgres). “We use SERIALIZABLE” in an interview must mean true serializability plus a retry story, not “we set the ANSI name.”
EXCLUDE constraints (PostgreSQL)
-- No two rows with overlapping shifts for the same room.
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)GiST (or SP-GiST / btree_gist) indexes the predicate. A conflicting INSERT/UPDATE errors — the invariant is physical, every client, every isolation level.
That closes many “at most one / no overlap” skews and phantoms. It does not encode “at least two doctors remain on” unless you store a count or range that EXCLUDE/CHECK can see. Need btree_gist for = plus && in one constraint. Without a supporting index type, Postgres will not (cheaply) enforce it.
Safer locking of a predicate
Intent in the source notes: lock the set you decided on, not one PK after the fact.
SELECT id FROM doctors
WHERE on_call = true
ORDER BY id
FOR UPDATE;Under Postgres this exclusive-locks currently matching rows. It serializes writers of those rows (write skew of the “turn D1/D2 off” shape). It still does not lock a future insert of D3 on_call = true the way InnoDB gap locks or SSI predicate locks would.
FOR UPDATE OF table_alias is join-scoped locking (which table in the FROM), not “lock this column’s predicate.” Combine with NOWAIT / SKIP LOCKED only when skipping is allowed — row locks. A sentinel row (locks table, one row per dept) is the explicit “predicate lock” when you need every txn to collide on the same tuple.
SSI recap
SSI detects dangerous structures at commit and aborts one txn (SQLSTATE 40001). You get SI’s non-blocking reads for most of the workload and serializability when it matters. Implementation: read-write dependency tracking, including predicate SIREAD so phantom inserts conflict. Details: SSI vs Snapshot Isolation. Source walk: serializable.c in Postgres.
Retries need backoff and jitter. High abort rate (tens of percent on a seat map) means redesign: lock a small set or constrain it. Long SSI txns also stall VACUUM’s horizon.
| Layer | Role against these anomalies |
|---|---|
| Isolation level | RC allows both; SI stops non-repeatable same-row; SSI aborts dangerous graphs |
| Lock manager | FOR UPDATE on found rows; InnoDB gaps; sentinel row as a manual predicate lock |
| Constraints | UNIQUE / EXCLUDE / CHECK make the illegal state unrepresentable |
| App retry | 40001 backoff; constraint violations are usually not retried the same way |
| Observability | Abort rate, lock waits — RC phantoms will not appear as errors |
In-memory phantoms vs skew (run this)
Two anomalies, one heap. I/O contract: concurrent traces in; whether the predicate set grew, whether disjoint writes broke the invariant.
Press Run. Snippets must be self-contained — no network, files, or native modules.
Deep dive · Predicate locks vs EXCLUDE vs sentinel rows
InnoDB RR locking reads take next-key locks: the gap in the index is owned, so an INSERT into that gap waits — phantom protection as blocking. Postgres SSI takes SIREAD predicate locks that do not wait; they abort at commit. EXCLUDE/UNIQUE reject the bad row with a constraint error (not 40001) — often simpler for “at most one / no overlap.” A sentinel row (SELECT * FROM dept_lock WHERE id = $dept FOR UPDATE) is an application-level predicate lock: every on-call change collides on one tuple. Use it when the invariant is “at least N” and you do not want SSI false positives. Observability: abort rate, pg_locks, lock waits — silent RC phantoms will not show up as errors; tests under concurrency will.
Interview Q&A
What is the fundamental difference between a phantom and write skew?
Answer
Phantom: a predicate re-read sees extra (or missing) rows because another txn inserted/deleted matching rows. Write skew: two txns read overlapping state, write disjoint rows, and the combination violates an invariant. Phantoms are about set membership; write skew is about a multi-row rule with no ww-conflict. An empty-shift double INSERT is both.
Why does Snapshot Isolation not guarantee serializability? Cite Berenson.
Answer
Berenson et al. (SIGMOD 1995) showed ANSI’s phenomena list is too weak and that SI, which prevents many of those phenomena, still allows write skew because it tracks ww, not rw-anti-dependencies. That critique motivated SSI (Cahill; Postgres 9.1+ via Ports and Grittner). Postgres RR is SI. Saying “we prevent phantoms so we are serializable” repeats the ANSI mistake.
How do EXCLUDE constraints prevent write skew?
Answer
They turn a predicate (“no two overlapping shifts,” “no two rows with on_call and same dept”) into a GiST-enforced lock. The second writer gets a constraint error at INSERT/UPDATE, regardless of isolation. They need the right index opclasses (btree_gist for mixes of = and &&). They do not encode “at least one remains true” unless you store a quantity the constraint can see.
What is the safer FOR UPDATE technique, and when does it fail?
Answer
Lock every row your decision read (WHERE on_call = true ORDER BY id FOR UPDATE), not one PK you already knew. That closes doctors-style skew on existing rows. It fails for future inserts matching the predicate under Postgres SI — that needs SSI, InnoDB gaps, UNIQUE/EXCLUDE, or a sentinel row. FOR UPDATE OF alias is a join hint, not a column predicate lock.
SSI vs plain SI — performance?
Answer
SSI adds dependency tracking and may abort (retries). Typical extra latency is small until contention concentrates on one predicate. SI is cheaper and silently wrong for multi-row invariants. Seat maps at high QPS often need explicit locks because abort rates climb. Measure 40001 rate; do not assume it stays rare.
Describe a retry strategy for serialization failures.
Answer
Catch 40001 / SerializationFailure, ROLLBACK, sleep base * 2^attempt + jitter (tens to hundreds of ms), retry the entire unit of work, cap at 3–8 attempts, log aborts. No external side effects inside the loop. Thundering-herd retries without jitter make contention worse.
Why is FOR UPDATE on a primary-key lookup not phantom protection?
Answer
You locked one existing tuple. A concurrent INSERT of a different key that matches the business predicate is not blocked. RC/SI FOR UPDATE ≠ gap lock. This is the most common production hole after “we used transactions.”
READ COMMITTED for a money invariant that spans a COUNT plus an INSERT?
Answer
Both phantoms and write skew are legal. The bug shows up under load, not in a single-session test. Use UNIQUE if the INSERT must be unique, SSI if the rule is multi-row, or lock a sentinel. RC is fine for “show latest price.”
When would you pick a guard row over SSI?
Answer
Invariant is a single counter (“at least one on call,” “capacity remaining”). One row, UPDATE … SET n = n - 1 WHERE n >= 1, CHECK on the table. No false-positive aborts, no predicate lock coarseness. SSI still wins when the constraint cannot be denormalized cheaply.
How do you observe that phantoms are happening?
Answer
They often do not error. Concurrency tests, invariant checkers after the fact, and tracing two sessions at RC/RR. SSI turns many of them into visible 40001. Lock wait graphs help InnoDB gap stalls. If you only monitor error rate, RC phantoms look like success.
Pitfalls
On one page, draw (A) COUNT pending then INSERT pending — mark the phantom. (B) two doctors UPDATE disjoint rows — mark write skew and the rw-cycle. For each, write one schema-level fix and one txn-level fix. Implement the playground’s UNIQUE empty-shift vs SI double INSERT on paper. List the SQLSTATE you retry and what you re-run.
Go Deeper
- PostgreSQL — Transaction isolation
- Berenson et al. — A Critique of ANSI SQL Isolation Levels
- PostgreSQL EXCLUDE constraints
- Wikipedia — Write skew
- Postgres serializable.c
Cluster: MVCC hub · Isolation levels · SSI vs SI · FOR UPDATE · VACUUM
One-line takeaway: phantoms change predicate membership; write skew breaks a multi-row invariant with disjoint writes — SI allows both; close them with SSI, gap/predicate locks, or constraints, not with a PK FOR UPDATE and hope.