Multi-Version Concurrency Control (MVCC)

📋 Overview

MVCC is the foundational concurrency control method used by modern relational databases (PostgreSQL, MySQL/InnoDB, Oracle) to provide high performance under heavy read/write contention. Its core mantra is: "Readers never block writers, and writers never block readers." It achieves this by maintaining multiple versions of a single data row, allowing each transaction to see a consistent "snapshot" of the database as it existed at a specific point in time.

---

🏗️ Core Principles & Characteristics

• Snapshot Isolation: Every transaction operates on a consistent view of the data. Even if another transaction commits an update during your read, you continue to see the data as it was when you started.
• Version Chain: Instead of overwriting a row, an update marks the old row as "deleted" (metadata update) and inserts a new row. These versions are often linked in a hidden chain.
• Transaction IDs (XIDs): Each row contains hidden metadata columns:
  • xmin: The ID of the transaction that created the version.
  • xmax: The ID of the transaction that deleted/superseded it.
• Visibility Rules: A transaction can see a row version only if its xmin is committed and its xmax is either null or belongs to an uncommitted/later transaction.

---

⚖️ Trade-offs: Pros & Cons

• Pros:
  • High Concurrency: Massive performance boost for read-heavy workloads (typical of web apps).
  • No Read Locks: Eliminates the need for "Shared Locks" that would otherwise stall writers.
• Cons:
  • Write Amplification: An update to a small column requires writing an entirely new row version.
  • Storage Bloat: Old versions (dead tuples) consume disk space until they are cleaned up.
  • Vacuuming Overhead: Requires a background process (like Postgres VACUUM) to reclaim space, which can cause IO spikes.

---

🌍 Real-World Implementation

• PostgreSQL: Uses "In-Table" MVCC. Old versions stay in the main table. This makes reads very fast but puts pressure on the autovacuum daemon.
• MySQL (InnoDB): Uses "Undo Logs." It keeps only the latest version in the table and stores older versions in a separate undo log segment. This prevents table bloat but makes long-running reads slightly slower as they have to "reconstruct" old data from logs.
• CockroachDB: Uses MVCC at the storage layer (RocksDB/Pebble) to provide distributed ACID transactions across global clusters.

---

💡 Interview "Gotchas" & Tips

• The "Long-Running Transaction" Trap: A single uncommitted transaction can prevent the database from cleaning up any dead versions created after it started. This leads to massive disk bloat.
• Phantom Reads: Be aware of how MVCC interacts with isolation levels. In "Read Committed," you might see different snapshots between two selects. In "Repeatable Read," the snapshot is frozen.
• Write-Write Conflicts: While MVCC stops readers from blocking writers, two writers trying to update the same row still require a "Pessimistic Lock" on that row.

---

📐 Suggested Architecture Primitives

• Dead Tuple Monitoring: Monitoring pg_stat_user_tables to track the ratio of live vs. dead rows.
• Undo Retention: In MySQL, tuning innodb_undo_directory and retention periods for high-churn databases.
• Index-Only Scans: A performance optimization where the database reads data directly from the index if the visibility information is already cached (Visibility Map).