--- title: "MVCC" description: "Watch snapshots choose row versions while readers overlap updates and cleanup waits." source: https://vizlearn.app/cs/databases/mvcc access: premium (interactive player requires Lifetime; this doc is free) --- # MVCC Multiversion Concurrency Control (MVCC) lets readers and writers overlap by retaining multiple logical versions of a row. A read does not simply take the newest stored value. It takes a snapshot, scans versions newest to oldest, and returns the first version whose creator and superseder are compatible with that snapshot. If autovacuum is running but an update-heavy PostgreSQL table keeps accumulating dead row versions, inspect long-running transactions and old snapshots. This lesson shows the mechanism behind that symptom: stable reads need history, and a pinned cleanup horizon can make that history pile up. The default animation follows one account row whose balance begins at `$100`. Reader T1 takes snapshot S1. Writer T2 later creates a `$120` version and commits it. T1 still reads `$100`, while a later reader with a fresh snapshot reads `$120`. Both results are correct because the readers ask different committed worlds. ## How MVCC Works This lesson uses a small, PostgreSQL-flavored visibility rule. Each logical row version records: - the transaction that created it; - whether that creator is in progress, committed, or aborted; - the transaction that superseded it, if any; and - whether that superseder is visible in the reader's snapshot. A version passes the lesson's two visibility gates when its creator is committed in snapshot S and its superseder is absent or not committed in S. The reader scans from newest to oldest and selects the first passing version. Suppose V0 stores `$100` and was created by committed T0. T1's first `SELECT` takes S1 with committed set `{T0}`. V0 passes both gates: T0 is committed in S1, and no transaction has superseded V0 in S1. T2 then runs an update. The animation does not overwrite V0. It first records that T2 supersedes V0, then appends V1=`$120`, created by T2. While T2 is in progress, V1 fails the creator gate. V0 still passes because T2 is not committed in S1. T1 can therefore return `$100` without waiting for the writer in this model. After T2 commits, its status changes, but S1 does not. V1 still fails for T1 because T2 is absent from S1's committed set. V0 still passes because its superseder is absent from that same set. A later snapshot S2 includes `{T0,T2}`, so V1 passes and becomes the newest visible version for that reader. The important distinction is between storage state and visibility state. V0 and V1 can both be stored. V1 can be current for fresh snapshots while V0 remains the right answer for an older active snapshot. ## MVCC Step by Step 1. The row begins with V0=`$100`, created by committed T0. 2. T1 begins. Under Repeatable Read, the snapshot is not taken by the `BEGIN` token; the first statement freezes S1=`{T0}`. 3. T1 probes V0. The creator gate passes, the superseder gate passes, and T1 receives `$100`. 4. T2 begins `UPDATE balance = 120`. V0 remains stored while its metadata records an in-progress superseder. 5. T2 appends V1=`$120`. T1 scans V1 first, rejects it because T2 is not committed in S1, then selects V0 and returns `$100` again. 6. T2 commits. Future snapshots may include T2, but S1 remains `{T0}`. 7. T1 scans again. V1 is committed globally but still absent from S1, so T1 selects V0 and returns its third `$100`. 8. Later reader T3 takes S2=`{T0,T2}`. V1 passes both gates and T3 returns `$120`. 9. Cleanup tests V0 but cannot reclaim it because S1 predates T2's superseding commit; V0 is still visible to S1. 10. T1 ends and releases S1. Cleanup can now remove V0, leaving V1 as the only stored version. The terminal ledger is the proof: T1 reads `$100 → $100 → $100`, while T3 reads `$120`. Switch the isolation selector to Read Committed and keep the row and update order unchanged. T1's first statement uses S1 and returns `$100`. Its statement while T2 is in progress takes S2, still excludes T2, and also returns `$100`. After T2 commits, the next statement takes S3=`{T0,T2}` and returns `$120`. The contrast changes snapshot lifetime, not the update. That is the practical tradeoff in this example: Repeatable Read favors one stable transaction-wide view, while Read Committed favors fresher committed data for each statement. ## Why Old Versions Accumulate Committing a new value does not by itself make every older version disposable. Visibility asks which version one snapshot selects. Cleanup asks a different question: has the global horizon advanced far enough to prove that an obsolete version is safe to reclaim? The Long snapshot preset holds T1's S1 while T2, T3, and T4 commit `$120`, `$140`, and `$160`. Fresh snapshots would advance to each new value, but S1 still excludes all three writers. T1 therefore rejects V3, V2, and V1 before selecting V0=`$100`. The shelf reports both counts as the workload runs: `stored 1 / obsolete 0` becomes `stored 4 / obsolete 3`. After T2, the cleanup horizon blocks `{V0}`. After T3 it blocks `{V0,V1}`. After T4 it blocks `{V0,V1,V2}`. S1 directly selects V0; it does not select V1 or V2. The PostgreSQL-flavored cleanup model retains all three because S1 pins the horizon before the transactions that superseded them. Once T1 ends, the horizon advances and V0, V1, and V2 become reclaimable. The animation removes them in separate beats, leaving V3=`$160`. Retained history gives readers a stable view without forcing them to observe an in-place overwrite. Continued updates can add another obsolete version while the horizon remains pinned, increasing storage and cleanup work. “Committed,” “visible to S1,” and “safe to reclaim” are three different judgments. For a row with `V` stored versions, this teaching model may inspect up to `V` candidates newest to oldest. Its fixed presets cap `V` at four. Real database engines use engine-specific metadata, indexes, page layouts, and cleanup strategies, so this scan is a visibility model rather than a universal runtime formula. ## Edge Cases - **Writer still in progress:** V1 fails the creator gate. The reader falls back to an older version that passes its snapshot. - **Writer committed after the snapshot:** the creator may now be committed globally but still be absent from the older snapshot. Repeatable Read preserves that distinction. - **Fresh statement snapshot:** Read Committed can include a commit that an earlier statement snapshot excluded, so two statements in one transaction may return different committed values. - **Aborted update:** the Aborted update preset marks V1's creator as aborted. Both an old snapshot and a later fresh snapshot reject V1 and select V0. Cleanup can remove the aborted version without removing the committed value it failed to replace. - **Several obsolete versions:** a single old transaction snapshot can pin the cleanup horizon while updates add more obsolete versions. The snapshot may select only the oldest value even though the horizon retains the intermediate history too. - **No active snapshot pins the cleanup horizon:** once the protecting snapshot ends—or a statement-scoped snapshot is released—the cleanup target can become reclaimable. ## Common Mistakes - Treating the newest stored version as visible to every reader. Visibility is relative to one snapshot. - Treating `BEGIN` as the snapshot boundary in every isolation level. In the modeled PostgreSQL Repeatable Read flow, the first statement establishes the transaction snapshot. - Assuming a commit mutates snapshots that already exist. It changes what future snapshots can include. - Assuming MVCC means no waiting anywhere. This lesson isolates non-blocking reads across one update; it omits write/write conflicts and other waits. - Deleting V0 immediately after V1 commits. S1 may still require V0 to reproduce its earlier result. - Treating visibility and cleanup as the same test. S1 selects V0 directly, while the cleanup horizon can also retain intermediate obsolete versions that S1 never selects. - Calling a row version a transaction or a snapshot. T2 creates a version; S1 describes a committed view; neither term is interchangeable. - Reading `created by` and `superseded by` as literal PostgreSQL tuple columns. They are logical labels used by this teaching model. ## A Note on Simplification The animation uses logical version cards and explicit committed transaction sets. It does not reproduce a physical PostgreSQL tuple chain, an undo-log engine, or every MVCC rule. It omits WAL, the buffer pool, indexes, write/write conflict locks, a transaction's own writes, subtransactions, Serializable Snapshot Isolation, HOT chains, transaction-id wraparound, and real vacuum scheduling. Storage layout, visibility metadata, isolation guarantees, and reclamation machinery differ across engines. This model keeps visibility and reclamation separate: snapshots select versions with the two gates, while a conservative PostgreSQL-flavored horizon decides when obsolete versions become reclaimable. It does not reproduce PostgreSQL's physical tuples or exact vacuum implementation.