In a database that uses multi-version concurrency control (MVCC), why can a long-running SELECT keep reading rows while other transactions update those same rows, with neither side waiting on the other?
answer
- Update = new version, not overwrite
- Snapshot picks the visible version
- No shared lock needed to read
- Remaining conflict: writer vs writer
- Cost: old versions + cleanup
basics
~20 sAn update writes a new version of the row instead of overwriting it in place. The reader keeps seeing the version that was current when its snapshot began, so it needs no lock on the row, and the writer has no reader lock to wait behind.
solid answer
~50 sMVCC engines do not destroy the current row image when a row is updated. An UPDATE creates a **new version** of the row and leaves the previous version in place until no running transaction can still need it. Every transaction reads against a **snapshot**: a rule that decides, for each row, which version that transaction is entitled to see. Because of that, a plain SELECT does not need a shared lock to get a stable read. The version it may see cannot be mutated out from under it, since writers append versions rather than overwrite. The consequences are the classic pair: - readers do not block writers (the reader holds no lock the writer must acquire), and - writers do not block readers (the reader uses an older version, not the one being written). The costs are extra storage for old versions and background work to reclaim them. The blocking that remains is writer-versus-writer on the same row, plus schema-level locks.
code
text · 5 linesT1: BEGIN; SELECT sum(amount) FROM orders; -- long scan, snapshot at t0
T2: BEGIN; UPDATE orders SET amount=5 WHERE id=7; -- creates new version
T2: COMMIT; -- no wait for T1
T1: ...still scanning, still sees the t0 version of row 7
T1: COMMIT;go deeper
Recall the mechanism in one breath: updates create a new version, a reader reads the version its snapshot allows, so no read lock is needed and nothing waits.
Add the reciprocal statement (writers don't block readers either) and name the remaining conflict class — writer versus writer on the same row — plus the storage and cleanup cost.
Connect it to operations: long readers hold back version cleanup, snapshot reads can be stale so read-modify-write needs explicit protection, and lock waits you observe are write-side, not read-side.
Frame it as a design tradeoff: MVCC trades space and reclamation work for read-path concurrency, moves the contention point to hot rows, and still requires an extra mechanism if you need true serializability.
## The problem Concurrency control must give each transaction a coherent view of the data while other transactions change it. The oldest general answer is locking. Before reading a row you take a **shared (S) lock**; before writing it you take an **exclusive (X) lock**. S locks are compatible with each other but not with X, so a reader and a writer on the same row must take turns. Under strict two-phase locking those locks are held to commit, so a long report can stall writes for its whole duration, and a long write transaction can stall readers. ## What MVCC changes MVCC (multi-version concurrency control) keeps **several physical versions of the same logical row**. An UPDATE does not overwrite the stored bytes of the live row; it produces a new version and marks the old one as superseded by the updating transaction. A DELETE similarly marks the version dead rather than erasing it immediately. Old versions stay readable until the engine can prove nobody needs them. Each transaction gets a **snapshot**: enough bookkeeping (typically which transactions had committed at a point in time) to decide, for any version it encounters, whether that version is visible to it. A read then means: walk the row's versions and pick the one the snapshot says is current for me. At READ COMMITTED a new snapshot is usually taken per statement; at REPEATABLE READ / SNAPSHOT the snapshot is taken once for the transaction. Because visibility is decided from immutable version metadata, a reader needs **no lock at all** on the rows it reads. Nothing another transaction does can change which version this reader is entitled to see: a concurrent UPDATE only adds a version that this snapshot will reject. That is the whole mechanism behind the slogan *readers don't block writers, and writers don't block readers*. ## What this buys you - **Reporting alongside OLTP.** A ten-minute analytical scan runs against a consistent point-in-time view without freezing the write path. - **Predictable read latency.** Reads do not queue behind write transactions holding X locks. - **Fewer deadlocks.** Deadlock needs at least two lock waiters; removing read locks removes a large class of lock cycles. - **Consistent reads for free.** The reader sees a single point in time, not a smear of rows read at different moments, without holding locks to get it. ## What it does not buy you - **Write-write conflicts still block.** Two transactions updating the same row still serialize: the second waits on the first transaction's row lock and only proceeds when it commits or rolls back. - **Stale-by-design reads.** A reader is looking at a snapshot, so what it returns may already be out of date at the moment it returns. This is the source of the classic *lost update* bug when application code reads, computes, then writes back without extra protection. - **Not full serializability by itself.** Plain snapshot isolation permits anomalies such as write skew; engines add either explicit locking or a serializable layer (for example serializable snapshot isolation) on top. - **Space and maintenance.** Superseded versions accumulate and must be reclaimed, either in the main storage structure or in a separate undo/rollback area. Long-running readers hold back that reclamation, because their snapshot may still need old versions. ## Where the versions physically live Two broad designs exist, and the difference matters for costs, not for the readers-don't-block-writers property. In an **append-in-place** design (PostgreSQL-style), new versions are written into the table's own pages and old versions are cleaned up later by a background process. In an **undo-log** design (InnoDB, Oracle-style), the row in the table holds the newest version and readers reconstruct older versions by applying undo records backwards. Either way the reader gets its version without taking a row lock; either way the engine must eventually discard versions nobody can see. ## How to say it in an interview State the mechanism first (updates create versions, reads pick a version via a snapshot, therefore reads take no row locks), then the two implications (readers don't block writers, writers don't block readers), then the honest limits (writer-writer conflicts still block; versions cost storage and cleanup; snapshot reads can be stale). That order shows you understand the cause rather than reciting the slogan.
- If readers take no locks, what still blocks in an MVCC engine?Writer-versus-writer conflicts on the same row: the second updater waits on the first transaction's row lock until it commits or aborts. Explicit locking requests (such as SELECT ... FOR UPDATE) deliberately reintroduce blocking. Schema changes and other object-level locks can also block both readers and writers.
- What is the price the engine pays for never overwriting rows in place?Multiple versions of the same row exist simultaneously, so tables and indexes consume more space and scans may touch versions they end up discarding. The engine must run cleanup to reclaim versions no snapshot can see, and a long-running transaction holds back that cleanup because its snapshot may still need old versions.
- Does MVCC mean a SELECT always returns current data?No. It returns the data as of its snapshot, which may be older than the committed state at the instant the result is delivered. That is why read-then-write logic in application code needs explicit locking or a conflict check; a snapshot read alone gives no guarantee that the row is unchanged when you write it back.
Like a wiki page: an editor saves a new revision instead of erasing the old one, so a reader who opened revision 12 keeps reading revision 12 undisturbed while revision 13 is being written.
saying these in an interview costs you the question
- Saying MVCC means there are no locks at all, when write-write conflicts still block
- Claiming readers see 'the latest data' rather than their snapshot
- Describing an UPDATE as overwriting the row and 'copying the old value somewhere for rollback only'
- Assuming MVCC gives serializability by itself
- Ignoring that old versions consume space and require cleanup