Snapshot isolation

The one idea

A snapshot = a captured set of committed XIDs (from 0004). An isolation level just chooses when that snapshot is taken and how long it's held. Same machinery throughout.

Dirty reads are impossible at every level (Read Uncommitted = Read Committed).

The three levels

Read Committed (default): fresh snapshot at the start of each statement → two SELECTs in one txn can disagree; conflicting writes re-read the latest version.

Repeatable Read (snapshot isolation): one snapshot at the txn's first statement → stable reads, no phantoms; write conflict ⇒ error, retry.

Serializable: RR + SSI watchdog on read/write dependencies → also prevents write skew; anomaly ⇒ error, retry.

The two errors (both SQLSTATE 40001)

RR write conflict:
could not serialize access due to concurrent update

Serializable anomaly:
could not serialize access due to read/write dependencies among transactions

→ One retry loop on 40001 handles both. Mandatory if you use RR/Serializable.

Write skew (what SSI catches)

Two txns each read an overlapping set, each checks a rule that holds, each writes — fine alone, impossible in any serial order. RR allows it; Serializable rolls one back.

Postgres vs MySQL

Aspect	MySQL	Postgres
Default	Repeatable Read	Read Committed
RR phantoms	gap locks	pure snapshot
RR conflict	blocks	40001 retry
Write skew	allowed at RR	Serializable stops it
Style	pessimistic	optimistic

Migration trap: default silently drops RR→RC; RR/Serializable need retry loops.