Multi-Version Concurrency Control (MVCC) is a concurrency control method used in databases to allow multiple users to read and write to the database without blocking each other.
Instead of locking data for every read/write, MVCC keeps multiple versions of a data item, so readers and writers can operate concurrently and consistently.
🧠 Key Idea:
When a transaction reads data, it sees a snapshot of the database — the most recent version that was committed before it started.
When a transaction writes, it creates a new version rather than modifying the existing one.
🔄 Example:
Imagine a row in a table:User(id=1, name="Alice")
- Transaction A starts and reads the name: sees
"Alice". - Transaction B starts after A and updates the name to
"Alicia", then commits. - Transaction A, still running, reads the name again — it still sees
"Alice"(its snapshot). - Later transactions will see
"Alicia"— the new committed version.
📚 How It Works (Simplified)
Each row stores:
- Created version (Xmin): the transaction ID that created this version.
- Deleted version (Xmax): the transaction ID that invalidated it (if any).
When a transaction reads data:
- It checks whether a version was visible at the time the transaction started.
When writing:
- It creates a new row version and marks the old one as outdated (but doesn’t delete it immediately).
✅ Benefits
- Non-blocking reads: readers don’t block writers and vice versa.
- Consistent snapshots: every transaction sees a consistent view of the data.
- Good performance for read-heavy workloads.
⚠️ Trade-offs
- More storage: due to multiple versions.
- Garbage collection needed: old versions must be cleaned up (e.g., PostgreSQL’s
VACUUM). - Complex logic to manage version visibility.
🛠️ Used In:
| Database | MVCC Support | Notes |
|---|---|---|
| PostgreSQL | ✅ | Full MVCC, VACUUM cleans old tuples |
| MySQL (InnoDB) | ✅ | Uses undo logs for old versions |
| Oracle | ✅ | Uses rollback segments |
| SQLite | ✅ | Since version 3.7.0 |
🧪 MVCC vs Locking
| Feature | MVCC | Lock-Based Control |
|---|---|---|
| Read performance | High (no blocking) | Readers may wait for locks |
| Write contention | Handled via versioning | Handled via locks |
| Complexity | Higher internals | Simpler conceptually |
| Snapshot isolation | Easy | Hard |