MVCC & Isolation Levels — Notes

Why MVCC

• Readers don't block writers; writers don't block readers.
• Enables consistent snapshots for backups, analytics, long reads.

Storage cost

• Each update creates a new tuple → bloat.
• Postgres VACUUM, InnoDB undo log purge, Oracle UNDO tablespace.
• Long open transactions block GC.

Write skew (the classic SI gotcha)

• Two surgeons on call; each checks the other is on call (true), then both go off.
• Both txns see the same snapshot, both commit, system ends up with zero on call.
• Fix: Serializable / SSI / explicit predicate lock / SELECT FOR UPDATE on both rows.

Cross-DB semantics

DB	Default isolation
Postgres	Read Committed (SI on request)
MySQL InnoDB	Repeatable Read (non-strict)
Oracle	Read Committed (SI on request)
SQL Server	Read Committed (snapshot opt)
CockroachDB	Serializable (always)
Spanner	External consistency (Serializable)

API discipline

• Wrap business invariants in DB constraints (FKs, CHECK, UNIQUE).
• Use SELECT ... FOR UPDATE for read-then-write under RC.
• Retry on serialization failure (40001) — every modern driver supports this.

Refs

• Berenson et al.: "A Critique of ANSI SQL Isolation Levels" (SIGMOD '95).
• Postgres concurrency control chapter.
• CockroachDB engineering blog on SSI.
• Jepsen analyses (PostgreSQL, MySQL, MongoDB) on jepsen.io.