Start here: locks make everyone wait#
TL;DRthe 30-second version
- Locking each row keeps transactions apart, but it's slow: a reader blocks writers and a writer blocks readers.
- MVCC keeps multiple versions of each row. A write appends a new version instead of overwriting the old one.
- Each transaction takes a snapshot when it begins and reads only versions committed before that. So reads never block writes, and writes never block reads.
- The one thing it can't wave away: two transactions that blindly write the same row can't both commit. First-committer-wins, and the later one aborts.
Isolation (the I in ACID) is the guarantee that each transaction behaves as if it had the database to itself. Without it, a transaction can see another's uncommitted change, or get a different answer when it runs the same query twice.
The straightforward way to prevent this is locking. A transaction takes a lock before touching a row: a shared lock to read, an exclusive lock to write. Two readers can share, but a writer needs everyone else out. So 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. 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. Each row is a chain of versions. When a transaction updates a row, it appends a new version tagged with the transaction that created it. The old version stays where it was.
To decide which version each transaction sees, the database keeps a commit clock. It's a counter that ticks forward every time a transaction commits, and each committed version is stamped with the commit-time at which it became visible. When a transaction begins, it records the current clock value as its snapshot. A transaction then sees the newest version of a row whose commit-time is at or before its snapshot. Newer versions are in its future, so it ignores them.
The writer appends a new version while the reader keeps reading the old committed one, so neither waits for the other. And nobody sees anyone else's uncommitted writes, because an uncommitted version has no commit-time, so no snapshot can select it.
There is one place MVCC has to say no. If two transactions blindly write the same row and both commit, one update silently vanishes. That's a lost update. Because writers never blocked each other on the way in, the database can only catch the collision at commit time. The rule is first-committer-wins. When a transaction commits, the database checks each row it wrote: has anyone else committed a new version since this transaction's snapshot? If yes, this transaction aborts with a serialization error, and the application retries it. On a hot row that everyone writes, retries pile up.
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?
T2 aborts. The database sees that T1 committed the counter after T2's snapshot, so T2's write is based on a stale read. T1 keeps its version and T2 gets a serialization error. The application retries T2, which now reads 101 and writes 102. Without the abort, both would write 101 and one increment would be lost.
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: everything committed before it started. So there are no dirty reads, no non-repeatable reads, and no phantoms. This package of guarantees is called snapshot isolation.
Snapshot isolation is not the same as serializable. Serializable means transactions behave as if they ran one at a time. Snapshot isolation is slightly weaker, and the anomaly it allows is called write skew. Two transactions each read an overlapping set of rows, then each writes a different row. Because they wrote different rows, neither trips the write-write check, so both commit. Together they break a rule that each alone would have kept. The classic example: two doctors each check that at least one other doctor is still on call, and each takes themselves off call.
What versions cost#
Old versions pile up. Every update leaves a dead version behind, and it can only be thrown away once no active snapshot could still need it. Reclaiming them is a background job. PostgreSQL calls it VACUUM, and stores versions inline in the table with each row carrying xmin (the transaction that created it) and xmax (the one that superseded it). InnoDB keeps older versions in a separate undo log and purges them in the background. A single long-running transaction holds this cleanup back, because every version committed after it began must stay alive. Oracle's famous 'snapshot too old' error is the same tension from the other side: the old version a long query needed was already recycled.
| 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) |
If this comes up in an interview#
Is snapshot isolation the same as serializable?
No. Snapshot isolation prevents dirty reads, non-repeatable reads, and phantoms, but it still allows write skew. Serializable forbids that too. Getting serializability on an MVCC engine takes extra conflict detection, like PostgreSQL's SSI, and most systems make it 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 at write time or by a first-committer-wins abort at commit time. `SELECT ... FOR UPDATE` deliberately locks rows you plan to update.
Why did a long-running report cause my database to bloat?
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. The fix is to run heavy analytics against a read replica, keep report transactions short, and make sure autovacuum keeps pace.
What's the difference between a dirty read, a non-repeatable read, and a phantom?
A dirty read sees another transaction's uncommitted change. A non-repeatable read is when a row you read changes value if you read it again in the same transaction. A phantom is when a range query returns a different set of rows on a second look. Snapshot isolation prevents all three by pinning your reads to one snapshot.
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.