Isolation, Part 2: An Introduction to MVCC

Building a Database Engine: Transactions & Concurrency

Chapter 6 · Isolation, Part 2: An Introduction to MVCC

Chapter 5 solved isolation with locking: a reader and a writer touching the same page genuinely have to wait for each other. MVCC — multi-version concurrency control — solves the same problem a completely different way: instead of one mutable value per row that everyone fights over, keep every version a row has ever had, tagged with when it was created and when it stopped being current. A reader just picks the version that was current as of the moment its own transaction began, and never has to wait for anyone.

A Real Version Chain, Not One Mutable Value

class Version:
    def __init__(self, value, created_by):
        self.value = value
        self.created_by = created_by   # txn_id that wrote this version
        self.created_ts = None         # commit sequence number -- None until committed
        self.deleted_ts = None         # commit sequence number when superseded -- None if still current

class MVCCStore:
    def __init__(self, detect_conflicts=False):
        self._next_ts = 1
        self.versions = {}             # row_id -> [Version, ...] in commit order
        self.txn_snapshot = {}         # txn_id -> snapshot ts, captured at begin()
        self.txn_pending_writes = {}   # txn_id -> {row_id: Version} -- uncommitted
    

Reading a row means finding the newest version whose created_ts is at or before the reader's own snapshot, and which hadn't yet been superseded (deleted_ts) as of that same snapshot. Writing never touches an existing version at all — it stages a brand-new one, invisible to everyone else until commit.

A Real Bug Found Building the Very First Test: the Snapshot Boundary

The first, most natural way to compute a transaction's own snapshot at begin() looks completely reasonable: self.txn_snapshot[txn_id] = self._next_ts — "whatever the counter currently reads." It's wrong.

Verified directly — a reader's own snapshot appeared to move on its own

A reader begins, reads a row (sees 100), a completely separate transaction commits a new value (999), and the reader reads the same row again — expecting to still see 100, since its own transaction never touched anything else. It saw 999 instead. The bug: _next_ts is the counter value the next commit will claim, not the value of the last commit that actually happened. A reader beginning right after a commit, and a writer whose commit lands moments later, could end up sharing the exact same timestamp — making it genuinely ambiguous whether the writer's commit happened "before" or "after" the reader's own snapshot.

The fix — snapshot = the last ts actually committed, not the next one available

self.txn_snapshot[txn_id] = self._next_ts - 1. A transaction's snapshot should mean "everything committed strictly before I began," never "everything up to and including some commit that might land a moment from now." With this fix, the exact same test correctly returns 100 both times — the reader's snapshot genuinely never moves, no matter what commits happen around it.

Finding 1: A Stable Snapshot, Verified Against a Real Concurrent Commit

Verified directly — the fixed version behaves correctly

Reader begins, reads 100. A separate transaction writes and commits 999. Reader reads again: still 100. A brand-new transaction started after that commit reads 999 immediately. The old reader isn't blocking the writer, and isn't blocked by it either — each transaction simply sees the world as of its own snapshot.

Finding 2: Read-Your-Own-Writes

Verified directly — visible to yourself immediately, invisible to everyone else until commit

A transaction writes 42 to a row and reads it back before committing: sees 42. A different transaction reading the same row at the same moment: sees the old, still-committed value, 1. Uncommitted work is real to the transaction that did it, and doesn't exist to anyone else yet.

Finding 3: Real Threads — Neither Side Ever Waits

A long-running reader holds a transaction open for 0.15s, reading the same row twice. A separate thread commits a new value to that row partway through.

Verified directly — real, measured timing confirms neither side blocks

The reader's two reads both return the original value, 'A' — its snapshot never moves. The writer's own commit() call completes in 0.01ms — it never had to wait for anything. Total wall time for both threads together: ≈0.15s, roughly one hold-duration, not two. Compare this directly against Chapter 5's own Finding 3, where an exclusive writer genuinely had to wait out a shared lock's own full hold-time before it could even begin.

Finding 4: An Honest Limitation — Version History Grows Forever

Verified directly — nothing is ever cleaned up

Fifty separate commits to the same row, one after another: len(store.versions['counter']) reports 51 — every single version, including the 50 that no transaction beginning from this point forward could possibly still need. This engine has no garbage collection for old versions at all. Real systems need one — PostgreSQL's own VACUUM process exists specifically to remove versions no active transaction's snapshot could ever reach anymore. Not attempted here; a real, deliberate scope gap.

Finding 5: MVCC Alone Doesn't Prevent a Lost Update

Two transactions both begin from the same snapshot, both read 100, one computes +10 and the other computes +20, and both commit.

Verified directly — Chapter 5's own race condition, reproduced with zero locking anywhere

Both commits report success. The final balance is 120, not 130 — the first transaction's own +10 is silently gone, overwritten by the second commit, which never knew the first one had happened. Nothing anywhere raised an error. MVCC solved concurrent reads never blocking — it never claimed to solve concurrent writes to the same row on its own.

def commit(self, txn_id):
    snapshot = self.txn_snapshot[txn_id]
    if self.detect_conflicts:
        for row_id in self.txn_pending_writes[txn_id]:
            current = self._current_committed_version(row_id)
            if current is not None and current.created_ts > snapshot:
                raise WriteConflict(
                    f"row {row_id!r} was committed by another transaction after this txn's own snapshot"
                )
    # ... proceed with the commit as before ...
    

Verified directly — first-committer-wins catches it

With the conflict check active, the first transaction commits normally (100 → 110). The second transaction's own commit is rejected — the row it read from was already superseded by a commit that happened after its own snapshot. It must retry: begin a fresh transaction, re-read the real current value (110), and redo its own calculation from there.

Where This Connects

This chapter's finding	What it connects to
Never blocking, verified with real threads	Chapter 5's own Finding 3 — the identical timing-based verification technique, this time proving the opposite property (no wait, not "correctly serialized")
A lost update reproduced under MVCC	Chapter 5's own Finding 1 — the same underlying race, appearing under a completely different concurrency strategy, resolved with a conflict check rather than a lock
Unbounded version growth	An honest, deliberate scope gap — a real vacuum/garbage-collection pass is a substantial feature of its own, not attempted in this course

Hands-On Exercises

Exercise 1

With conflict detection enabled, run a transaction that only reads a row (never writes to it), let a separate transaction commit a change to that same row, and then commit the read-only transaction. Confirm it commits with no conflict at all, and explain why a read-only transaction can never trigger the conflict check.

📄 View solution

Exercise 2

With conflict detection enabled, run two transactions from the same starting snapshot, but have each one write to a different row instead of the same one. Confirm both commit successfully with no conflict, and explain why the conflict check being per-row (not per-transaction or per-database) makes this the correct outcome.

📄 View solution

Exercise 3

Reproduce Finding 5's own lost-update scenario with conflict detection enabled, catch the resulting WriteConflict, and then retry the losing transaction properly — begin a brand-new transaction, re-read the current value, and redo the calculation. Confirm the final balance correctly reflects both increments this time, with nothing lost.

📄 View solution

Chapter 6 Quick Reference

MVCC's core idea: keep every version of a row, tagged with when it was created and superseded — readers pick the version current as of their own snapshot
Real bug found: a transaction's snapshot must be the last commit ts that already happened, not the next one available — off by one, and a genuinely wrong snapshot boundary
Verified Finding 1: a reader's snapshot stays stable across concurrent commits; a fresh transaction sees the new value immediately
Verified Finding 2: read-your-own-writes — visible to yourself before commit, invisible to everyone else
Verified Finding 3: real threads confirm neither a long reader nor a concurrent writer ever blocks the other
Verified Finding 4: version history grows without bound — no garbage collection, an honest scope gap
Verified Finding 5: MVCC alone still allows a lost update between concurrent writers to the same row — fixed with a first-committer-wins conflict check
Next chapter: Multi-Table Support & Foreign Keys — extending this engine beyond a single table