The Runtime Theory
mediumApplicationDSA#isolation#transactions#mvcc

Explain the difference between READ COMMITTED and REPEATABLE READ

The interviewer is probing whether you know isolation levels by their anomalies and MVCC mechanisms — snapshot timing, non-repeatable reads, phantoms, and write skew — not by their definitions.

The Runtime Theory Team2 min readasked at cockroachlabs · datadog · stripe

If you answer this with "READ COMMITTED only sees committed data, REPEATABLE READ returns the same rows every read," you've passed the vocabulary test but missed the point. The interviewer wants the mechanism and the anomalies — what the isolation levels are actually for.

Both levels are built on MVCC: row versions carry creator (xmin) and deleter (xmax) transaction IDs, and each transaction sees a snapshot — a cutoff below which versions are visible. The difference is when the snapshot is taken.

READ COMMITTED takes a fresh snapshot for each statement. A transaction that runs SELECT balance FROM accounts WHERE id=1, waits, and runs it again may see different values if another transaction committed in between — that's the non-repeatable read. It can also see a table torn across statements: reading two rows that were consistent at different moments (read skew). The guarantee is narrow and precise: nothing you read is uncommitted, ever, because visibility is filtered through the snapshot at statement start.

REPEATABLE READ takes the snapshot once — at the first read (Postgres) or at transaction start (MySQL with consistent read) — and keeps it for the whole transaction. Every subsequent statement sees the same version of the world. Non-repeatable reads are gone. But the classic SQL standard says phantoms can still appear: a predicate such as WHERE amount > 100 can match new rows inserted by other transactions. In practice, Postgres's MVCC snapshot kills phantoms too — the snapshot excludes any row version committed after it — while MySQL needs gap locks on the index to reach the same result.

The cost is where the senior answer lands. REPEATABLE READ's long-lived snapshot is what creates bloat: versions visible to the snapshot can't be removed, so old tuples pile up and vacuum has to work harder. It also doesn't buy you everything — write skew (two transactions reading the same rows and writing conflicting state) is possible at REPEATABLE READ in Postgres; only SERIALIZABLE closes that gap. And REPEATABLE READ can fail with a spurious serialization error under MySQL's gap locks when two transactions deadlock over a phantom range.

The practical notes: Postgres defaults to READ COMMITTED (you opt into REPEATABLE READ via BEGIN ISOLATION LEVEL REPEATABLE READ), while MySQL defaults to REPEATABLE READ largely because statement-based binlog replication needs the longer snapshot to replay deterministically. Say that and you've shown you know the levels are engineering tradeoffs, not spec trivia.

This answer walks

Follow-ups they'll push on

  1. 01Can write skew happen at REPEATABLE READ? Can it happen at SERIALIZABLE?
  2. 02Why does MySQL default to REPEATABLE READ but Postgres to READ COMMITTED?
  3. 03What makes a phantom row different from a non-repeatable read?

More interviews in this topic

One dispatch a week

The trace behind each question, the tradeoff that explains it, and one technical dispatch per week — no noise.

One technical dispatch per week. No noise.