The ANSI SQL standard defines REPEATABLE READ as permitting one specific anomaly. Which one is it, and why do many multiversion engines not actually exhibit it at that level?
answer
- ANSI RR: dirty no, non-repeatable no, phantom yes
- phantom = predicate population changes
- lock model: existing rows lockable, future rows not
- MVCC snapshot = phantom-free for plain reads
- level name is a ceiling on anomalies, not a mechanism
basics
~20 sANSI REPEATABLE READ still permits phantoms: rows another transaction inserts or deletes can change the result of a repeated range query. Snapshot-based engines read everything from one fixed snapshot, so later inserts stay invisible and plain reads show no phantoms.
solid answer
~50 sANSI defines the levels by which anomalies they forbid. REPEATABLE READ forbids dirty reads and non-repeatable reads but still permits **phantoms** — re-running `SELECT ... WHERE status = 'NEW'` can return extra rows because someone inserted matching rows and committed. That definition comes from a lock-based mental model: hold long-duration shared locks on the *rows you read*, but no lock on the predicate, so new rows are unconstrained. Multiversion engines implement the level differently: one snapshot fixed at the transaction's first read serves every read. A row inserted after that snapshot simply is not visible, so a repeated range query returns the identical set. Those engines therefore give something stronger than ANSI requires — usually called snapshot isolation. Two caveats. The names are guarantees of a *minimum*, so the label REPEATABLE READ does not tell you which behaviour you get — check the engine. And in some engines locking reads and plain reads at this level do not use the same view.
code
text · 5 linesT1: BEGIN;
T1: SELECT count(*) FROM orders WHERE status = 'NEW'; -- 5 (S-locks on those 5 rows)
T2: INSERT INTO orders(status) VALUES ('NEW'); COMMIT; -- no lock conflict
T1: SELECT count(*) FROM orders WHERE status = 'NEW'; -- 6 <- phantom
T1: COMMIT;go deeper
Know the anomaly table and be able to define a phantom with a concrete example.
Explain why lock-duration reasoning permits phantoms at this level and why an MVCC snapshot removes them anyway.
Add the portability trap and the plain-read versus locking-read split inside one transaction, and say how you would verify behaviour on your engine.
Argue about depending on stronger-than-specified behaviour: what you standardise on, and whether the invariant belongs in the isolation level or in a constraint.
## The ANSI definition is a list of forbidden anomalies The SQL standard defines isolation levels not by mechanism but by which phenomena may occur: | Level | Dirty read | Non-repeatable read | Phantom | |---|---|---|---| | READ UNCOMMITTED | possible | possible | possible | | READ COMMITTED | no | possible | possible | | REPEATABLE READ | no | no | **possible** | | SERIALIZABLE | no | no | no | So REPEATABLE READ's distinguishing feature is: rows you have already read will not change under you, but the *set* of rows matching a predicate may. ## What a phantom is A **phantom** is a row that newly satisfies (or stops satisfying) a search condition your transaction already evaluated. You run `SELECT count(*) FROM orders WHERE status = 'NEW'` and get 5. Another transaction inserts a sixth NEW order and commits. You run the same count again and get 6. No row you read changed value; the population changed. That breaks any logic of the form check a condition over a set, then act on the assumption it still holds. ## Why the standard allows it here The level definitions were written with two-phase locking in mind. Under that model: - READ COMMITTED = short-duration shared locks (taken and released per statement). - REPEATABLE READ = long-duration shared locks on the *records read*, held to commit. - SERIALIZABLE = long-duration locks on the *predicate* too. You can lock rows that exist. You cannot lock a row that does not exist yet unless you lock the range or the predicate itself — and that extra machinery is what the standard reserves for the strongest level. Hence phantoms remain legal one level down. ## Why MVCC implementations differ Multiversion concurrency control does not use read locks at all. Each update writes a new row version; each transaction reads through a snapshot that determines which versions are visible. At REPEATABLE READ the snapshot is taken once — typically at the first statement that reads data, not necessarily at BEGIN — and reused for the whole transaction. Under that scheme a phantom is structurally impossible for plain reads: an INSERT committed after your snapshot produced a version your snapshot excludes, exactly as an UPDATE would. The result of a repeated range query is stable. This behaviour is commonly called **snapshot isolation**, and it is strictly stronger than ANSI REPEATABLE READ on the anomaly table while still being weaker than serial execution. That is legal: the standard specifies a ceiling on anomalies, not a floor on strictness. An engine may always give you more isolation than the level name demands. ## The traps this creates **Portability.** The same level name behaves differently across products. Code that relies on phantom-freedom at REPEATABLE READ is correct on a snapshot engine and broken on a pure lock-based one. Conversely, code that relies on seeing other sessions' commits mid-transaction breaks when moved onto a snapshot engine. **Mixed read modes.** In at least one widely used engine, a plain SELECT at this level reads from the transaction's snapshot while a locking read (`SELECT ... FOR UPDATE`, or the read performed inside an UPDATE or DELETE) reads the *latest committed* data and takes range locks. So the same transaction can count 5 rows with a plain SELECT and have an UPDATE affect 6. Knowing which of your statements are locking reads matters more than the level name. **Stronger is not serial.** Removing phantoms does not remove all anomalies. Two transactions can read overlapping stable snapshots, each write a *different* row, and jointly violate an invariant neither could break alone — no read was unstable, no write was lost, yet the outcome matches no serial order. ## How to answer in an interview State the ANSI answer (phantoms allowed), explain the lock-duration reasoning behind it, note that snapshot implementations exceed it, and finish with the practical rule: verify the behaviour on the engine you actually run, because the level name is a promise about a minimum, not a description of a mechanism.
- If a snapshot-based engine gives phantom-freedom for free at REPEATABLE READ, why is SERIALIZABLE still a separate level there?Because phantom-freedom is not serial-equivalence. Two transactions on disjoint but related rows can each read a consistent snapshot, each write, and both commit an outcome no serial order allows — the reads never conflict with the writes at row level, so nothing is detected. SERIALIZABLE adds conflict detection or predicate locking on top to catch precisely those cases.
- When is the snapshot actually taken — at BEGIN or later?Typically at the first statement that reads data, not at BEGIN itself, so an idle open transaction has not yet frozen a view. That matters when you open a transaction early and read late: the snapshot reflects the later moment. It also matters for tests, which often assume BEGIN freezes time.
saying these in an interview costs you the question
- Saying REPEATABLE READ prevents phantoms by definition — the ANSI definition explicitly permits them.
- Saying it never prevents phantoms — snapshot implementations do, for plain reads.
- Treating the ANSI level names as descriptions of a mechanism rather than a maximum set of allowed anomalies.
- Confusing a phantom with a non-repeatable read: a changed value versus a changed row population.
- Assuming plain SELECT and locking reads in the same transaction always see the same data.