Multi-Version Concurrency Control (MVCC)
TechniqueDatabase InternalsMidSeniorUpdated
Each write produces a new row version tagged with the transaction id; readers see the version visible to their snapshot. Readers never block writers, writers never block readers.
When to Use Multi-Version Concurrency Control (MVCC)
Any mixed read/write OLTP workload where long analytic reads should not block point updates. Postgres, Oracle, MySQL/InnoDB, and most modern relational engines use some flavor of MVCC.
In Practice
Postgres stores
xmin / xmax per row tuple. A SELECT under REPEATABLE READ takes a snapshot at statement (or transaction) start; concurrent UPDATEs create new tuples that the snapshot ignores. VACUUM later reclaims dead tuples.How Multi-Version Concurrency Control (MVCC) Comes Up in Interviews
Scenario
A long-running analytics query in Postgres must not block concurrent writes, and must see a consistent snapshot of the data. Which mechanism is doing the heavy lifting?
MVCC lets the reader use a snapshot built from xmin/xmax and the active transaction list. Writers create new row versions; the long read keeps seeing the old ones. No locks are held against writers.
The price is bloat: dead tuples accumulate until VACUUM reclaims them. A very long-running transaction holds back the xmin horizon and can prevent VACUUM from cleaning up, leading to table bloat and index bloat.