Skip to content

MVCC (Multi-Version Concurrency Control)

1. What is it? (2-3 lines)

MVCC is a database concurrency control mechanism that allows concurrent reads and writes on the same data row without blocking each other. It achieves this by maintaining multiple timestamped or versioned physical copies (tuples) of a single logical record in storage.

2. Why does it exist?

Traditional locking protocols (e.g., Strict 2PL) block readers when a writer updates a row and block writers when readers access a row. At high QPS, read-write lock contention destroys database throughput. MVCC eliminates read-write blocking ("readers never block writers, and writers never block readers"), dramatically improving concurrent transaction throughput.

3. Where is it used?

  • PostgreSQL: Heap tuple headers with xmin / xmax transaction IDs and VACUUM cleanup.
  • MySQL (InnoDB): Undo logs (rollback segments) and DB_TRX_ID / DB_ROLL_PTR row pointers.
  • Oracle / CockroachDB / TiDB: Multi-version timestamp ordering in LSM-tree / Key-Value stores.

4. How does it work? (simple flow)

  1. Write/Update: Instead of overwriting a row in-place, the database creates a new physical tuple version tagged with the current Transaction ID (TxnID).
  2. Delete: The row isn't immediately purged; it is marked as deleted by setting its expiry transaction header (xmax = current TxnID).
  3. Read (Read View): When a transaction executes a query, the engine builds a snapshot ("Read View") of active transactions.
  4. Visibility Check: The engine scans tuple versions and selects the latest tuple version where xmin is committed and xmax is either unassigned or created after the snapshot start time.
  5. Garbage Collection: Outdated versions invisible to all active snapshots are purged via background threads (Postgres VACUUM or InnoDB Undo Log purging).

5. Advantages

  • High read-write concurrency; reads never lock writes.
  • Consistent point-in-time snapshot isolation without full table/row read locks.
  • Non-blocking historical queries (e.g., repeatable reads during long analytical reports).

6. Limitations

  • Table Bloat: High update/delete velocity generates dead tuples, consuming disk and memory.
  • Garbage Collection Overhead: Requires aggressive background vacuuming/purging, causing periodic I/O spikes.
  • Write-Write Conflicts: Does not prevent write-write blocking; concurrent updates on the same row still require row locks (SELECT FOR UPDATE).

7. Common interview questions (10)

  1. How does PostgreSQL MVCC tuple visibility differ from MySQL InnoDB's Undo Log approach?
  2. What causes PostgreSQL table bloat under high UPDATE workloads?
  3. How does MVCC prevent Non-Repeatable Reads and Dirty Reads?
  4. Can MVCC alone prevent Write Skew anomalies under Repeatable Read isolation?
  5. What is the role of xmin and xmax in PostgreSQL tuple headers?
  6. How does Autovacuum work and what happens during Transaction ID Wraparound?
  7. What is a Read View in MySQL InnoDB and when is it instantiated for READ COMMITTED vs REPEATABLE READ?
  8. How do secondary indexes work under MVCC in PostgreSQL vs MySQL?
  9. How does MVCC impact disk I/O and cache hit ratios compared to 2PL?
  10. How does CockroachDB implement MVCC on top of a distributed Key-Value store (Raft + RocksDB/Pebble)?

8. Common misconceptions

  • Misconception: MVCC eliminates all locking. Fact: MVCC eliminates read-write locks; concurrent writes on the same row still acquire exclusive row-level write locks.
  • Misconception: Updates overwrite data in-place. Fact: Updates insert a brand-new tuple copy (Postgres) or append an Undo log entry (MySQL).

10. 30-second cheat sheet

  • Golden Rule: Readers never block writers; writers never block readers.
  • Postgres Mechanics: In-table tuple versions (xmin/xmax). Needs VACUUM to clean dead rows.
  • MySQL Mechanics: In-table current version + Undo Log pointer (DB_ROLL_PTR). Purge thread clears undo logs.
  • Tradeoff: Exceptional read QPS at the cost of disk bloat and background GC I/O pressure.