MVCC
Readers see a snapshot; writers create versions. Postgres and InnoDB's answer to blocking reads.
Level: advanced
What is it?
Multi-Version Concurrency Control (MVCC) maintains multiple logical versions of rows so readers can observe a consistent snapshot while writers create newer versions. The database determines which version is visible using transaction metadata, timestamps, transaction IDs, or related visibility rules.
Why does it matter?
MVCC can reduce reader-writer blocking and improve concurrent workloads, but it adds version cleanup, vacuum/purge work, and subtleties around isolation and write conflicts.
Mental model
Instead of overwriting the only copy immediately, a writer creates a new version; each reader sees the version allowed by its snapshot. Old versions remain until no active transaction requires them.
How it works
- MVCC does not mean locking disappears; writers and schema operations may still take locks.
- Snapshot rules differ by database and isolation level; statement snapshots and transaction snapshots are not identical.
- Old row versions consume storage until vacuum/purge or garbage collection can reclaim them.
- Long-running transactions can retain old versions and delay cleanup.
- Write-write conflicts, uniqueness constraints, and serialization failures still need explicit handling.
Steps
- Transaction A obtains a snapshot and reads row version V1.
- Transaction B updates the row and commits version V2.
- Depending on A's snapshot and isolation, A may continue to see V1 even after B commits.
- When no active snapshot needs V1, the engine can eventually reclaim it.
Real-world usage
- Explaining readers and writers under load.
- Diagnosing table bloat, vacuum pressure, and old snapshots.
- Understanding snapshot isolation and serialization anomalies.
Common mistakes
- Assuming MVCC prevents all anomalies or all locks.
- Leaving long-running transactions open and retaining old versions.
- Assuming an old snapshot automatically sees every newly committed row.
Interview questions
How can MVCC reduce read-write blocking?
Readers can use visible older versions while writers create new versions, subject to the engine's snapshot and locking rules.
Why can a long-running transaction increase storage pressure?
It can keep an old snapshot active, preventing cleanup of versions that might still be visible to it.