Multiversion concurrency control (MVCC)
Why does one long read pin every old version?
The idea
Every scheme so far pays because there is one copy of each item, so a reader and a writer must contend for its state. MVCC removes the premise. Keep old versions. A reader reads the version that was current as of its start timestamp. A writer adds a new version. The whole read-write conflict class disappears: readers never block writers, writers never block readers.
What survives untouched is write-write conflict. So the common claim "MVCC means writers don't block" is half-true, and the half that is false is the half that hurts.
The costs are four, and they all follow from the same move.
Cost 1: space, and the oldest-reader problem
The length of a version chain equals the update rate times the retention window, and the retention window is set by the oldest live snapshot. Therefore:
A local action (one analyst's query) creates a global cost on the write path. This is the single most common MVCC production failure. And the two families of systems make opposite choices about who pays:
| Family | Where old versions live | What happens under a long read | Who pays |
|---|---|---|---|
| Postgres | In the table (the "heap") until vacuum removes them | Versions pile up: table and index bloat, which cannot be recovered without a rewrite | The writers |
| InnoDB | In undo logs that purge cannot trim past the oldest open snapshot | The undo history (the "history list length") keeps growing; purge falls behind and reads of old versions walk longer chains | The writers, and every reader that needs an old version |
| Oracle | In a bounded undo segment | The reader's version is eventually overwritten and the reader fails: ORA-01555 snapshot too old | The reader |
Same mechanism, different parties billed. This is costs are conserved with the invoice visible. It is also exactly the two halves of Tomas's first symptom.
Cost 2: version storage layout
There are two designs.
- Append the new version in place (Postgres). Reading the current version is O(1), but every version of every row needs an index entry, so an update to a column that has no index still writes to every index. That is exactly why Postgres has the HOT optimization (heap-only tuple updates, which skip index writes when no indexed column changed). It is granularity mismatch again.
- Delta (undo) chains (InnoDB, Oracle). Store the current row in place, and rebuild old versions by applying undo records backwards. Reading the current version is cheap; reading an old version costs O(length of the chain).
Both designs make the newest version the fast one, because that is the common case.
Cost 3: the collector
Vacuum, purge, undo truncation. Immutable storage grows an indirection layer plus a collector, and the collector falls behind in exactly the conditions that produce the most garbage (heavy updates plus long readers). This is the same pattern as flash translation layers, LSM compaction and table-format snapshot expiry.
Cost 4: visibility checks on the read path
Every tuple that is read must be tested for visibility to your snapshot: compare the creation and deletion timestamps and, if the writer's status is unknown, look in the commit log. That is a branch and a possible cache miss for every tuple in a scan, and in scan-heavy work (where data is read in bulk) the CPU memory system is already the main cost. Hint bits (a flag stored on the row after the first lookup) and commit-status caches exist to amortize this.
What MVCC alone does not give you
rw antidependencies are invisible to MVCC. Nothing in the mechanism notices that a reader's decision was invalidated by a later writer. So MVCC gives Snapshot Isolation (SI), and SI permits write skew. That gap is the entire reason SSI exists (see below).
Multiversioning converts a mutual-exclusion problem into a space-plus-collector problem, and the bill arrives from the oldest live reader.