Start here: locks make everyone wait#
TL;DRthe 30-second version
- A database must isolate concurrent transactions so one never sees another's uncommitted work, and a transaction's own reads stay stable while it runs. The classic way — locking each row so readers and writers take turns — is correct but slow: a reader blocks writers and a writer blocks readers, so contention crushes throughput.
- MVCC keeps multiple versions of each row instead of one. A write doesn't overwrite the old value; it appends a new version tagged with the transaction that made it and the moment it became visible to others.
- Each transaction takes a snapshot of the commit timeline when it begins, and for its whole life reads only versions committed at or before that snapshot. So readers read old versions while writers append new ones — reads never block writes, writes never block reads.
- The one thing MVCC can't wave away: if two transactions blindly write the same row and both try to commit, one must lose, or a write would be lost. Snapshot isolation resolves this with first-committer-wins — the later committer aborts.
Run two transactions at the same time and they can corrupt each other's view of the data unless the database steps in. Isolation (the I in ACID — atomicity, consistency, isolation, durability) is the guarantee that concurrent transactions don't interfere — each behaves as if it had the database to itself. Without it you get anomalies with specific names: a dirty read (seeing another transaction's uncommitted change, which might then roll back), a non-repeatable read (running the same query twice in one transaction and getting different answers because someone committed in between), and phantoms (a range query returning new rows on a second look).
The straightforward way to prevent all this is locking. Give each row a single value, and make a transaction take a lock before touching it: a shared lock to read, an exclusive lock to write. Two readers can share, but a writer needs everyone else out. This is correct, but it means a reader and a writer of the same row block each other. A long analytics query holding read locks can stall every writer behind it; a slow writer can stall every reader. Under real contention, transactions spend their time waiting in line rather than doing work.
The fix: keep every version#
MVCC stops treating a row as a single cell. Instead, each row is a chain of versions. When a transaction updates a row, it does not overwrite the current value — it appends a new version, tagged with the transaction that created it. The old version stays exactly where it was. So at any moment a row can hold several versions at once: the last committed value, plus any newer versions that in-flight transactions have written but not yet committed.
To decide which version each transaction should see, the database keeps a commit clock — a counter that ticks forward every time a transaction commits — and stamps each committed version with the commit-time at which it became visible. When a transaction begins, it records the current value of that clock as its snapshot. From then on, the rule for reads is simple: a transaction sees the newest version of a row whose commit-time is at or before its snapshot. Newer versions exist, but they're in the transaction's future, so it ignores them.
This is what buys the concurrency. A reader never has to wait for a writer, because the writer is off appending a brand-new version while the reader keeps reading the old committed one. And a writer never has to wait for a reader, because it isn't touching the version the reader is looking at. Reads don't block writes; writes don't block reads. Two guarantees fall out for free: a transaction always sees its own uncommitted writes (read-your-writes — its new version is visible to itself), and it never sees anyone else's uncommitted writes (no dirty reads — an uncommitted version has no commit-time, so no other snapshot can select it).
What the snapshot guarantees — and what it doesn't#
Because a transaction's snapshot is fixed at begin and never moves, every read it does sees the same consistent picture of the whole database — the set of everything committed before it started. Run the same query twice and you get the same answer (no non-repeatable reads); a range query won't sprout new rows (no phantoms); and you never see uncommitted data (no dirty reads). This package of guarantees is called snapshot isolation. It's the isolation level MVCC most naturally provides — though many databases (PostgreSQL, Oracle) still default to the weaker Read Committed and offer full snapshot isolation only as an opt-in level.
The one place it says no: write-write conflicts#
MVCC lets writers run without blocking — but that freedom creates a problem it has to resolve at the end. Suppose two transactions both begin at the same snapshot, both read a balance of 100, and both want to update it. If both were allowed to commit, one of the two updates would silently vanish — a lost update. So the database has to stop that, and because writers never blocked each other on the way in, it can only catch the collision at commit time.
The rule is first-committer-wins. When a transaction tries to commit, the database checks each row it wrote: has anyone else committed a new version of that row since this transaction's snapshot? If yes, this transaction is too late — it based its write on a value that's now stale, so it aborts and its versions are discarded. The application gets a serialization error (the database's signal to run the transaction again) and typically retries. The first transaction to commit wins the row; the second one is turned away. That abort is the price of never having blocked the writers up front, and it's exactly what prevents the lost update.
PredictT1 and T2 both begin when a counter reads 100. Each reads it, adds 1, and writes 101. T1 commits first. What happens when T2 commits, and why does it matter?
Hint: Both read 100 and both want to write 101. If both commit, what's the final value — and what should it have been?
T2 aborts. When T2 tries to commit, the database sees that the counter was committed by T1 (to 101) after T2's snapshot, so T2's write is based on a stale read — it would overwrite T1's update and lose it. Under first-committer-wins, T1 (the first committer) keeps its version and T2 is aborted with a serialization error; the application retries T2, which now reads 101 and correctly writes 102. This matters because without the abort, both transactions would write 101 and one increment would be silently lost — the counter would end at 101 instead of 102. The abort is how snapshot isolation turns a would-be lost update into a safe retry.
What versions cost: garbage#
Keeping old versions isn't free — they pile up. Every update leaves a dead version behind, and those old versions can only be thrown away once no active transaction's snapshot could still need them. Reclaiming them is a background job: PostgreSQL calls it VACUUM, InnoDB does it via purge threads on its undo logs. If it keeps up, dead versions are recycled and the storage stays flat.
So MVCC trades space and background I/O for its concurrency: more storage for the version chains, plus a vacuum/purge process that has to keep pace with the write rate. For the overwhelming majority of workloads that's a bargain — a bit of garbage collection in exchange for readers and writers that never block each other.
MVCC vs locking#
| Two-phase locking | MVCC (snapshot isolation) | |
|---|---|---|
| Reader vs writer | Block each other | Never block each other |
| What a read sees | The latest committed value (waits for locks) | A consistent snapshot as of its start |
| Main cost | Waiting / reduced concurrency | Storage for old versions + garbage collection |
| Write-write conflicts | Blocked by locks up front | Detected at commit → one aborts |
| Weak spot | Deadlocks, low throughput under contention | Write skew (not serializable without extra checks) |
The two approaches sit at opposite ends. Pure two-phase locking — the strict locking scheme where a transaction takes all its locks as it goes and releases them only at the end — is pessimistic: assume conflict, take a lock, make others wait. MVCC is optimistic for reads: assume no conflict, let everyone read their own snapshot, and only pay at commit time when two writers actually collide. Most databases are hybrids — they use MVCC for reads and still take row locks for writes (which is why `SELECT ... FOR UPDATE` exists: it lets you opt back into locking a row you intend to update, avoiding the commit-time abort).
In the wild
- PostgreSQL stores versions inline in the table: each row carries xmin (the transaction that created it) and xmax (the transaction that deleted/superseded it), and VACUUM reclaims dead versions. Its default isolation is Read Committed; it also offers true Serializable via SSI.
- MySQL's InnoDB keeps the current row in place and older versions in a separate undo log, reconstructing the right version for each transaction's read view. Its default isolation level is Repeatable Read, implemented with MVCC.
- Oracle pioneered this approach with rollback/undo segments and multiversion read consistency; the 'ORA-01555: snapshot too old' error is what happens when a long query needs an old version that was already overwritten and recycled.
- Distributed databases like CockroachDB, YugabyteDB, and Google Spanner build MVCC on timestamps (Spanner uses TrueTime), so a snapshot is literally 'the database as of time T' — enabling consistent reads across many machines without global locks.
- The SQL isolation levels (Read Committed, Repeatable Read, Serializable) are the knob that sits on top of all this: they control how a transaction's snapshot is taken and how strictly conflicts are checked.
Pitfalls & gotchas
Is snapshot isolation the same as serializable?
No. Snapshot isolation prevents dirty reads, non-repeatable reads, and phantoms, but it still allows write skew — two transactions that read an overlapping set and each write a different row can both commit yet jointly break an invariant. Serializable forbids that too. Getting serializability on an MVCC engine takes extra conflict detection (e.g. PostgreSQL's SSI), which most systems make opt-in because it costs more.
Does MVCC mean there are no locks at all?
No — it means reads don't take locks. Writes still coordinate: two transactions writing the same row will conflict, resolved either by a row lock taken at write time or by a first-committer-wins abort at commit time, depending on the engine and isolation level. MVCC removes reader/writer blocking, not writer/writer coordination. `SELECT ... FOR UPDATE` deliberately locks rows you plan to update.
Why did a long-running report cause my database to bloat?
Because old versions can't be reclaimed while any snapshot might still need them. A long transaction pins the garbage-collection horizon at the moment it began, so every version created since then must be kept, even as the rest of the system moves on. Tables and indexes grow, and vacuum can't clean up until the transaction ends. This is why leaving transactions open is dangerous in MVCC systems.
What's the difference between a dirty read, a non-repeatable read, and a phantom?
A dirty read sees another transaction's uncommitted change (which might roll back). A non-repeatable read is when a row you read changes value if you read it again within the same transaction (someone committed in between). A phantom is when a range query returns a different set of rows on a second look (someone inserted or deleted matching rows). Snapshot isolation prevents all three by pinning your reads to one snapshot.
QuizUnder snapshot isolation, transaction T1 begins and reads x = 5. While T1 is still running, T2 updates x to 10 and commits. T1 then reads x again. What does T1 see?
- 10 — reads always return the latest committed value.
- 5 — T1 reads from the snapshot it took when it began, and T2 committed after that.
- An error — the value changed underneath T1.
- It depends on whether T1 has written to x.
Show answer
5 — T1 reads from the snapshot it took when it began, and T2 committed after that. — T1 sees 5 again. Its snapshot was fixed when it began, before T2 committed, so T2's version of x (committed at a later commit-time) is outside T1's snapshot and invisible to it. This is snapshot isolation's repeatable read: T1's view of the database is frozen at its start, so re-reading x returns the same 5 no matter what commits alongside it. A transaction that began after T2's commit would see 10.
In an interview
Lead with the one idea and everything follows: MVCC keeps multiple versions of each row so a transaction can read a consistent snapshot without locking out writers. Then show you know where the abstraction leaks — the write-write abort and the vacuum cost — because that's what separates a memorized definition from understanding.
- State the core rule: a write appends a new version; each transaction reads the newest version committed at or before its snapshot. That's why reads don't block writes and writes don't block reads.
- Name the guarantees snapshot isolation gives — no dirty reads, no non-repeatable reads, no phantoms — and the one it doesn't: write skew (so it's weaker than serializable).
- Explain the write-write conflict: two blind writers can't both commit, so first-committer-wins aborts the later one. That's the price of not blocking writers up front.
- Mention the cost: old versions are garbage that must be reclaimed (VACUUM/purge), and a long-running transaction holds that garbage collection back and bloats the database.
- Connect it: MVCC is often paired with a write-ahead log for durability; Postgres uses xmin/xmax, InnoDB uses undo logs, and distributed stores (Spanner, CockroachDB) do MVCC over timestamps.
PredictAn interviewer asks: 'We run big analytics reports against our production OLTP (transaction-processing) database and they seem to be causing write contention and bloat. What's going on, and what would you do?' What's the strong answer?
Hint: What does a long transaction do to old row versions, and why can't they be cleaned up?
The reports are long-running transactions, and under MVCC that's expensive in two ways. First, each report holds a snapshot open for its whole duration, which pins the garbage-collection horizon: every row version created since the report began must be retained in case the report reads it, so tables and indexes bloat and vacuum can't keep up — that's your bloat. (It's not classic lock contention, since MVCC reads don't block writes; the 'contention' is really the GC pressure and I/O from the version buildup.) The fixes: run heavy analytics against a read replica or a snapshot/standby instead of the primary, so the long snapshots don't pin the primary's vacuum horizon; keep report transactions as short as possible or chunk them; and make sure autovacuum is tuned to keep pace. The framing to show you understand it: in MVCC the cost of a long read isn't blocking, it's holding old versions alive — so move long reads off the write path.
References
- PostgreSQL — Concurrency Control (MVCC) — The canonical explanation of snapshots, isolation levels, and VACUUM.
- MySQL — InnoDB Multi-Versioning — How InnoDB uses undo logs and read views to reconstruct versions.
- Berenson et al. — A Critique of ANSI SQL Isolation Levels (1995) — The paper that defined snapshot isolation and named write skew.
- Designing Data-Intensive Applications, Ch. 7 — Kleppmann's clear treatment of isolation levels, SI, and serializability.
- CMU 15-445 — Multi-Version Concurrency Control — Andy Pavlo's database course lectures on MVCC internals and version storage.
Ready to try it?
The simulator is a real, deterministic implementation — pick a scenario and step through it, scrubbing the timeline forward and backward through every change.