MVCC
MVCC keeps multiple versions of rows so readers see a consistent snapshot without blocking writers and writers do not block readers, trading storage and cleanup for concurrency.
Definition
Multiversion Concurrency Control assigns each row version visibility bounds in terms of transaction identifiers. Readers reconstruct the snapshot that their transaction started with, ignoring newer versions; writers create new versions and mark old ones dead. Locking between pure readers disappears, and isolation levels differ in how strictly they hide concurrent writes.
Context and scope
The concurrency backbone of PostgreSQL, InnoDB, and many distributed databases. Explains long-running-report interference with vacuum, snapshot age limits, and why read-committed and repeatable-read behave differently.
Common misconceptions
- MVCC removes write conflicts — it removes reader-writer blocking; concurrent writers to the same row still conflict
- Old row versions are free — dead versions accumulate until vacuum, and long transactions pin them, bloating tables
- Readers always see the newest data — each statement or transaction sees its snapshot; freshness depends on isolation level
Anti-patterns
- Running multi-hour analytics transactions and pinning dead tuples against vacuum, bloating every hot table
- Assuming repeatable-read reads still see fresh committed data mid-transaction and building logic on the wrong premise
- Treating vacuum failures as background noise instead of a foreground latency and disk-growth incident
Related concepts
Related examples
Authoritative source
What it is
Lock-based concurrency forces readers and writers to queue. MVCC instead keeps versions: each transaction decides which versions it can see based on who created them and which transactions were in flight. Readers never wait for writers; writers never wait for readers.
How it works
PostgreSQL tuples carry xmin and xmax transaction identifiers; InnoDB undoes changes into rollback segments and uses read views. A snapshot records the set of in-flight transactions, and version visibility is computed per row. Isolation levels change the snapshot policy: one snapshot per statement, or one per transaction.
When it matters
Mixed OLTP and reporting on one server, high read concurrency, and any workload where read locks would serialize throughput. Plan vacuum and undo retention as first-class operations, not defaults.
Pitfalls
Long transactions hold snapshots that pin garbage collection, causing bloat and transaction-ID wraparound risk in PostgreSQL or huge undo logs in InnoDB. Serialization anomalies still exist at lower isolation levels — MVCC is not serializability by default. Monitor snapshot age and dead tuple counts as capacity signals.