What is MVCC and how does it let readers and writers coexist?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
MVCC (multi-version concurrency control) keeps multiple versions of each row so readers see a consistent snapshot without blocking writers, and writers create new versions instead of overwriting in place. It is why PostgreSQL reads never wait for writes — at the cost of dead row versions that VACUUM must clean up.
Detailed explanation
Lock-everything concurrency would serialize the database: every report blocks every order update. MVCC avoids this by versioning. In PostgreSQL, an UPDATE doesn't overwrite the row — it inserts a new row version stamped with the updater's transaction id and marks the old version as expired for future transactions. Each transaction gets a snapshot (a list of which concurrent transactions are invisible), so a long-running SELECT sees exactly the data committed before it started, even while writers commit new versions mid-query. Readers never block writers and writers never block readers; only concurrent writers to the same row block (or abort). Consequences operators must know: dead (superseded) row versions accumulate and must be reclaimed by VACUUM/autovacuum — neglected tables bloat, and long-open transactions pin old versions, delaying cleanup and risking transaction-id wraparound. Snapshot isolation also explains write skew: two transactions each read a constraint-satisfying snapshot and commit changes that together violate it (two doctors both going on call) — prevented only by Serializable isolation or explicit locking. MySQL's InnoDB implements the same idea with undo logs instead of in-table versions, with analogous purge mechanics.
Example
-- Session A starts a long report:
-- BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SELECT sum(total_cents) FROM orders; -- snapshot fixed here
-- Session B concurrently:
-- INSERT INTO orders ... ; COMMIT; -- creates new row versions, never blocked
-- Session A re-runs the same SELECT: identical result (stable snapshot).
-- Dead versions from B's old updates linger until VACUUM reclaims them.Interview tip
Explain it as snapshots plus version chains in two sentences, then name the operational cost (VACUUM/bloat) and the correctness caveat (write skew). Operators and seniors are judged on those two follow-ups.
Common mistakes
- Believing MVCC eliminates all locking — concurrent writers to one row still conflict.
- Leaving transactions idle in open state, pinning bloat and stalling VACUUM.
- Missing that snapshot isolation still permits write skew.