What is MVCC (Multi-Version Concurrency Control)?
Learn how Multi-Version Concurrency Control (MVCC) lets databases avoid read/write blocking using versioned row snapshots.
Expected Interview Answer
MVCC (Multi-Version Concurrency Control) is a concurrency-control technique where the database keeps multiple versions of a row so readers see a consistent snapshot as of when their transaction started, while writers create new versions instead of overwriting data in place, letting reads and writes proceed without blocking each other.
Instead of locking a row for every read, MVCC tags each row version with the transaction that created it and a validity range, so a transaction can find the correct version to read without waiting on a lock held by a concurrent writer. Writers append a new version rather than mutating the old one, and old versions are cleaned up later (e.g. PostgreSQL’s VACUUM) once no active transaction can still need them. This gives readers a consistent, repeatable snapshot for the duration of their transaction while writers keep making progress, at the cost of extra storage for old versions and the need for periodic cleanup or garbage collection.
- Readers never block writers and writers never block readers
- Provides a consistent snapshot view for the life of a transaction
- Reduces lock contention compared to strict two-phase locking
- Enables efficient Repeatable Read / Snapshot Isolation without heavy locking
AI Mentor Explanation
Imagine a scoreboard that never erases old totals but instead stacks a new sheet on top every time the score changes, timestamped with when it was posted. A spectator who arrived at over 10 keeps reading the sheet that was current at over 10 throughout the innings, even as new sheets pile up on top from later overs, so their view never jumps around mid-read. MVCC works the same way: each transaction reads the row version that was valid when it started, while newer versions accumulate independently for later readers.
Step-by-Step Explanation
Step 1
Start a transaction and record a snapshot point
The database notes a transaction ID or timestamp marking which row versions are visible to this transaction.
Step 2
Writers create new versions
An UPDATE or DELETE does not overwrite the row in place; it creates a new version tagged with the writing transaction.
Step 3
Readers pick the correct version
Each read filters row versions to the one valid as of the reader’s snapshot, ignoring newer, not-yet-visible versions.
Step 4
Garbage-collect old versions
Once no active transaction can still need an old version, the database reclaims that space (e.g. VACUUM in PostgreSQL).
What Interviewer Expects
- Clear explanation of versioned rows instead of in-place overwrites
- Understanding of how snapshots give readers a consistent view without locking
- Awareness of the cleanup/garbage-collection cost (e.g. VACUUM, bloat)
- Ability to name a real database that uses MVCC (PostgreSQL, MySQL InnoDB, Oracle)
Common Mistakes
- Confusing MVCC with simple row-level locking
- Forgetting that writers still create versions serially and can conflict with each other
- Not mentioning the storage/cleanup overhead of retained old versions
- Assuming MVCC eliminates all anomalies without pairing it with an isolation level
Best Answer (HR Friendly)
“MVCC lets the database keep multiple versions of a row instead of one, so a reader gets a consistent snapshot of the data as of when it started, while writers keep creating new versions in parallel. That means reads do not block writes and writes do not block reads, which is why databases like PostgreSQL feel fast even with lots of concurrent activity, though old versions do need periodic cleanup.”
Code Example
-- Session A starts a transaction and takes an implicit snapshot
BEGIN;
SELECT balance FROM Accounts WHERE account_id = 1; -- sees 1000
-- Session B updates and commits concurrently, creating a new row version
UPDATE Accounts SET balance = 1500 WHERE account_id = 1;
COMMIT;
-- Session A, still in its original transaction, re-reads
SELECT balance FROM Accounts WHERE account_id = 1;
-- under REPEATABLE READ / snapshot isolation, still sees 1000
-- because it is pinned to the version valid at its snapshot start
COMMIT;Follow-up Questions
- How does MVCC differ from strict two-phase locking?
- What causes table bloat in PostgreSQL and how does VACUUM address it?
- How does MVCC interact with Repeatable Read vs Serializable isolation?
- What happens when two concurrent writers try to update the same row under MVCC?
MCQ Practice
1. In MVCC, what happens when a row is updated?
MVCC creates a new row version rather than mutating the existing one, so concurrent readers keep seeing their own consistent version.
2. What is the main benefit of MVCC over pure locking-based concurrency control?
Because readers see a versioned snapshot and writers append new versions, reads and writes can proceed concurrently without blocking.
3. Why do MVCC databases like PostgreSQL need a process like VACUUM?
Old versions accumulate as writes happen; VACUUM (or equivalent) reclaims space once no active transaction can still reference them.
Flash Cards
What is MVCC? — A concurrency technique keeping multiple row versions so readers get a consistent snapshot without blocking writers.
How does MVCC handle an UPDATE? — It creates a new row version instead of overwriting the existing one in place.
Why does MVCC need cleanup? — Old, no-longer-needed row versions accumulate and must be reclaimed (e.g. PostgreSQL VACUUM).
Key benefit of MVCC? — Readers never block writers and writers never block readers.