MVCC and snapshots

Locks alone could give you isolation. Readers would take shared locks and writers would take exclusive ones. The cost is that a long report would block every writer it touched. Multi-version concurrency control (MVCC) avoids that. It keeps several versions of each row, so a reader sees a consistent snapshot of the data and a writer never has to wait for a reader.

Row versions in Postgres

Every transaction gets a 32-bit transaction ID (XID). Every row version, called a tuple, carries two hidden columns:

• xmin: the XID that created this version.
• xmax: the XID that deleted or replaced it. It is 0 while the version is current.

An UPDATE does not overwrite the old version. It sets xmax on the old version and inserts a new version with xmin set to the updating transaction. You can see this in the hidden columns. The output below is illustrative.

SELECT xmin, xmax, id, balance FROM accounts WHERE id = 1;
--  xmin | xmax | id | balance
--   741 |    0 |  1 |     100

UPDATE accounts SET balance = 90 WHERE id = 1;   -- run as XID 742

SELECT xmin, xmax, id, balance FROM accounts WHERE id = 1;
--  xmin | xmax | id | balance
--   742 |    0 |  1 |      90
-- the old version (xmin 741, xmax 742) is still on disk, but new snapshots skip it

How visibility is decided

A snapshot records which transactions had committed when it was taken. A row version is visible to your snapshot when two things hold:

1. Its xmin transaction committed before your snapshot.
2. Its xmax is empty, belongs to an aborted transaction, or belongs to a transaction that committed after your snapshot.

Both checks are integer comparisons. That is how each transaction sees a different, but internally consistent, version of the database without taking a lock.

Read committed takes a new snapshot for each statement. Repeatable read and serializable keep one snapshot for the whole transaction.

Vacuum and long-running transactions

Old versions must eventually be removed. VACUUM, usually run by autovacuum, deletes versions that no running transaction can still see, and it marks their space reusable.

The word that matters is running. The oldest open snapshot sets a horizon, and vacuum cannot remove any version newer than it. Suppose one transaction stays open for six hours on a busy site. Vacuum cannot clean up anything written in those six hours, across every table. Dead rows pile up, indexes grow and queries slow down. A forgotten BEGIN in a psql session has the same effect.

Transaction ID wraparound

XIDs are 32 bits, so they wrap around after about 4 billion transactions. Postgres can only compare XIDs within a window of about 2 billion. To keep old rows visible after wraparound, vacuum freezes them, which marks them as visible to every transaction.

If freezing falls far enough behind, Postgres stops accepting writes to protect the data. Sentry had a multi-hour outage from this in 2015. Watch the age with:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Alert long before the age approaches 2 billion.

How MySQL differs

InnoDB updates rows in place and keeps the old values in undo logs. A purge thread removes them when no one needs them. Long transactions cause the same kind of problem here: the undo history grows, and you can watch it as the "history list length".

Common mistakes

• Leaving a transaction open. An idle-in-transaction session holds back vacuum for every table. Set idle_in_transaction_session_timeout.
• Treating MVCC as "no locks". Readers do not block writers, but two writers still lock the same row.
• Relying on xmin and xmax. They are internal bookkeeping. Do not build application logic on them.
• Ignoring XID age until writes stop. By then the database is already protecting itself.

Check yourself

1. A transaction stays open for six hours on a busy database. What happens to old row versions? Vacuum cannot remove any version newer than the oldest open snapshot. Dead rows accumulate across all tables, causing bloat.
2. Why do readers and writers not block each other in MVCC? A reader sees the older committed version that matches its snapshot. A writer creates a new version rather than overwriting the old one, so they touch different versions.

Key takeaways

• MVCC keeps several row versions, so readers do not wait for writers.
• In Postgres, xmin and xmax decide visibility, and a snapshot is a set of committed transactions.
• The oldest open transaction limits what vacuum can clean. Keep transactions short.
• XID wraparound is a real outage risk. Monitor age(datfrozenxid).

Further learning

Andy Pavlo's CMU lecture on MVCC covers the design space behind Postgres, MySQL and others. It looks at version storage, garbage collection and index management.

Next, read locking and concurrency control to see how writers still wait for each other.