MVCC — Multi-Version Concurrency Control

The Problem MVCC Solves

Traditional locking: T1 is READING a row, T2 wants to WRITE → T2 must WAIT → bad performance, lots of blocking.

MVCC approach: T2 creates a NEW VERSION of the row; T1 still reads the OLD VERSION → No waiting, no blocking.

Core idea: Instead of locking, keep multiple versions of the same data.

How MVCC Works Internally

Every row has hidden metadata:

data	created_by_txn	deleted_by_txn
500	TX_000	TX_001
800	TX_001	—

• When a row is updated → old version is kept, new version is created
• Each transaction sees the version that was current at its start time
• Old versions cleaned up later by vacuum/garbage collector

MVCC vs Locking

	Locking	MVCC
Readers block writers?	Yes	No
Writers block readers?	Yes	No
Multiple versions kept?	No	Yes
Storage overhead?	Low	Higher
Used in	Older DBs	PostgreSQL, MySQL, Oracle

Golden rule of MVCC: "Readers never block writers, writers never block readers"

Snapshot Isolation

MVCC enables Snapshot Isolation — each transaction works on a consistent snapshot of the DB at the time it started.

Garbage Collection

• PostgreSQL → uses VACUUM process
• MySQL InnoDB → uses purge thread
• Removes row versions that no active transaction needs anymore

MVCC and Isolation Levels

• Read Committed → sees snapshot of each STATEMENT
• Repeatable Read → sees snapshot of start of TRANSACTION
• Serializable → SSI on top of MVCC (detects conflicts)

MVCC Implementation by Database

Database	MVCC Implementation
PostgreSQL	Full MVCC with SSI
MySQL InnoDB	MVCC with undo logs
Oracle	MVCC with undo tablespace
MongoDB	MVCC since v4.0