Notestransactions🌱 seedling

MVCC

Also: Multi-Version Concurrency Control · Multiversion Concurrency Control · Row Versioning · VACUUM · Undo Log

The database keeps several versions of each row, tagged with the transactions that created and replaced them, and each transaction reads the version its snapshot should see. Readers never wait for writers; the price is storing and garbage-collecting old versions.

Why it exists

Snapshot Isolation and non-blocking Read Committed need old values to stay readable while new ones are being written, without copying the database.

How it works

  • Postgres (versions in the heap): an UPDATE marks the old tuple's xmax and inserts a new tuple with xmin = the writer. A tuple is visible if its creator committed before the snapshot and its deleter didn't. VACUUM removes tuples no snapshot can see.
  • InnoDB and Oracle (undo): update in place and write the previous version to an undo log; readers with older snapshots follow the chain back. A purge thread drops undo records no read view needs.
  • Snapshot timing: per statement for Read Committed, per transaction for snapshot isolation.
  • Writers still lock rows against other writers.

Trade-offs

Postgres heap versions Undo-log versions
cheap rollback: aborted tuples are just invisible rollback must apply undo
table and index bloat until VACUUM long snapshots grow undo; old reads slow down
updates create new index entries (unless HOT) updates in place keep rows compact

In practice

  • Transaction ID wraparound: Postgres XIDs are 32-bit, so VACUUM must freeze old tuples. In February 2019 Mailchimp's Mandrill service had a Postgres shard stop accepting writes at the wraparound limit, a roughly 40-hour outage.
  • Oracle raises ORA-01555 "snapshot too old" when undo needed by a long query has been overwritten.

Pitfalls

  • An idle-in-transaction session pins every newer version: Postgres can't vacuum them, InnoDB's history list grows.
  • Bloat from churn-heavy tables (queues, counters) needs aggressive autovacuum settings.

Recall

These cards are also in your review deck, scheduled with spaced repetition.