MVCC: readers never wait for writers

What it is for. A row is being updated while others read it. Readers must not queue behind the writer's lock, and must never see a half-changed row. Mixed read/write workloads (report queries running next to live traffic) are where this hurts.

The mechanism in one line. Never overwrite in place. An update creates a new version of the row and stamps the old one as invalidated; readers pick the version their snapshot entitles them to see.

xmin and xmax — the two stamps. Each version's header carries two transaction ids (these are PostgreSQL's real field names). xmin: the transaction that created this version. xmax: the transaction that invalidated it — by DELETE, or by an UPDATE that superseded it; 0 while the version is still current. Three things must not be conflated: invalidation is a logical state (retired, not gone — vacuum reclaims the space later); xmax is a field that holds exactly one number; that number is a transaction's id — not "a person", and not the act itself. A side note: PostgreSQL also borrows xmax to record row locks, told apart from a real invalidation by flag bits in the header (infomask); its primary job is the invalidation stamp.

The version chain. When transaction 742 updates a row created by 731, the old version becomes xmin=731, xmax=742, and the new version is xmin=742, xmax=0. The old version's xmax equals the new version's xmin — that equality is the link of the chain.

A real record. Live run, PostgreSQL 16.2, 2026-10-22, table accounts, id=7. INSERT ran as transaction 731; a plain SELECT exposing the system columns (SELECT ctid, xmin, xmax, * FROM accounts) showed: ctid (0,1), xmin 731, xmax 0, balance 100. A Repeatable Read session then opened its snapshot. UPDATE ran as transaction 742 and committed. A fresh reader saw the new version: (0,2), xmin 742, xmax 0, balance 80. The snapshot session re-read and still saw (0,1), xmin 731, xmax 742, balance 100 — the old version physically in place, stamped by its superseder, its invalidation simply not in effect for that snapshot. DELETE ran as transaction 743: fresh readers counted 0 rows, while the snapshot session kept seeing balance 100 until it committed, after which it too saw 0 rows. The frozen world lasted exactly one transaction.

How it solves the problem. Writers only stamp and append new versions; they never consult readers. Readers walk the chain applying the visibility rule; they take no read locks. Read-write blocking disappears on both sides. The bill is storage (several versions of one row coexisting) plus cleanup: PostgreSQL's vacuum, InnoDB's purge. A long transaction's snapshot acts as a GC root — it pins every version it can still see, cleanup cannot reclaim them, and the table bloats. That is the concrete cost of holding a snapshot.

InnoDB's layout differs, the idea is the same: the row keeps the id of the last transaction that modified it plus a roll pointer; older versions are not kept in place but reconstructed from the undo log. (That undo log doubles as the old-version store — one structure serving both rollback and MVCC reads.)

What MVCC is not. It separates reads from writes; it does not arbitrate write-write conflicts — concurrent writers of the same row still serialize on locks. And it says nothing about durability: versions may live only in memory, which is wal's jurisdiction, and nothing about which consistency contract is sold to transactions, which is isolation-levels' jurisdiction.