MVCC

Hook: Interviewers love MVCC because it explains why a busy database can still feel fast.

Question: What is MVCC in SQL databases?

Answer: MVCC stands for Multi-Version Concurrency Control. It is a way for a database to keep more than one version of a row so readers can see a stable snapshot while writers make changes. That means a query usually does not block another query just because a row is being updated. The main trade-off is extra storage and cleanup work for old row versions.

Interview-Ready Answer: “MVCC means the database keeps multiple row versions instead of overwriting data in place. When I run a query, I see a snapshot of rows that were committed before my transaction started, so readers do not block writers in the common case. Updates create a new version, and old versions stay around until no active transaction can see them. That is why MVCC improves concurrency, but it also creates dead rows that vacuum or undo cleanup has to remove.”

What MVCC really means

MVCC = Multi-Version Concurrency Control. Instead of overwriting a row in place, the database keeps row versions. A transaction sees a snapshot, meaning the set of committed row versions it is allowed to read at that moment. That is why a long query is not surprised by half-finished writes.

How it works under the hood

1. A transaction starts and gets a snapshot. In PostgreSQL, that snapshot records which transaction IDs were active, so the engine can decide which row versions are visible.
2. When you SELECT, the engine finds candidate rows and checks visibility. A row version is visible if its creating transaction committed before your snapshot and its deleting or updating transaction is not visible to that snapshot.
3. When you UPDATE, the database usually creates a brand-new row version. The old version is marked as no longer current, but it is not physically erased right away.
4. When you DELETE, the row is also just marked dead for future snapshots. Your current query stops seeing it, but older snapshots may still see it until they finish.
5. Cleanup happens later. A background process such as VACUUM in PostgreSQL removes dead versions only after no active transaction can possibly need them. This is why long-running transactions are dangerous.
6. Each engine stores versions differently. PostgreSQL keeps old row versions in the table heap, while MySQL InnoDB relies heavily on undo logs. Same idea, different plumbing.

Why it is used

MVCC is used because it makes read-heavy systems feel fast: readers usually do not block writers, and writers usually do not block readers. That is perfect for checkout pages, feeds, dashboards, and APIs where many small reads happen all the time.

Performance and edge cases

• Read cost: visibility checks are cheap, roughly O(1) per row version, but a query can touch more pages if the table has many dead rows.
• Write cost: each update creates new row data, so heavy updates generate more I/O than in-place overwrites.
• Cleanup cost: in PostgreSQL, autovacuum often triggers around 20% dead tuples plus a small threshold of 50 rows by default, so bloat can grow if the table is large or updates are constant.
• HOT updates: PostgreSQL can do a Heap-Only Tuple update when no indexed column changes and there is room on the page; this avoids touching indexes and is much cheaper.
• Long transactions: a transaction left open for minutes or hours can pin old versions, block cleanup, and grow disk usage fast.
• MVCC is not magic: writes still lock the rows they modify, and SERIALIZABLE isolation can still abort a transaction to prevent anomalies.

Memory model: think “readers get yesterday’s newspaper, writers print today’s edition, and vacuum recycles the old papers later.”

Real-world story

Imagine a checkout service for a shopping app. Hundreds of readers check item price and stock while orders constantly decrement inventory. MVCC lets those reads keep moving without waiting for each update, so the page stays responsive during a flash sale.

What can go wrong: a reporting job opens a transaction, runs a few SELECTs, then sits idle for 45 minutes because the app forgot to COMMIT. That one old snapshot pins dead row versions, autovacuum cannot clean them, table files grow, and query latency climbs from tens of milliseconds to hundreds.

Symptoms: disk usage spikes, autovacuum messages mention old snapshots or skipped cleanup, and the app starts logging slow queries. User impact: stock checks slow down, checkout may stall, and eventually the database can even run out of disk space.

Follow-up & Tricky Questions:

• How does MVCC improve concurrency? Readers usually do not block writers because they read a snapshot, so a busy system can serve many queries at once. The trade-off is that the database must later clean up old versions.
• What is a snapshot? A snapshot is the set of committed row versions your transaction is allowed to see. It is the reason two transactions can look at the same table and still get different answers.
• Does MVCC remove all locks? No. MVCC mainly helps reads avoid blocking; writes still lock the rows they modify, and some statements like SELECT ... FOR UPDATE also take locks on purpose.
• Why do long transactions hurt? They keep old row versions alive because cleanup must preserve anything that might still be visible to that transaction. That leads to bloat and slower vacuuming.
• What is the difference between Read Committed and Repeatable Read? In Read Committed, each statement gets a fresh snapshot, so you can see newer committed data on the next statement. In Repeatable Read, the whole transaction uses one snapshot, so repeated reads stay stable.
• Does MVCC prevent lost updates automatically? Not always. MVCC improves visibility and concurrency, but lost-update prevention depends on the isolation level, locking, or application logic.
• Does DELETE physically remove the row right away? Usually no. It makes the row invisible first, and physical cleanup happens later when vacuum or undo cleanup decides it is safe.
• Can SERIALIZABLE still fail? Yes. Serializable isolation can abort one transaction with a serialization failure if it detects a dangerous conflict pattern; that is expected and must be retried.

• Tricky: “If I update a row, do other transactions instantly lose the old value?” No. Their snapshot may still see the older version until their transaction finishes, which is the whole point of MVCC.
• Tricky: “If readers do not block writers, can two writers safely update the same row at the same time?” Not without coordination. The database still uses row locks or conflict checks to stop two writers from silently corrupting the same row.
• Tricky: “Does MVCC mean no deadlocks?” No. Deadlocks can still happen when transactions take locks in different orders; MVCC reduces some waiting, but it does not eliminate all locking problems.

Common Mistakes:

• Mistake: Saying MVCC means “no locks at all.” Correction: MVCC reduces reader-writer blocking, but writes still lock rows and some statements lock intentionally.
• Mistake: Thinking DELETE immediately frees disk space. Correction: The row becomes invisible first; physical space is reclaimed later by cleanup.
• Mistake: Ignoring long transactions. Correction: They pin old versions, slow vacuum, and can cause table bloat.
• Mistake: Assuming every database implements MVCC exactly the same way. Correction: PostgreSQL stores row versions in the heap, while InnoDB leans on undo logs.

Memory Hook: “MVCC is a newspaper stand: readers take a copy of today’s paper, writers print a new edition, and the recycler removes old copies later.”

Cheat Sheet:

• MVCC = multiple row versions for concurrent reads and writes.
• Readers see a snapshot; writers create new versions.
• Old versions stay until cleanup is safe.
• PostgreSQL uses heap tuples and vacuum; InnoDB uses undo logs.
• Big risk: long transactions cause bloat.
• MVCC improves concurrency, but it does not remove all locks or all conflicts.

Practice Tasks:

• Run the SQL demo and inspect the xmin, xmax, and ctid columns after each change.
• Open two SQL sessions and compare READ COMMITTED versus REPEATABLE READ on the same row.
• Create a table, update the same row many times, then check how long-running transactions affect vacuum and table size.