MVCC (multi-version concurrency control) is how most modern relational databases let readers and writers work at the same time: instead of overwriting a row in place and making everyone else wait, the engine keeps several versions of it and lets each transaction read the version that was current when its snapshot was taken. Interviewers probe it because it explains isolation levels, why VACUUM exists, why a forgotten open transaction can bloat a production database, and why two writers still collide. For a Backend or AI engineer it is the mechanism behind "why did my long analytical query or my stuck migration hurt the whole OLTP database" and behind every "read a consistent view without locking" claim you will make in a design review. This is a learn-after-the-basics topic: it assumes you already know the isolation levels and the basics of locking.
Without MVCC (lock-based reads) With MVCC (snapshot reads)
Writer W: UPDATE balance = 700 Writer W: UPDATE balance = 700
Reader R: SELECT balance --> BLOCKS Reader R: SELECT balance --> 1000 (old version, no wait)
until W commits or aborts W commits.
Reader R (same txn): still 1000
New txn N: 700
Row "balance" over time (each UPDATE adds a version, it does not overwrite):
v1: 1000 created by T1, deleted by T3 <- visible to snapshots taken before T3 committed
v2: 700 created by T3, deleted by (none) <- visible to snapshots taken after T3 committedLock-based reading forces a trade: either readers take shared locks (and block writers, and are blocked by them) or you accept dirty reads. MVCC changes the trade. Every write produces a new version, and every transaction gets a snapshot that decides which version it sees. A plain SELECT therefore never waits for a row writer, and a writer never waits for a plain SELECT. The price is paid elsewhere: extra storage for old versions, background cleanup work, and version-visibility checks on every row read.
Two caveats interviewers like to hear. First, "readers never block" applies to ordinary snapshot reads; SELECT ... FOR UPDATE, DDL such as ALTER TABLE, and explicit table locks still take locks. Second, MVCC is a storage and visibility mechanism, not an isolation level: what it delivers (per-statement snapshots or one snapshot per transaction) depends on the isolation level chosen.
Heap page for table accounts (PostgreSQL). Each row version is a "tuple" with hidden columns:
ctid xmin xmax data
(0,1) 100 105 balance=1000 <- xmin: txid that created it; xmax: txid that deleted/updated it
(0,2) 105 0 balance=700 <- xmax=0: not deleted (the live version)
UPDATE = mark old tuple's xmax with my txid + insert a NEW tuple with xmin = my txid
DELETE = set xmax only
Snapshot = (xmin_horizon, xmax_next, list of in-progress txids) taken at statement start (READ COMMITTED)
or at first statement of the transaction (REPEATABLE READ / SERIALIZABLE)
A tuple is visible to a snapshot when:
1. its xmin committed AND is "before" the snapshot (not in-progress, not started after it)
2. AND its xmax is unset, aborted, or NOT visible to the snapshot (deleter still running / committed later)
(a transaction always sees its own writes)When a transaction aborts, nothing has to be undone: its tuples are simply never visible, because the commit status of xmin is "aborted". That is why PostgreSQL rollbacks are cheap. The flip side is that a heavily updated table accumulates dead tuples — versions no snapshot can ever see again. VACUUM (usually run by autovacuum) scans for them, marks their space reusable and cleans dead index entries. Ordinary VACUUM normally makes space reusable inside the table but generally does not shrink the file (it can truncate empty pages at the end); VACUUM FULL rewrites the table under an exclusive lock. If dead tuples pile up faster than they are removed, the result is bloat: bigger tables and indexes, more I/O, slower scans.
The crucial rule: a dead tuple may only be removed if no running transaction could still need it. The oldest running snapshot therefore acts as a cleanup horizon for the whole database. One long-running transaction — an analyst's report, a forgotten BEGIN in a psql session, an idle in transaction connection from a buggy pool, even a lagging replication slot or a stuck prepared transaction — holds that horizon back and blocks cleanup everywhere, not just on the tables it touched. PostgreSQL also has 32-bit transaction ids, so old tuples must eventually be frozen; if vacuum cannot keep up, the database has to take drastic protective action against transaction-id wraparound. HOT (heap-only tuple) updates soften the cost: if no indexed column changes and the new version fits on the same page, the update avoids creating new index entries.
InnoDB keeps the NEWEST version in the clustered index, and older versions in the undo log.
clustered index row: id=7 | balance=500 | DB_TRX_ID=210 | DB_ROLL_PTR --+
|
undo log: [balance=700, TRX_ID=190, ROLL_PTR] <--------------+
|
+--> [balance=1000, TRX_ID=150, ROLL_PTR] --> ...
Consistent read (REPEATABLE READ): build a "read view" (which trx ids were active/committed)
at the first consistent read. Start at the newest row; if its DB_TRX_ID is not visible to the
read view, follow DB_ROLL_PTR into undo to rebuild the previous version, repeat until visible.
Purge thread: deletes undo records (and delete-marked rows / secondary-index entries)
once NO read view can still need them.The design difference from PostgreSQL is where old versions live. In PostgreSQL they sit in the table itself (so the heap bloats and vacuum reclaims it); in InnoDB an UPDATE modifies the clustered-index record in place and writes the "how to get back to the old value" information to the undo log, so the main table is less prone to version bloat. The cost moves to the undo log: a long-running transaction stops purge, the undo history grows (the "history list length" metric), and reading an old snapshot of a hot row means walking a longer chain of undo records, which slows down exactly the long readers that caused the problem. Also, rollback in InnoDB has real work to do — it applies undo records to restore the old values — so rolling back a huge transaction can take as long as the transaction itself.
Isolation details differ by engine, so never generalise: InnoDB READ COMMITTED takes a fresh read view per statement, REPEATABLE READ (the default) uses one read view per transaction plus next-key locks for locking reads and writes. Plain UPDATE statements in InnoDB read the latest committed row ("current read"), not the snapshot version.
Snapshot isolation (what MVCC gives you at PostgreSQL REPEATABLE READ):
T1 begins (snapshot S1) T2 begins (snapshot S2)
T1: UPDATE acct SET bal=600
WHERE id=1; -- locks row / new version
T2: UPDATE acct SET bal=500 WHERE id=1;
--> waits for T1
T1: COMMIT;
PostgreSQL REPEATABLE READ: T2 fails with
"could not serialize access due to concurrent update"
(first-committer-wins; the app must retry T2)
PostgreSQL READ COMMITTED: T2 re-reads the latest row version and proceeds
Write skew (still possible under snapshot isolation):
Rule: at least one doctor on call. Alice and Bob both on call.
T1: reads both on call, sets Alice off. T2: reads both on call, sets Bob off.
Different rows are written, so no write-write conflict; both commit; nobody is on call.MVCC removes read/write blocking, not write/write conflict. Two transactions that modify the same row must be serialized somehow, otherwise you get lost updates. Engines do it either by making the second writer wait and then either re-evaluating against the newest committed row (PostgreSQL READ COMMITTED, InnoDB current reads) or by aborting it so the application can retry (PostgreSQL REPEATABLE READ). Snapshot isolation prevents dirty reads, non-repeatable reads and (in PostgreSQL's implementation) phantoms, but it does not prevent write skew, because the conflicting transactions write different rows after reading overlapping data. PostgreSQL SERIALIZABLE adds predicate tracking (SSI) to detect such dangerous patterns and abort one transaction; the cost is more aborts and the need for retry logic. Locking reads such as SELECT ... FOR UPDATE, or a constraint, are the other standard fixes.
Time ------------------------------------------------------------->
Report txn BEGIN ...................................... (still open, snapshot from t0)
OLTP writes u u u u u u u u u u u u u u <- thousands of UPDATEs, each leaves a dead tuple / undo record
autovacuum / purge "can I remove version X?" --> "NO: the old snapshot may still see it"
Result: PostgreSQL -> table + index bloat, slower scans, vacuum runs but reclaims little
InnoDB -> undo log grows, history list length rises, old-snapshot reads get slower
Extra risks: locks held by the txn block DDL and other writers; connection-pool slots stay occupiedLong transactions are harmful for three separate reasons: they pin the cleanup horizon (space and read cost), they hold row locks and often block DDL queued behind them (a pending ALTER TABLE then blocks every new query behind it), and they occupy connections. Practical guards are a statement_timeout and idle_in_transaction_session_timeout (PostgreSQL), sending long reports to a read replica (mind that replica query conflicts and hot_standby_feedback can transmit the same horizon problem back to the primary), batching large updates into short transactions, and monitoring the age of the oldest transaction. The same principle from other topics applies: never hold a database transaction open across a slow external call such as an HTTP request or a payment API.
xmin (creator) and xmax (deleter/updater); an UPDATE is "mark old tuple dead, insert new tuple". Dead tuples are reclaimed by VACUUM.DB_ROLL_PTR; the purge thread cleans up when no read view needs them.xmax) and creates a new version tagged with the writer's id (xmin) — or, in InnoDB, updates the row in place and records the old value in undo.A toy MVCC store with versioned rows, snapshot reads, first-updater-wins conflicts and a vacuum bounded by open snapshots. It is a teaching model (no aborted-transaction status, no persistence, no locking), not a copy of either engine.
import itertools
class MVCCStore:
"""Toy MVCC: an UPDATE never overwrites; it stamps the old version's xmax and appends a new version."""
def __init__(self):
self._ids = itertools.count(1)
self.versions = {} # key -> list of {"value", "xmin", "xmax"}
self.committed = set()
self.active = {} # txid -> snapshot (set of txids committed when it began)
def begin(self):
txid = next(self._ids)
self.active[txid] = frozenset(self.committed) # the snapshot
return txid
def _sees(self, tx, other): # is `other` a committed-before-my-snapshot txn (or me)?
return other == tx or other in self.active[tx]
def _visible(self, tx, v):
return self._sees(tx, v["xmin"]) and not (v["xmax"] is not None and self._sees(tx, v["xmax"]))
def read(self, tx, key):
for v in reversed(self.versions.get(key, [])):
if self._visible(tx, v):
return v["value"]
return None
def write(self, tx, key, value):
chain = self.versions.setdefault(key, [])
if chain:
latest = chain[-1]
if not self._sees(tx, latest["xmin"]) or (latest["xmax"] not in (None, tx)):
# someone changed this row after my snapshot (or is changing it right now)
raise RuntimeError(f"txn {tx}: write-write conflict on {key!r}")
latest["xmax"] = tx
chain.append({"value": value, "xmin": tx, "xmax": None})
def commit(self, tx):
del self.active[tx]; self.committed.add(tx)
def abort(self, tx): # (a real engine also ignores the aborted txn's versions)
del self.active[tx]
def vacuum(self):
"""Remove versions whose deleter (xmax) committed AND is visible to every still-open snapshot."""
removed = 0
for key, chain in self.versions.items():
keep = []
for v in chain:
x = v["xmax"]
dead = x in self.committed and all(x in snap for snap in self.active.values())
removed += dead
if not dead: keep.append(v)
self.versions[key] = keep
return removed
def dump(self, key):
return [(v["value"], v["xmin"], v["xmax"]) for v in self.versions[key]]
db = MVCCStore()
t = db.begin(); db.write(t, "balance", 1000); db.commit(t) # txn 1 creates the row
report = db.begin() # txn 2: long-running reader
print("report reads :", db.read(report, "balance"))
w1 = db.begin(); db.write(w1, "balance", 700) # txn 3
print("w1 reads its own write :", db.read(w1, "balance"))
print("report while w1 open :", db.read(report, "balance"))
db.commit(w1)
print("report after w1 commits :", db.read(report, "balance"), "(snapshot is frozen)")
fresh = db.begin() # txn 4
print("new txn reads :", db.read(fresh, "balance"))
print("chain (value,xmin,xmax) :", db.dump("balance"))
a = db.begin(); b = db.begin() # txn 5 and 6 race on the same row
db.write(a, "balance", 600)
try:
db.write(b, "balance", 500)
except RuntimeError as e:
print(e)
db.commit(a)
print("vacuum, old report open :", db.vacuum(), "removed ->", db.dump("balance"))
db.abort(b) # loser of the conflict gives up (or retries)
db.commit(report); db.commit(fresh)
print("vacuum, all finished :", db.vacuum(), "removed ->", db.dump("balance"))
# Output:
# report reads : 1000
# w1 reads its own write : 700
# report while w1 open : 1000
# report after w1 commits : 1000 (snapshot is frozen)
# new txn reads : 700
# chain (value,xmin,xmax) : [(1000, 1, 3), (700, 3, None)]
# txn 6: write-write conflict on 'balance'
# vacuum, old report open : 0 removed -> [(1000, 1, 3), (700, 3, 5), (600, 5, None)]
# vacuum, all finished : 2 removed -> [(600, 5, None)]Note how the version (1000, 1, 3) cannot be vacuumed while the old "report" snapshot is open, and disappears only after every transaction that could see it has finished. Now the real engine:
-- PostgreSQL (needs two psql sessions; expected behaviour described in comments, no output is invented here)
CREATE TABLE accounts (id int PRIMARY KEY, balance int);
INSERT INTO accounts VALUES (1, 1000);
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 1000 (snapshot taken here)
-- Session B
UPDATE accounts SET balance = 700 WHERE id = 1; -- autocommit; creates a new tuple version
-- Session A
SELECT balance FROM accounts WHERE id = 1; -- still 1000: A's snapshot is frozen
UPDATE accounts SET balance = 600 WHERE id = 1; -- ERROR: could not serialize access due to concurrent update
ROLLBACK;
-- Peek at the versions (hidden columns) and dead tuples
SELECT ctid, xmin, xmax, * FROM accounts; -- the live version now has a new ctid and xmin
SELECT n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'accounts';
-- Find the transactions holding back cleanup:
SELECT pid, state, xact_start, backend_xmin FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;Q: What is MVCC?
A: Multi-version concurrency control keeps several versions of a row and lets each transaction read the version visible to its snapshot, so readers do not block writers and writers do not block plain readers. A write creates a new version instead of overwriting, and old versions are cleaned up once no snapshot can see them. The trade-off is storage overhead and cleanup work (VACUUM or purge), plus the fact that writer-versus-writer conflicts still have to be resolved.
Q: Does MVCC mean nothing ever blocks?
A: No. Plain snapshot reads do not take row locks that conflict with writers, but two writers on the same row still serialize, SELECT ... FOR UPDATE takes locks, and DDL takes heavier table locks. In PostgreSQL even an ALTER TABLE waiting behind a long transaction can stall every new query queued behind it.
Q: Why do writers still conflict with writers?
A: Two transactions updating the same row cannot both apply changes based on the same old value without losing one update, and a row has only one "latest" version. So the engine makes the second writer wait for the first and then either aborts it with a serialization error (PostgreSQL REPEATABLE READ) or lets it re-read the newest committed row and continue (PostgreSQL READ COMMITTED, InnoDB current reads). Either way the application needs retry logic or a deliberate locking strategy.
Q: What physically happens on an UPDATE in PostgreSQL versus InnoDB?
A: PostgreSQL sets xmax on the old tuple and inserts a completely new tuple, so the heap holds both until VACUUM removes the old one (HOT updates avoid extra index entries when possible). InnoDB updates the clustered-index record in place and stores the previous values in the undo log, chained through DB_ROLL_PTR. Consequences: PostgreSQL tables bloat and need vacuum; InnoDB's undo history grows and old-snapshot reads walk undo chains, but rollback in InnoDB costs real undo work.
Q: Why is a long-running transaction dangerous under MVCC?
A: Its snapshot may still need old versions, so the database cannot reclaim any version newer than that snapshot, on any table. In PostgreSQL that shows up as bloat and vacuum that runs but frees little; in InnoDB as a growing undo log and history list. It also holds locks, blocks DDL, and ties up a connection, so keep transactions short and set timeouts.
Q: How does MVCC give snapshot isolation, and what is still not prevented?
A: If every transaction reads only versions committed before its snapshot and its own writes, it sees a consistent point-in-time view: no dirty reads, no non-repeatable reads, and in PostgreSQL's implementation no phantoms. It does not prevent write skew, where two transactions read overlapping data and write different rows. Preventing that needs SERIALIZABLE (SSI in PostgreSQL, with more aborts), explicit locking reads, or a constraint that makes the invariant enforceable.
Follow-up: VACUUM ran, yet the table file is still huge. Why?
A: Ordinary VACUUM marks dead-tuple space as reusable for future inserts and updates but generally does not return it to the operating system, except empty pages at the end of the file. The table is "bloated" but stable. Reclaiming disk needs VACUUM FULL or a rewrite tool, which take heavy locks or extra space, so the better answer is to stop the bloat by fixing long transactions and tuning autovacuum.
Follow-up: How does READ COMMITTED differ from REPEATABLE READ in PostgreSQL's MVCC?
A: The difference is when the snapshot is taken. READ COMMITTED takes a new snapshot for each statement, so two consecutive SELECTs in one transaction can see different committed data; REPEATABLE READ takes one snapshot at the first statement and keeps it until the end. That is why the second can raise a serialization failure on a concurrent update while the first just re-reads the newest row.
Follow-up: A team says "we use MVCC so we don't need to think about concurrent updates on inventory". What is wrong?
A: A read-then-write cycle (SELECT stock, compute in the app, UPDATE) under a snapshot reads a stale value and can overwrite a concurrent change or oversell. Use an atomic UPDATE ... SET stock = stock - 1 WHERE stock > 0, SELECT ... FOR UPDATE, optimistic version columns, or SERIALIZABLE with retries. MVCC guarantees consistent reads, not correct read-modify-write logic.
MVCC is the reason one PostgreSQL or MySQL instance can serve OLTP writes while dashboards, exports and backups read consistent snapshots (pg_dump relies on it), and it shapes operational design: long reports go to read replicas or a warehouse, migrations run with lock timeouts and short transactions, and connection pools use idle-in-transaction timeouts. For AI and RAG pipelines it matters when ingestion jobs rewrite many rows (embeddings, chunk metadata, status flags): update-heavy tables generate dead versions quickly, so batch sizes, autovacuum settings, and append-only designs with periodic cleanup become real capacity-planning inputs. It also explains why distributed databases with snapshot reads (Spanner-style timestamps, CockroachDB, Aurora read replicas) can offer consistent historical reads without blocking writers: the same idea of "versions plus a snapshot timestamp" scales beyond one node.
Choosing MVCC trades storage and background work for concurrency: readers and writers stop blocking each other, but you must budget for dead versions, cleanup jobs, and visibility checks, and monitor them. The failure mode is quiet: a stuck transaction does not cause errors, it slowly degrades performance and disk. It is the wrong mental model to think it removes the need for concurrency design, because write conflicts, write skew under snapshot isolation, and read-modify-write races remain. Update-heavy tables with tiny rows and constant churn (counters, queues, sessions) amplify version overhead; consider redesigning them as inserts plus aggregation, moving them to Redis, or tuning fillfactor and autovacuum aggressively. Finally, engine details differ (PostgreSQL heap versions and VACUUM versus InnoDB undo and purge), so in an interview always name the engine before claiming a behaviour.