Multi-Version Concurrency Control
Also known as:
MVCC
Snapshot Isolation
Definition
Multi-Version Concurrency Control, or MVCC, lets many transactions read and write the same data at once without blocking each other. Instead of overwriting a row in place, the database writes a new version of the row and keeps the old one around until no running transaction can still see it. Readers get a consistent snapshot from a point in time, writers add new versions, so readers never block writers and writers never block readers.
Key Takeaways
- MVCC trades storage for concurrency. Every UPDATE or DELETE leaves an old row version behind instead of overwriting it.
- Those old versions are dead tuples. They pile up as table bloat and must be reclaimed by autovacuum.
- Readers see a snapshot, so they never block writers. This is how MVCC gives you isolation without heavy locking.
- MVCC is the engine behind snapshot isolation, one of the most common ACID isolation levels.
How It Works
- Each row version is stamped with the transaction ids that created and deleted it.
- A transaction reads only the versions visible to its snapshot, ignoring newer or already-dead ones.
- An UPDATE writes a brand new version and marks the previous one as dead rather than editing in place.
- Once no active transaction can see a dead version, vacuum reclaims its space for reuse.
Where It Is Used
- PostgreSQL keeps every row version inline and relies on autovacuum to clean dead tuples.
- MySQL InnoDB stores old versions in the undo log and purges them in the background.
- Oracle uses undo segments for the same read-consistent snapshots.