📸 Introduction: The Google Doc History
Imagine you are editing a school essay in a shared document:
- Lock-Based System: When you want to edit a sentence, the document locks up. All other classmates are blocked from reading the document until you hit save. Everyone stands around waiting.
- MVCC System (Version Control): When you edit a sentence, the system leaves the old version untouched for readers. While you are typing the new version, your classmates keep reading the original snapshot of the document. The moment you submit your edit, the system switches new readers to view the updated version, keeping the old history on disk.
In databases, MVCC (Multi-Version Concurrency Control) is this exact system! Instead of locking rows and making readers wait for writers, the database creates a new copy (version) of a row every time it is updated.
This means readers never block writers, and writers never block readers!
⚙️ How MVCC Works Under the Hood
When you update a row in an MVCC database (like PostgreSQL), the engine doesn't overwrite the data on disk. It inserts a new row version alongside the old one.
Every row has hidden metadata columns to track validity:
xmin: The Transaction ID that created the row.xmax: The Transaction ID that deleted or replaced the row.
ROW VERSIONS ON DISK (Update Alice's location)
+---------+--------------------+-----------------------+------------+------------+
| Version | Username | Location | xmin (Min) | xmax (Max) |
+---------+--------------------+-----------------------+------------+------------+
| V1 (Old)| Alice | New York | Tx #100 | Tx #101 | <--- Invisible to Tx #102
| V2 (New)| Alice | San Francisco | Tx #101 | Null | <--- Visible to Tx #102
+---------+--------------------+-----------------------+------------+------------+
The Life Cycle of an MVCC Update
- Transaction #101 updates Alice's location from "New York" to "San Francisco".
- The database writes a new row version (V2) with
xmin = 101. - The old row (V1) is marked as deleted/replaced by setting
xmax = 101. - Any active transactions older than #101 will continue reading V1. New transactions will read V2.
- Garbage Collection (Vacuum): Once all old transactions finish, the database cleans up the old V1 row to free space (this is called
VACUUMin PostgreSQL).
💻 Code Examples: Simulating Version Visibility
Let's write a python/code script to simulate how MVCC checks row visibility using Transaction IDs.
Multi-Language Execution
⚠️ Common Mistakes
1. Forgetting Vacuum (Bloat accumulation)
In MVCC databases, deletes don't free disk space immediately—they just mark rows dead. If you don't run regular database cleanup (auto-vacuuming), your tables will double in size due to dead row versions ("bloat"), slowing down sequential scans.
2. High Transaction ID Wrap-around
Transaction IDs are limited numbers (usually 32-bit integers). If your database runs billions of transactions, the ID counter wraps back to 0. If not managed, the database will freeze to prevent old data from suddenly becoming invisible!
🔍 Interview Corner
Q1: What is the main advantage of MVCC over lock-based concurrency control?
In a lock-based system, reading data blocks writing data, and writing data blocks reading data. In MVCC, readers do not block writers, and writers do not block readers, enabling high-performance concurrency.
Q2: What is "bloat" in MVCC databases, and how is it resolved?
Bloat refers to the accumulated disk space occupied by deleted or old, replaced row versions that are no longer visible to any active transactions. It is resolved by running a background vacuum cleaner process (Garbage Collection / VACUUM) to reclaim page space.
📝 Summary
- MVCC enables concurrency by keeping multiple versions of updated rows.
- Readers read isolated consistent snapshots without acquiring locks.
- Old versions are cleaned up later by database garbage collection (
VACUUM).