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
xmaxand inserts a new tuple withxmin= 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.
Connections
- Part of
- Snapshot Isolation
- Learn first
- Isolation Level
- Contrasts with
- Two-Phase Locking
- Related
- Hybrid Logical ClockRead CommittedWrite-Ahead LogSerializable Snapshot IsolationOptimistic Concurrency Control
- In real systems
- PostgreSQLMySQLCockroachDBSpanneretcd
Recall
These cards are also in your review deck, scheduled with spaced repetition.