# MVCC

> Keep several versions of each row so readers never wait for writers, and clean up the old ones later.

Concept · Transactions & indexes · https://levelcode.org/concepts/mvcc

**Multi-version concurrency control** lets a database keep several versions of the same row at once. A writer creates a new version instead of overwriting the old one. Each transaction reads from a **snapshot**: the versions that were committed when its snapshot was taken. Readers don't block writers, and writers don't block readers.

## How it works in PostgreSQL

Every row version, called a *tuple*, records two transaction IDs:

- `xmin`: the transaction that created it.
- `xmax`: the transaction that deleted or replaced it, if any.

An `UPDATE` marks the old tuple's `xmax` and inserts a new tuple. A `DELETE` only sets `xmax`. When a transaction reads, it checks each tuple against its snapshot. It skips tuples created by transactions it can't see, and keeps tuples deleted by transactions it can't see.

```text
id=7  balance=100  xmin=500  xmax=812    ← visible to snapshots taken before 812 committed
id=7  balance=60   xmin=812  xmax=∅      ← visible to snapshots after
```

Under `READ COMMITTED` each statement takes a fresh snapshot. Under `REPEATABLE READ` and `SERIALIZABLE` the whole transaction uses one. That's the difference between the [isolation levels](https://levelcode.org/concepts/isolation-levels).

## Two writers still conflict

MVCC removes read–write blocking, not write–write. When two transactions update the same row, the second waits on a row lock. If the first commits, the second either re-reads the new version (read committed) or aborts with a serialization error (repeatable read and above).

## The costs

- **Dead tuples.** Old versions stay on disk until no snapshot can still need them. `VACUUM`, usually autovacuum, removes them. Tables that get many updates bloat when vacuum falls behind.
- **Long transactions hold everything back.** One transaction that's been open for an hour, even an idle one, keeps an hour of dead tuples alive across the whole database. Watch `pg_stat_activity` for `idle in transaction`.
- **Updates write a whole new row**, and often new index entries too. Updating a frequently changed counter column in a wide row is more expensive than it looks.

Other engines implement MVCC with undo logs instead, as in MySQL InnoDB and Oracle: they keep the latest version in place and rebuild older ones on demand. The guarantees are similar; the cleanup work moves elsewhere.