Chapter 12: MVCC & Isolation Levels

---

Metadata Card

Dimension	Value
Difficulty	(Intermediate)
Prerequisites	Chapter 11 (ACID), understanding of concurrent transactions
Keywords	MVCC, isolation levels, dirty read, non-repeatable read, phantom read, snapshot isolation, serializable
Code Language	SQL, Python

---

Your Progress

"Administrators started using 'copy notebooks' to avoid interfering with each other — the art of MVCC and isolation levels."

The Three Anomalies

Anomaly	Description	Example
Dirty Read	Read uncommitted data	T1 writes, T2 reads before T1 commits (T1 may rollback)
Non-Repeatable Read	Same query returns different results within a transaction	T1 reads row, T2 updates row, T1 reads again — different value
Phantom Read	Same query returns different rows within a transaction	T1 reads rows matching predicate, T2 inserts, T1 reads again — new row appears

SQL Standard Isolation Levels

Level	Dirty Read	Non-Repeatable	Phantom
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

MVCC — Multi-Version Concurrency Control

Instead of locking, MVCC maintains multiple versions of each data item:

Time ──────────────────────────────────────────→
Row "balance=100"
  │
  │  T1 updates to 200
  │  └── Version 1: balance=100 (readable by older transactions)
  │      Version 2: balance=200 (visible to T1 only)
  │
  │  T2 updates to 300
  │  └── Version 1: balance=100
  │      Version 2: balance=200
  │      Version 3: balance=300
  │
  │  Garbage collection removes version 1 (no active transaction needs it)

Each row has:

• Creation timestamp (XID or commit timestamp)
• Expiration/delete timestamp
• Version chain pointer

How MVCC Implements Isolation

READ COMMITTED: Transaction sees all changes committed before its current statement.

REPEATABLE READ / SNAPSHOT ISOLATION: Transaction sees all changes committed before its start time. A consistent snapshot.

SERIALIZABLE: Full isolation — as if transactions run one after another.

Version Storage

Approach	How	Example
Append-only	New version = new row/tuple	PostgreSQL (heap + dead tuples)
Delta	Store differences from base version	MySQL InnoDB (undo log)
Copy-on-write	Copy page, modify copy	LMDB, Btrfs

MVCC vs Locking

	MVCC	Two-Phase Locking (2PL)
Reads block writes?	No	Yes (read locks)
Writes block reads?	No	Yes (write locks)
Storage overhead	Higher (multiple versions)	Lower
Best for	Read-heavy workloads	Write-heavy, contention-prone

---

Traveler's Notes

MVCC is one of the most elegant solutions in database systems. By keeping multiple versions of data, it allows readers to never block writers and writers to never block readers. This makes modern databases practical under high concurrency. Understanding MVCC and isolation levels helps you choose the right level for your application — balancing correctness against performance.