What is MVCC (multi-version concurrency control), and how does it let readers avoid blocking writers?
Quick Answer
MVCC keeps multiple versions of a row simultaneously — when a row is updated, the engine creates a new version rather than overwriting the old one in place, and each transaction sees a consistent snapshot of the data as of some point in time. This means readers never need to wait for writers (and vice versa) to see a consistent view, because a reader simply looks at the version of each row that was committed as of its snapshot, ignoring newer, uncommitted, or later versions.
Detailed Answer
The problem MVCC solves
A simpler concurrency model — pure locking, no versioning — would require every reader to acquire a shared lock and every writer to acquire an exclusive lock, meaning a long-running read blocks writers, and a write blocks all readers until it commits. That's simple but kills concurrency for read-heavy workloads.
How MVCC works
Instead of updating a row in place, an UPDATE (conceptually) creates a new version of the row, tagged with the transaction ID that created it, while the old version stays around (marked as superseded, but not yet physically removed) as long as any active transaction might still need to see it.
Row (id=1), before update: [ version 1: balance=500, created_by=txn_10, valid_until=txn_15 ]
After UPDATE by txn_15: [ version 1: ...valid_until=txn_15 ]
[ version 2: balance=400, created_by=txn_15, valid_until=(open) ]
Each transaction operates against a snapshot — effectively "the set of row versions committed as of the moment my transaction/statement started." A reader's SELECT simply picks the version of each row that was valid as of its snapshot, entirely ignoring whatever a concurrent writer is doing to create newer versions.
The key benefit: readers don't block writers, and vice versa
-- Transaction A (long-running report query)
BEGIN;
SELECT SUM(balance) FROM accounts; -- takes 30 seconds, sees a consistent snapshot
-- Transaction B, running concurrently, is NOT blocked by A's SELECT
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- succeeds immediately, doesn't wait for A's SELECT to finish
Transaction A's report simply doesn't see B's update (it wasn't committed as of A's snapshot) — it gets a consistent, if slightly stale, view rather than being blocked or seeing a torn/partial state.
The cost: old versions must be cleaned up
Since old row versions stick around until no transaction could possibly need them, MVCC engines need a garbage-collection mechanism:
- PostgreSQL:
VACUUM(usually viaautovacuum) reclaims space from dead row versions ("dead tuples"). Under-vacuumed tables can bloat significantly, and in extreme, badly-managed cases risk transaction ID wraparound issues. - MySQL/InnoDB: maintains an undo log of old versions, purged by a background thread once no longer needed; a very long-running transaction can bloat the undo log by preventing purges.
- Oracle: similarly uses undo segments/tablespaces for the same purpose.
Why this matters for interviews
MVCC explains a lot of otherwise-confusing behavior: why a SELECT in PostgreSQL basically never blocks on a concurrent UPDATE, why a long transaction can cause table bloat, and why "Repeatable Read" is comparatively cheap to implement in an MVCC engine (it's mostly "pick your snapshot once, at transaction start, instead of per-statement") compared to a purely lock-based concurrency model.