Multi-Version Concurrency Control

Picture a busy library: dozens of readers want to browse the shelves at the same time, and a librarian is quietly reorganizing books behind the scenes. A naive approach would lock the whole room every time the librarian moves a book. Real libraries do not work that way — and neither do modern databases.

Multi-Version Concurrency Control (MVCC) is the trick that makes it possible. Instead of overwriting a row when it is updated, the database keeps the old version alive. Every running transaction is given a snapshot — a consistent view of all committed data at the moment it started. Readers see the old version; writers create a new one. Nobody waits for anyone else.

The result is the "readers never block writers, writers never block readers" guarantee that is the backbone of SQL optimization and transaction scheduling. It is not a magic trick but a precise algorithm, and understanding it reveals why isolation levels, phantom reads, and deadlocks behave the way they do.

The demo below simulates two transactions running at the same time while a third transaction commits a write between them. Each transaction gets a snapshot locked to its start time.

<p class="hint">{{hint}}</p>
<div id="timeline"></div>
<div class="controls">
  <button id="btnA" type="button">{{btn_start_a}}</button>
  <button id="btnW" type="button" disabled>{{btn_commit_write}}</button>
  <button id="btnB" type="button" disabled>{{btn_start_b}}</button>
  <button id="readA" type="button" disabled>{{btn_read_a}}</button>
  <button id="readB" type="button" disabled>{{btn_read_b}}</button>
  <button id="reset" type="button" class="ghost">{{btn_reset}}</button>
</div>
<div id="log" class="log"></div>

* { box-sizing: border-box; }
body { font-family: system-ui, sans-serif; color: #222; margin: 0; font-size: 14px; }
.hint { font-size: .88rem; color: #444; margin: 0 0 .8rem; line-height: 1.5; }
#timeline {
  display: grid;
  grid-template-columns: 80px 1fr 1fr 1fr;
  border: 1px solid #cdd9e3;
  border-radius: 8px;
  overflow: hidden;
  margin-bottom: .8rem;
}
.th { background: #1d3557; color: #fff; font-weight: 700;
      padding: .4rem .6rem; font-size: .8rem; text-align: center; }
.cell {
  padding: .4rem .6rem; border-top: 1px solid #e0e6eb;
  font-size: .82rem; text-align: center; background: #f7f9fb;
  min-height: 2.2rem; display: flex; align-items: center; justify-content: center;
}
.cell.event { background: #e8eef3; font-weight: 600; }
.cell.write { background: #fff3cd; }
.cell.read-old { background: #d4edda; color: #0a7d33; font-weight: 700; }
.cell.read-new { background: #cce5ff; color: #004085; font-weight: 700; }
.label { font-weight: 700; color: #1d3557; }
.controls { display: flex; gap: .5rem; flex-wrap: wrap; margin-bottom: .7rem; }
button {
  font: 600 13px system-ui, sans-serif;
  padding: .4rem .85rem; border: 1px solid #1d3557;
  background: #1d3557; color: #fff; border-radius: 8px; cursor: pointer;
}
button:disabled { opacity: .4; cursor: not-allowed; }
button.ghost { background: #fff; color: #1d3557; }
.log { font-size: .82rem; color: #333; line-height: 1.7; }
.log .ok { color: #0a7d33; font-weight: 700; }
.log .info { color: #004085; font-weight: 700; }
.log .write-msg { color: #856404; font-weight: 700; }

// Code not found

Click Start Txn A and Start Txn B to open two readers, then Commit Write to update the balance. Finally, hit Read on each transaction. Notice that Txn A — which started before the write — still sees the old value, while Txn B (started after) sees the new one. Both reads are correct: each transaction lives inside its own consistent snapshot.

MVCC looks elegant, but the engineering beneath it is surprisingly subtle.

• Version chains grow without bound. Every update appends a new row version. Without cleanup, a heavily-updated table becomes a linked list of stale versions that readers must scan. PostgreSQL calls its cleanup daemon VACUUM; without it, tables bloat and read performance degrades.
• Snapshot timestamps must be globally ordered. The database maintains a transaction ID counter (or a hybrid logical clock in distributed systems). Every row version carries the transaction ID that created it and, optionally, the one that deleted it. Deciding which version a snapshot should see requires comparing these IDs correctly — a subtle ordering problem.
• Snapshot isolation is not serializability. Under snapshot isolation a phenomenon called write skew can occur: two transactions each read a value, each decides it is safe to write, and the combined effect violates a constraint no single write would have broken. Postgres added Serializable Snapshot Isolation (SSI) in 2012 to detect and abort such cycles — a result proved correct by Cahill, Röhm, and Fekete.
• Anomaly prevention has a cost. SSI tracks read/write conflicts between active transactions. The more concurrent transactions, the more bookkeeping. Choosing the right isolation level is always a trade-off between correctness and throughput.

MVCC shifts the cost of concurrency from holding locks (which blocks others) to storing and cleaning up old versions (which costs memory and I/O). Understanding this trade-off is at the heart of database scheduling and performance tuning.

MVCC is not an academic curiosity — it is the concurrency engine used in production by billions of transactions every day:

• PostgreSQL uses heap-based MVCC: every updated row gets a new physical tuple with xmin/xmax transaction stamps; VACUUM reclaims dead tuples.
• MySQL InnoDB stores old versions in an undo log separate from the main table, keeping the primary data compact while the undo log serves historical reads.
• CockroachDB and FoundationDB extend MVCC to distributed clusters, using hybrid logical clocks to order versions across nodes without a single global lock.
• Git is conceptually MVCC for file trees: every commit is an immutable snapshot, and branches diverge without blocking each other.
• Event-sourced systems store every state change as an immutable event (a new version), replaying history to reconstruct any past snapshot on demand.

Whenever a system must let multiple agents read and write the same data concurrently without corrupting each other's view, MVCC — or a close relative — is the answer. Learn it once and you will recognize it everywhere, from databases and filesystems to distributed systems and beyond.

Multi-Version Concurrency Control resolves the oldest tension in database design — readers want stability, writers want progress — by making them work on different versions of the same truth. A reader always sees a clean snapshot from its start time; a writer always appends a new version without touching what anyone else is reading.

The cost is real: old versions accumulate, vacuum must run, and choosing the right isolation level requires understanding which anomalies you can tolerate. But the payoff — high-concurrency databases that feel like single-user systems — is why MVCC has become the default concurrency model for every serious relational engine built in the last three decades.

Next time a query returns instantly on a live, write-heavy database, remember: somewhere beneath it, a version chain is quietly doing the work that locks once paid for — without making anyone wait.