skip to content

The SQL standard lists phantom reads as permitted at REPEATABLE READ, yet on several multi-version (MVCC) engines a repeated range query inside a REPEATABLE READ transaction never shows newly committed rows. Explain the discrepancy and what it does and does not guarantee.

level: seniorimportance: should knowfreq 42%

answer

  1. Standard levels = lock-based phenomena, not a spec of mechanism
  2. Snapshot freezes membership → no read phantoms
  3. Forbidding more anomalies than required is legal
  4. Reads use snapshot, writes check latest version
  5. Stable view ≠ safe write decision

basics

~20 s

The standard's levels were defined by which anomalies a lock-based implementation exhibits. MVCC engines serve a transaction's reads from one snapshot, so re-reads are stable and read phantoms never appear — but that only stabilizes what you see, it does not make writes based on that view safe.

solid answer

~60 s

The isolation levels in the standard are defined phenomenologically — by which anomalies show up — and the phenomena were derived from lock-based implementations, where REPEATABLE READ means "hold read locks on rows to commit, but no range locks", which lets inserts through. MVCC engines implement REPEATABLE READ differently: the transaction takes a snapshot and every read is answered as of that snapshot. Membership of any predicate is therefore frozen — no committed insert, delete or boundary-crossing update can appear mid-transaction. So *read* phantoms are absent even though the standard permits them. That is legal: the standard specifies a maximum set of permitted anomalies, not a minimum. The crucial caveat is that snapshot stability is a property of *reads*. The moment the transaction writes based on "my predicate matched nothing", the guarantee stops covering it: another transaction with its own snapshot can concurrently reach the same conclusion, and both commit. That is why snapshot-based REPEATABLE READ is not SERIALIZABLE, and why "my engine has no phantoms at REPEATABLE READ" is not a correctness argument for read-then-write logic.

code

sql · 10 lines
sql
-- both transactions run at snapshot-based REPEATABLE READ
-- A                                  -- B
BEGIN;                                BEGIN;
SELECT count(*) FROM seats            SELECT count(*) FROM seats
  WHERE flight=42 AND booked;           WHERE flight=42 AND booked;
-- 199 (stable all transaction)       -- 199 (its own snapshot)

INSERT INTO seats VALUES(42, ...);    INSERT INTO seats VALUES(42, ...);
COMMIT;                               COMMIT;
-- neither saw a phantom; capacity 200 is now exceeded by one

go deeper

for a junior

It is enough to say the standard describes anomalies from lock-based engines, while MVCC engines answer all reads from one snapshot so the row set does not change mid-transaction.

for a middle

Explain the snapshot mechanism concretely — versions plus a visibility rule — and note that the standard sets a ceiling on permitted anomalies, not a floor.

for a senior

Draw the line explicitly: reads are stabilized, decisions are not. Give the act-on-absence and act-on-aggregate failure patterns and the escalation options, and flag the cross-engine portability trap.

for a principal

Discuss it as a contract-definition problem: level names describe symptoms, not guarantees, so system correctness should be argued from the invariants and enforcement mechanism, with isolation level as one implementation choice among constraints, locking and SSI.

## Why the standard's table is misleading The ANSI/ISO isolation levels are not defined by an abstract correctness property; they are defined by a list of forbidden *phenomena* — dirty read, non-repeatable read, phantom — and those phenomena were catalogued from the behaviour of two-phase-locking implementations. In that world the levels correspond to a locking discipline: - READ UNCOMMITTED: no read locks. - READ COMMITTED: read locks taken and released per statement. - REPEATABLE READ: read locks on touched rows held to commit — rows stable, gaps unprotected, so inserts slip in. - SERIALIZABLE: range/predicate locks as well — gaps protected too. So "REPEATABLE READ permits phantoms" is really a statement about what that locking discipline fails to cover, not a requirement that an engine must exhibit phantoms. ## What multi-version concurrency control does instead Under MVCC, every write creates a new version of a row stamped with the writing transaction's identity, and old versions are retained. A reading transaction takes a **snapshot** — a description of exactly which transactions had committed at a chosen instant — and every read is answered against that snapshot: it sees the newest version of each row that was committed as of the snapshot, and nothing newer. When an MVCC engine sets that snapshot once per transaction (rather than once per statement), you get snapshot isolation. Re-evaluating any predicate returns the same rows, because the visible state of the whole database is frozen. Newly inserted rows are invisible; deleted rows remain visible; a row updated across the predicate boundary is still seen in its old version. All three phantom shapes are eliminated for reads. Readers also never block writers and writers never block readers, which is the main reason MVCC is popular. The standard is not violated: it lists which anomalies a level *may* permit. Forbidding more than required is always allowed. It does mean the same level name denotes materially different behaviour on lock-based and MVCC engines, which is a genuine portability hazard — code that is correct on one can be wrong on the other, and vice versa. ## What snapshot stability does not buy you The snapshot makes your *view* consistent. It says nothing about whether your view is still true at commit time. Two patterns break: 1. **Act on absence.** "Nothing matches this predicate, therefore I may insert." Two concurrent transactions each see the empty set in their own snapshots and both insert. No engine mechanism objects, because they wrote different rows. 2. **Act on an aggregate.** "The sum over this predicate is under the limit, therefore I may add." Both transactions compute the same pre-insert sum and both add. Engines differ on the *write* path in ways worth knowing: writes are checked against the latest committed row version, not the snapshot. If a snapshot-isolation transaction updates a row that has been changed since its snapshot, the engine typically aborts it with a serialization failure rather than let it write blind. That protects same-row conflicts only; rows that do not exist yet, and rows nobody else touched, are outside its reach. ## What to actually do - If the transaction only *reports*, a transaction-scoped snapshot is enough and cheap — you get repeatable aggregates and matching detail rows. - If the transaction *decides to write based on a predicate's contents or emptiness*, escalate: run at SERIALIZABLE (with a retry loop, since SSI implementations abort rather than block), take explicit locks that cover the range, or express the rule as a unique or exclusion constraint so the index enforces it. - Never port "we're safe, we run REPEATABLE READ" across engines without checking what that level does there. On a lock-based engine it means row locks held to commit; on an MVCC engine it means a transaction-lifetime snapshot. The failure modes are different, and so are the mitigations. ## The summary sentence worth being able to say Snapshot-based REPEATABLE READ removes read phantoms as a *side effect of freezing the read view*, not by reasoning about predicates; therefore it protects the consistency of what you observe, never the validity of a decision you make from it.

  • If snapshot-based REPEATABLE READ has no read phantoms, what exactly is still missing compared to SERIALIZABLE?
    Serializability requires that the concurrent execution be equivalent to some serial order of the whole transactions, including their writes. Snapshot isolation only guarantees each transaction reads a consistent point-in-time state and that same-row write conflicts are detected. Decisions made from overlapping reads followed by non-overlapping writes are still permitted, so invariants spanning multiple rows or the absence of rows can be violated.
  • What happens on an MVCC engine when a REPEATABLE READ transaction updates a row that another transaction modified after the snapshot was taken?
    The write cannot be applied against the stale snapshot version, so the engine raises a serialization failure and aborts the transaction rather than silently writing over newer data. The application is expected to retry the whole transaction, which then takes a fresh snapshot. This first-updater-wins behaviour covers same-row conflicts only; it does not detect conflicts between transactions writing different rows.

saying these in an interview costs you the question

  • Insisting the engine is non-compliant because it forbids an anomaly the level allows
  • Treating 'REPEATABLE READ' as meaning the same thing on every engine
  • Concluding that no read phantoms implies serializable behaviour
  • Assuming writes inside a snapshot transaction also see only the snapshot
  • Recommending a level bump as the fix without adding a retry loop for serialization failures

context

resolving links…