MVCC
Multi-Version Concurrency Control: keeping multiple versions of a row so readers see a consistent snapshot without blocking writers. Used by Postgres, MySQL InnoDB, Oracle. Enables snapshot isolation with high concurrency.
- Default in modern relational DBs — you get it automatically.
- Mixed read/write workloads.
- Workloads where bloat/vacuum is a problem and SI is overkill (rare to opt out).
Definition
MVCC stores multiple row versions; each transaction sees a snapshot as of its start time. Readers never block writers (and vice versa). Old versions are garbage-collected (vacuum). Provides snapshot isolation; serializable needs extra checks.
Why it exists
Lock-based concurrency (readers block writers) kills throughput under mixed workloads. MVCC lets reads proceed against a snapshot while writes create new versions, maximizing concurrency.
How it works
Each row version has xmin/xmax (transaction IDs that created/ deleted it). A transaction sees versions visible to its snapshot. Writes create a new version and mark the old deleted. Vacuum reclaims versions invisible to all snapshots.
Scaling limits
Long-running transactions prevent vacuuming dead versions, causing bloat. Snapshot isolation has write skew (serializable fixes it at a cost). Version storage grows with churn.
Cost shape
Storage for multiple versions (bloat) + vacuum overhead. Blow-ups: long transactions causing bloat and vacuum falling behind.
Alternatives
- Lock-based concurrency — Simpler, but readers block writers.
- OCC (optimistic) — Validate at commit; good for low contention.
Failure modes
- Bloat from long transactions.
- Write skew under snapshot isolation.
- Vacuum storms.