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.

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.

#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.