How MVCC Works: Snapshots, Version Chains, and Why Readers Don't Block Writers

A long-running report wants a consistent view of every row it started reading. A checkout wants to update one of those rows at the same moment. If the report took a lock on every row it touched, the checkout would wait. If the checkout overwrote the row in place, the report would see a mix of old and new values.

Multi-version concurrency control (MVCC) keeps more than one version of a row on purpose. A reader picks versions that belong to its snapshot. A writer creates a new version instead of erasing the old one. Readers and writers stop fighting over the same physical bytes.

This article is about that design: snapshots, xmin/xmax-style visibility, version chains, write-write conflicts, and the garbage that versions leave behind. It is not a second tour of write-ahead logging, LSM compaction, two-phase commit across services, or B-tree lookup. Those appear only where they touch visibility. The search intent here is one question: how does a database let readers see a stable past while writers keep changing the present?

The contract

MVCC answers a visibility question for every row version a scan encounters:

• Which transaction created this version?
• Which transaction, if any, deleted or replaced it?
• Had those transactions already committed when this reader started (or when it asked for a snapshot)?

If the creating transaction is visible and the deleting transaction is not, the version is live for that reader. Otherwise it is skipped. The physical row is not locked just because someone is reading it.

That contract is different from two-phase locking. Two-phase locking serializes access to a single copy of the row. MVCC serializes writers of the same row and lets readers travel on older copies.

A row is a chain of versions

Think of a primary key acct=42 with balance 100. Transaction T10 updates it to 80. Transaction T20 later updates it to 75.

On disk you do not have one mutable tuple. You have versions:

v1: acct=42, bal=100, created_by=T1,  deleted_by=T10
v2: acct=42, bal=80,  created_by=T10, deleted_by=T20
v3: acct=42, bal=75,  created_by=T20, deleted_by=none

Implementations differ in layout. PostgreSQL stores versions as heap tuples with xmin (creator) and xmax (deleter) transaction IDs, plus hint bits and an optional user-level lock. InnoDB keeps older versions in an undo log and walks backward from the clustered index record. WiredTiger and several LSM engines keep timestamps on keys and drop obsolete versions during compaction.

The logical picture is the same. An update is an insert of a new version plus a mark that the previous version ended. A delete is only the mark.

What a snapshot actually is

A snapshot is not a copy of the database. It is a compact description of which transactions are in the past for this reader.

A typical snapshot records:

• the newest transaction ID that had been assigned when the snapshot was taken (or a high-water timestamp)
• the set of transactions that were still in progress at that moment
• sometimes a catalog of transactions that aborted

Visibility then becomes arithmetic and set membership. Version V created by Tx is visible if Tx committed and Tx is not in the in-progress set and Tx is not after the snapshot horizon. Version V deleted by Ty is invisible once Ty is likewise visible as committed.

PostgreSQL takes this snapshot at the start of a statement in READ COMMITTED and at the start of the transaction in REPEATABLE READ and SERIALIZABLE. That is why a REPEATABLE READ transaction can keep seeing balance 100 after T10 commits: T10 was in progress, or not yet committed, when the snapshot froze.

InnoDB's REPEATABLE READ uses a similar idea with ReadView and undo. Oracle's read consistency reconstructs blocks from undo as of a System Change Number (SCN).

Why readers do not block writers

A reader never needs to stop a writer from creating v2. The reader is looking at v1. The writer appends v2 and sets v1's deleted-by field. As long as v1 stays on the page or in undo until every snapshot that can still see it has ended, the reader is safe.

Writers can still block other writers on the same row. If T10 and T20 both try to update acct=42, one of them must win. Common rules:

• wait for the first writer to commit or abort, then decide
• abort the second writer immediately (first-updater-wins)
• in SERIALIZABLE systems, abort on a later check if the write set overlaps a read that must not have seen the write

MVCC removes the read-write conflict. It does not remove the write-write conflict. That distinction is the whole point.

A walk through one select and one update

Reader R starts at snapshot S = {horizon=T15, in_progress={T12, T14}}.

It finds v1 created by T1 (committed long ago) and deleted by T10. T10 committed as T10, which is before T15 and not in progress. So v1 is dead for R.

It finds v2 created by T10 and deleted by nobody yet. T10 is visible. v2 is live. R returns balance 80.

Meanwhile T20, whose ID is 20, updates the row. It writes v3, sets v2.deleted_by = T20, and commits. R is still running. R does not restart. Its next fetch of the same row, under the same snapshot, still refuses v3 because T20 is after the horizon. It still accepts v2 because T20's delete is not visible.

That is snapshot isolation in one scene. The report is coherent. The checkout is not waiting on the report.

Isolation is not one thing

People say "we use MVCC" as if that named a single isolation level. It does not.

READ COMMITTED plus MVCC means each statement gets a new snapshot. A transaction can see a row that another transaction committed between its first and second statement. Lost updates are possible unless the statement uses SELECT ... FOR UPDATE or an atomic UPDATE.

REPEATABLE READ plus MVCC means one snapshot for the transaction. Phantom-style surprises shrink. Write skew can still happen: two transactions each read a disjoint pair of rows, each write one row, and the combination violates a constraint that neither transaction saw broken. Classic example: two doctors go on call, each sees that the other is on duty, each goes off duty.

SERIALIZABLE on an MVCC engine adds conflict tracking. PostgreSQL SSI records read and write sets and aborts a transaction that would close a dangerous pivot. It does not fall back to holding every read lock for the whole transaction.

If you need a lock, ask for a lock. MVCC will not invent one.

Indexes have to play the same game

A secondary index cannot point only at "the" row. It points at a version, or at a heap location that then applies visibility, or at a timestamped key.

PostgreSQL heap indexes store tuple identifiers. The index can return a pointer to a dead version. The heap check applies xmin/xmax. Hot updates that stay on the same page can avoid a new index entry; updates that move the tuple cannot.

InnoDB secondary indexes store the primary key and, for deleted versions, rely on the clustered record plus undo. LSM engines often put the timestamp in the key so compaction can drop older timestamps once no reader needs them.

An index-only scan is only legal if visibility can be decided without the heap. PostgreSQL's visibility map exists for that reason: if a page is all-visible to every current snapshot, the index need not visit the heap tuple.

Garbage is the bill

Every extra version occupies space and slows scans. Something must delete versions that no living snapshot can see.

PostgreSQL VACUUM walks tables, freezes old transaction IDs so the 32-bit counter can wrap safely, and prunes tuples whose xmax is visible to all. If VACUUM lags, tables bloat and transaction ID wraparound becomes an operational emergency.

InnoDB purge reads the undo history list and drops records older than the oldest active ReadView. Long-running transactions freeze purge. Temporary undo growth is the symptom.

LSM compaction drops keys whose timestamp is below the oldest reader timestamp, subject to snapshot pins from the storage engine.

A six-hour analytics transaction is therefore not free just because it takes no row locks. It pins old versions. Design the snapshot lifetime on purpose.

Where WAL and replication fit

MVCC does not make a commit durable. The write-ahead log still records the new version and the deletion mark before commit returns. Recovery rebuilds the newest versions; visibility metadata has to come back with them.

Physical replication copies pages or log records that already contain xmin/xmax or undo. Logical replication often emits a row change as a single new image. Subscribers reconstruct their own versions.

Distributed atomic commit is a different contract again. Two-phase commit decides whether several resource managers accept a transaction. Each manager may still use MVCC locally after the decision. Raft and 2PC do not replace snapshots.

Misconceptions

"MVCC means no locks." Row-level exclusive locks still exist for updates, foreign keys, and SELECT FOR UPDATE. Predicate locks or SSI checks exist for serializable. MVCC removes the common case where a reader blocks a writer.

"A snapshot is a consistent backup of every page." It is a visibility cut. Pages keep changing. Readers reconstruct the past from versions that have not been vacuumed yet.

"If I use REPEATABLE READ I cannot get write skew." You can. Snapshot isolation is not serializability. Write skew is the usual leftover anomaly.

"Vacuum is optional housekeeping." Without it, versions accumulate, indexes bloat, and in PostgreSQL the transaction ID space can exhaust. That is a correctness problem, not a cosmetic one.

"LSM trees do not need MVCC." They still need a rule for which timestamp a reader may see. Compaction is their vacuum.

Takeaways

MVCC stores several versions of a row and answers visibility with a snapshot: who created this version, who ended it, and which of those transactions were already committed for this reader.

Readers stop blocking writers because they do not share a single mutable copy. Writers of the same key still conflict.

The cost is leftover versions. Vacuum, purge, and compaction exist because snapshots pin history. Isolation level chooses how often the snapshot refreshes and whether write-set conflicts are checked later.

When a query "sees old data," ask which snapshot it holds and which version chain it walked. That question is more precise than "the database is eventually consistent."

Related reading on this site: How Write-Ahead Logging Works, How LSM-Trees Work, How Two-Phase Commit Works, Database Indexing Explained, How Databases Work Internally.