Inside one database transaction you run SELECT balance FROM accounts WHERE id = 7 twice and get two different values because another transaction committed a change in between. What is that anomaly called, and what does the REPEATABLE READ isolation level do about it?
answer
- re-read differs = non-repeatable read
- stability unit: statement -> transaction
- S-locks held to commit, or one fixed snapshot
- phantoms are a separate anomaly
- single statement gains nothing
basics
~20 sThat is a non-repeatable read. REPEATABLE READ gives a transaction a stable view of rows it has read — a fixed snapshot, or read locks held until commit — so the second SELECT returns the same value as the first.
solid answer
~50 sSeeing a different value on a re-read is a **non-repeatable read** (fuzzy read): between my two reads another transaction committed an update to that row, and my isolation level let me see it. REPEATABLE READ eliminates it. The level's contract is per-transaction read stability: once my transaction has read a row, every later read of that row inside the same transaction returns the same committed value, no matter who commits meanwhile. Engines deliver that in one of two ways. A lock-based engine takes a shared lock on each row it reads and holds it to commit, so nobody can update it underneath. An MVCC engine pins one snapshot — a point-in-time view of committed data — at the transaction's first read and answers every later read from it; writers are not blocked, they just create versions my snapshot ignores. What it does not necessarily fix is **phantoms**: rows that newly start matching a range predicate.
code
text · 5 linesT1: BEGIN;
T1: SELECT balance FROM accounts WHERE id = 7; -- 100
T2: BEGIN; UPDATE accounts SET balance = 40 WHERE id = 7; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 7; -- READ COMMITTED: 40 | REPEATABLE READ: 100
T1: COMMIT;go deeper
Name the anomaly (non-repeatable read) and state the guarantee: the same row read twice in one transaction gives the same value.
Add the mechanism — shared locks held to commit versus one fixed snapshot — and note that phantoms are a separate question.
Discuss which implementation your engine uses, what it means for blocking versus stale-write conflicts, and how long such transactions may live.
Frame it as choosing where consistency is enforced: read stability at the engine versus revalidation in application logic, and the cost each imposes under contention.
## The anomaly has a name A transaction that reads the same row twice and sees two different committed values has suffered a **non-repeatable read** (fuzzy read). It is one of three classic read anomalies: - **Dirty read** — you see data written by a transaction that has not committed and may roll back. - **Non-repeatable read** — you re-read a row you already read and its committed value changed. - **Phantom** — you re-run a *range* query and rows appear or disappear because someone inserted or deleted rows matching your predicate. The first is prevented from READ COMMITTED upward. The second is exactly what REPEATABLE READ adds. ## Why the anomaly happens at weaker levels At READ COMMITTED, the unit of stability is the *statement*, not the transaction. Each statement sees the data committed as of the moment that statement started (in an MVCC engine) or takes a short read lock and releases it immediately (in a lock-based engine). Between your two SELECTs there is a window where another transaction can update the row and commit. Your second statement then honestly reports the newest committed state — which differs from the first. That is fine for a single-statement read. It is a bug generator for multi-statement logic: read a balance, decide something in application code, read again to build the response, and the two reads disagree. ## What REPEATABLE READ changes REPEATABLE READ moves the unit of stability from the statement to the transaction. Everything the transaction reads stays as it was first observed, for the transaction's whole life. **Lock-based implementation (strict two-phase locking).** Reading a row acquires a shared (S) lock; the lock is held until commit or rollback rather than released at end of statement. Another transaction wanting to update that row must take an exclusive lock, which conflicts, so it waits. Readers block writers. Deadlocks become more likely because locks are held longer. **MVCC / snapshot implementation.** The engine keeps multiple versions of each row, each stamped with the transaction that created it. When your transaction takes its first read, the engine records a snapshot: which transactions counted as committed at that instant. Every read in the transaction filters row versions against that one snapshot. Other transactions update the row freely, creating newer versions your snapshot cannot see. Nothing blocks; the price is version storage and cleanup. The visible contract — my re-read returns what I saw before — is the same either way; the operational behaviour is very different. ## What it still does not give you Read stability is not serial execution. Two transactions can each read a stable view, each decide something based on it, and both commit a result that no serial ordering would have produced. Under the ANSI definition, REPEATABLE READ may also still show phantoms on range queries. And a transaction that reads-then-writes may find, on a snapshot engine, that the row it based its decision on was changed by someone else meanwhile — the engine handles that at write time, not read time. ## Practical shape Use REPEATABLE READ when one transaction must make several reads that have to agree with each other: a report over several tables, a multi-step consistency check, an export. For a single statement it buys nothing over READ COMMITTED, because a single statement already has one consistent view of the data. Keep such transactions short: on a lock-based engine they hold locks for their whole lifetime; on an MVCC engine they hold back version cleanup for their whole lifetime.
- Does REPEATABLE READ also stop the row from changing on disk, or just from being visible to you?On an MVCC engine it only controls visibility: other transactions really do update and commit the row, they just write new versions your snapshot ignores, so your value is stale by the time you commit. On a lock-based engine it genuinely prevents the change, because the writer blocks on your held shared lock until you finish. Same read guarantee, opposite effect on other sessions.
- If a transaction only runs one SELECT, is REPEATABLE READ worth setting?No. A single statement already sees one consistent view of the data under READ COMMITTED, so there is nothing to stabilise across. You would only pay the costs — longer-held locks or a longer-lived snapshot. The level pays off from the second read onward, or when a read is followed by a write that depends on it.
READ COMMITTED is asking a colleague for the number again each time and getting today's answer; REPEATABLE READ is photocopying the page once and reading your copy all day.
saying these in an interview costs you the question
- Calling it a dirty read — nothing uncommitted was involved; the other transaction committed.
- Claiming REPEATABLE READ makes the transaction see other sessions' latest committed data — it deliberately does the opposite.
- Assuming the level makes the whole transaction serial or read-modify-write safe with no further checks.
- Thinking it prevents every anomaly, including phantoms, by definition.
- Believing MVCC snapshots block other writers.