MVCC — Multi-Version Concurrency Control

Locking protects data but pessimistically: readers block writers, writers block readers. Multi-Version Concurrency Control (MVCC) unlocks the contention: every write creates a new version, and every read picks the right version. Readers never wait for writers, writers never wait for readers.

The Core Idea

Instead of overwriting a row in place, MVCC keeps the chain of past versions. Each version is tagged with:

• xmin: the transaction id that created it
• xmax: the transaction id that obsoleted it (or unset)
• commit_ts: the logical timestamp at which it became visible to other transactions

A read at snapshot s walks the chain newest-to-oldest and returns the first version where commit_ts <= s (or the reader's own uncommitted write).

key "x" → version_chain (newest first)
  [ value="A3"  xmin=T7  commit_ts=12 ]   ← T7 wrote it, committed at logical clock 12
  [ value="A2"  xmin=T5  commit_ts=8  ]   ← T5 wrote at 8
  [ value="A1"  xmin=T3  commit_ts=4  ]

A transaction T2 that started at snapshot_ts=10 reads "x":

• commit_ts=12 > 10 → skip
• commit_ts=8 ≤ 10 → return "A2" ✓

A transaction T9 with snapshot_ts=12 sees "A3". Both reads happen in parallel with no blocking and no inconsistency.

Why This is Powerful

PostgreSQL, Oracle, MySQL InnoDB, MS SQL Server's read-committed snapshot, CockroachDB, TiDB — they all use MVCC. The cost is storage (old versions stick around) and garbage collection (vacuum/undo segment reclaim).

Garbage Collection

A version is unreachable when no live snapshot can see it AND a newer visible version exists. The lowest snapshot among active transactions defines the cutoff: anything older AND superseded can be reclaimed. Postgres runs VACUUM; Oracle expires UNDO segments; CockroachDB uses GC TTL.

Conflicts Still Happen

MVCC eliminates read-write conflicts but write-write conflicts must still serialize. The two common approaches:

• First-committer-wins: the second writer sees the conflict at commit time and aborts.
• Pessimistic write locks: take an exclusive lock on the row when writing (Postgres does this).

In this lesson you'll build the version-chain machinery that makes snapshot reads possible.