MVCC
How Postgres-style engines let reads and writes happen at the same time without ever blocking each other.
Overview
MVCC (Multi-Version Concurrency Control) is the mechanism most modern relational engines — Postgres, MySQL/InnoDB, Oracle — use to let reads and writes happen at the same time without blocking each other, by keeping multiple versions of each row instead of locking it.
Why It Exists
The naive way to stop concurrent transactions from seeing each other's half-finished work is locking — a writer takes a lock, and any reader has to wait for it to release. That works, but it tanks throughput under real concurrent load, since readers and writers constantly queue behind each other. MVCC exists to let a reader see a consistent snapshot of the data exactly as it existed at one point in time, without ever blocking a concurrent writer — and without the writer ever having to wait for the reader either.
Real World Example
A long-running report query starts reading the `orders` table at 10:00:00, computing a total. A separate transaction updates several of those same order rows at 10:00:01. Because of MVCC, the report query keeps seeing the row versions exactly as they were at 10:00:00 for its entire duration — it isn't blocked by the update, and it never sees an inconsistent mix of some old and some new rows either.
Example Data
Two versions of the same logical row — Postgres exposes this via hidden xmin/xmax columns
| Row Version | xmin (created by) | xmax (superseded by) | status |
|---|---|---|---|
| orders row, version 1 | txn 501 | txn 812 | old — kept for any transaction still reading it |
| orders row, version 2 | txn 812 | (none yet) | current — visible to new transactions |
How MVCC Actually Works
Row Versions, Not Locks
An update doesn't overwrite a row in place — it creates a new version of that row and marks the old one as superseded, rather than locking the row and making other transactions wait.
Each Transaction Sees a Snapshot
When a transaction starts, it effectively gets a snapshot of the database as of that moment. Every read within that transaction sees row versions valid as of that snapshot, regardless of what other transactions commit afterward.
Old Versions Get Cleaned Up Eventually
Once no active transaction's snapshot still needs an old row version, it becomes safe to reclaim. In Postgres, this cleanup is handled by a background process called VACUUM — until it runs, old versions ('dead tuples') accumulate.
Writers Still Conflict With Writers
MVCC solves reader-vs-writer blocking specifically. Two transactions trying to update the exact same row at the same time still conflict — one has to wait for or abort against the other. MVCC doesn't remove write-write contention.
Diagram
A reader keeps its original snapshot even while a writer creates a new row version
Transaction starts
sees a snapshot as of this moment
Concurrent writer updates the row
creates a new version; old version kept
Reader keeps using its original snapshot
never blocked, never sees the new version
Once no transaction needs it
the old version is cleaned up (e.g. VACUUM)
Common Mistakes
Assuming MVCC means writers never block writers
Why: MVCC solves reader-vs-writer blocking. Two transactions trying to update the same row concurrently still genuinely conflict — MVCC doesn't eliminate write-write contention.
Fix: Understand MVCC removes reader-writer blocking specifically, not all forms of concurrency conflict.
Not accounting for old-row-version cleanup
Why: In Postgres specifically, old row versions accumulate as dead tuples until a background process (VACUUM) reclaims them — a workload with heavy updates and misconfigured autovacuum can bloat table size and slow every query.
Fix: Monitor and tune autovacuum (or the engine's equivalent) rather than assuming old versions clean themselves up instantly.
Assuming a long-running read transaction has no real cost
Why: As long as an old transaction's snapshot might still need an old row version, that version can't be cleaned up — a forgotten long-running transaction can silently block cleanup and cause bloat across the whole database, not just the rows it's reading.
Fix: Keep transactions as short as possible, and monitor for unexpectedly long-running ones in production.
Interview Questions
What problem does MVCC solve compared to lock-based concurrency?
Lock-based concurrency makes readers and writers block each other, hurting throughput under load. MVCC lets a reader see a consistent snapshot of the data without ever blocking a concurrent writer, and without the writer waiting for the reader either.
Does MVCC eliminate the need for locks entirely?
No. MVCC removes the need for readers and writers to block each other, but two writers updating the exact same row still need to coordinate — typically through row-level locking or a conflict-detection mechanism — since only one of their conflicting updates can win.
A Postgres database is suffering from table bloat despite normal-looking write volume. How does this connect to MVCC, and what would you check?
MVCC keeps old row versions around until no active transaction's snapshot still needs them, and Postgres relies on autovacuum to reclaim them afterward. Bloat despite normal write volume usually points to either autovacuum being misconfigured or too infrequent for the update rate, or a long-running transaction somewhere holding an old snapshot open and preventing cleanup — I'd check `pg_stat_activity` for long-running transactions first, then autovacuum settings and activity.
Production Best Practices
Do
✓Keep transactions as short as possible to avoid blocking version cleanup.
✓Monitor autovacuum (or your engine's equivalent) activity and tuning under heavy update workloads.
✓Understand that MVCC solves reader-writer blocking, not writer-writer contention.
Don't
✗Don't assume old row versions vanish instantly once superseded.
✗Don't leave long-running read transactions open longer than necessary in an MVCC engine.
✗Don't assume MVCC removes the need to think about write-write conflicts.
Comparison
| Lock-Based Concurrency | MVCC | |
|---|---|---|
| Readers block writers? | Yes | No |
| Writers block readers? | Yes | No |
| Extra storage cost | None | Multiple row versions until cleaned up |
| Cleanup needed? | No | Yes (e.g. Postgres VACUUM) |