Questions: Multi-Version Concurrency Control (MVCC)

Question 1

Transaction T1 started at time 10. Transaction T2 started at time 15 and is currently updating row R. At time 20, T1 reads row R. What does T1 see under MVCC?

• A. T1 blocks until T2 commits, then reads the new version
• B. T1 reads the version of R that was committed before T1's snapshot time (time 10)
• C. T1 reads the version of R that T2 is currently writing (the in-progress update)
• D. T1 receives an error because row R is locked

Answer: (B)

Under MVCC, each transaction reads from a snapshot taken at its start time. T1's snapshot was taken at time 10, before T2 started. When T1 reads row R, the database finds the most recent committed version of R that existed before time 10 — not T2's in-progress update. This is the core MVCC guarantee: readers never block writers and writers never block readers. T1 proceeds immediately without waiting for T2.

Question 2

Under MVCC, two transactions simultaneously attempt to update the same row. What happens?

• A. Both updates succeed simultaneously, with MVCC creating two new versions
• B. The second transaction reads the old version and writes independently, causing no conflict
• C. The second transaction must wait or abort — write-write conflicts still require coordination
• D. MVCC prevents this situation by making rows read-only during updates

Answer: (C)

MVCC eliminates read-write blocking but does NOT eliminate write-write conflicts. When two transactions try to update the same row, the database must serialize them. PostgreSQL uses a 'first updater wins' policy: the second transaction blocks until the first commits or rolls back, then either proceeds on the new version or aborts depending on the isolation level. This is a critical misconception to avoid: MVCC is not a universal lock eliminator.

Question 3

Under MVCC, a long-running analytical query can cause the database to retain old row versions that would otherwise be garbage collected.

• T. True
• F. False

Answer: True

This is a real and important operational concern. MVCC garbage collection (VACUUM in PostgreSQL) can only reclaim a row version when no active transaction has a snapshot older than that version. A long-running analytical query that holds an old snapshot prevents the database from reclaiming any versions created after its snapshot time. This causes 'table bloat' — the table grows on disk even if rows are being deleted — and can severely degrade performance. It's one of the key reasons why long-running transactions are problematic in MVCC databases.

Question 4

MVCC substantially eliminates the need for any locking in the database.

• T. True
• F. False

Answer: False

MVCC eliminates read-write locking — readers and writers no longer block each other. But write-write conflicts still require locking or conflict detection. When two transactions try to modify the same row, the database must serialize them. Additionally, DDL operations (schema changes), certain isolation levels (serializable), and explicit user-level locks (SELECT FOR UPDATE) still involve traditional locking. MVCC's promise is specifically that reads do not block writes and vice versa — not that locks disappear entirely.

Question 5

Explain why old row versions in an MVCC database cannot be immediately deleted when a transaction updates a row.

Model answer: Old versions must be retained as long as any active transaction has a snapshot older than that version. A transaction reads from a consistent snapshot of the database at its start time, and may need to access old versions to satisfy that snapshot. Only once all active transactions have snapshots newer than the old version — meaning no transaction will ever need to see it — can the version be safely reclaimed by garbage collection.

This lifecycle is what makes MVCC work: creating a new version on update, retaining old versions for active snapshots, and garbage collecting when versions are no longer visible to any active transaction. The failure to garbage collect promptly (due to long-running transactions or a misconfigured autovacuum in PostgreSQL) leads to table bloat, increased disk I/O, and degraded query performance.