# MVCC: How Databases Let Readers and Writers Ignore Each Other

> Postgres never updates a row — it writes a new version and lets old transactions keep seeing the old one. Multiversion concurrency is why reads don't block writes, why VACUUM exists, and why 'the database is bloated' is a real sentence.

URL: https://harshith.in/blog/mvcc-how-databases-time-travel/
Author: Harshith Sunku (Staff Engineer) — https://harshith.in/about/
Tags: databases, concurrency, systems

The naive way to make transactions safe is locks: readers lock out
writers, writers lock out everyone. Run analytics on a busy OLTP
database under that regime and everything stops. Every serious database
abandoned that world for a stranger model: **keep multiple versions of
every row**, give each transaction a consistent snapshot of the past,
and let readers and writers proceed as if the other didn't exist. The
mechanics — visible right down to hidden columns you can SELECT — are
where isolation levels, VACUUM, and several famous outages live.

## Versions and Snapshots, Concretely

Postgres wears the machinery openly. Every row carries system columns:
`xmin` (the transaction ID that created this version) and `xmax` (the
one that deleted/superseded it, or 0):

```sql
SELECT xmin, xmax, * FROM accounts WHERE id = 7;
```

An UPDATE never modifies in place — it writes a *new* row version
(new xmin), stamps the old version's xmax, and both coexist in the
table. Each transaction starts with a **snapshot**: the set of
transaction IDs committed as of its start. Visibility is then a pure
function — a version exists for you if its creator committed before
your snapshot and its deleter didn't:

```text
  txn 100 UPDATE:   [v1 xmin=90 xmax=100] [v2 xmin=100 xmax=0]
  txn 95 (older snapshot)  → still sees v1, no waiting
  txn 105 (newer snapshot) → sees v2
```

Nobody blocked anybody. Readers *never* block writers or vice versa —
the property that makes long reports on live systems possible. Writers
still block writers on the *same row* (someone must win), and that's
where isolation levels diverge: READ COMMITTED re-snapshots per
statement and lets the loser proceed against the new version — with
subtle mid-statement anomalies; REPEATABLE READ keeps one snapshot for
the whole transaction and instead *aborts* the loser
("could not serialize access"), turning retry loops into a mandatory
application pattern; Postgres's SERIALIZABLE (SSI) goes further,
detecting dangerous read-write dependency patterns and aborting those
too. Same version store, increasingly strict referees.

## The Bill: Dead Versions

Old versions become garbage the moment no live snapshot can see them —
but something must notice and reclaim. Postgres's answer is **VACUUM**
(and autovacuum), and this is where MVCC stops being elegant theory
and becomes operations:

- **Bloat**: update-heavy tables accumulate dead versions faster than
  autovacuum's defaults reclaim them; the table grows, cache hit rates
  fall, seq scans lengthen. `pg_stat_user_tables.n_dead_tup` is the
  gauge; tuning autovacuum *per hot table* is normal, not exotic.
- **Long transactions are poison**: a snapshot held open for hours
  pins every version created since — VACUUM can reclaim nothing newer.
  One forgotten `idle in transaction` connection (or an abandoned
  replication slot) can bloat an entire database. Monitoring for
  old snapshots is table stakes.
- **Wraparound**: transaction IDs are 32-bit; without periodic freezing
  of old tuples, the counter wraps and visibility math inverts.
  Autovacuum escalates to aggressive anti-wraparound mode on its own —
  the incidents you've read about (databases refusing writes to
  protect themselves) come from workloads that outran or disabled it.

MySQL's InnoDB makes the opposite layout bet: rows are updated
**in place**, old versions reconstructed from **undo logs** when an old
snapshot needs them. Cleaner tables, no VACUUM — but long-running
transactions bloat the undo history instead (history list length is
InnoDB's version of the same pathology), and reconstructing old
versions costs CPU on read. Same MVCC contract, different place to
keep the past; both charge rent for long snapshots.

Two design consequences worth carrying into schema work: MVCC makes
*hot single-row counters* an anti-pattern (every increment is a new
version + dead tuple — hence sharded counters or Redis in front), and
it makes `SELECT ... FOR UPDATE` / advisory locks the honest tool when
you genuinely need "read then write, atomically" — snapshots protect
you from seeing chaos, not from write-write races on what you read.

## Takeaways

- MVCC = versions + snapshots: readers see a consistent past, writers
  append the future, neither waits for the other.
- Postgres exposes it: xmin/xmax on every row; isolation levels are
  snapshot-refresh policies plus abort rules — retry loops are part of
  the contract above READ COMMITTED.
- Garbage is the price: VACUUM/bloat (Postgres) or undo history
  (InnoDB), and long-lived snapshots pin garbage in both.
- Wraparound freezing is why you never disable autovacuum, only tune it.
- Snapshots don't serialize read-modify-write: FOR UPDATE or explicit
  locks where the logic needs it.