The Runtime Theory

Isolation Levels: When Snapshots Are Taken and Held

See how READ COMMITTED and REPEATABLE READ differ: when the snapshot is taken, what each statement sees, and where locking and conflicts happen in a transaction.

The Runtime Theory Team08 stages

trace / request.md

TRANSACTIONBEGINSSNAPSHOTTIMING DIFFERSREADS APPLYVISIBILITY RULESWRITES TAKEROW LOCKSWAIT FOR THELOCK HOLDERCOMMIT PUBLISHESCHANGESROLLBACK ERASESEVERYTHINGANOMALIESREVEAL THE LEVEL

readyThe transaction starts and receives an ID. READ COMMITTED defers snapshot creation until the first statement; REPEATABLE READ immediately records the snapshot — the set of in-flight transactions at this instant.

Isolation levels are not abstract ratings; they are a single concrete question: when does this transaction take its snapshot, and how long does it hold it? This diagram puts that question at the center. In READ COMMITTED, every statement takes a new snapshot at its start — which is why a row read twice in one transaction can change between reads. In REPEATABLE READ, the snapshot is captured at BEGIN and pinned for the whole transaction — which is why reads are stable but also why a transaction can produce write conflicts it never sees coming.

The read path in both levels is lock-free: visibility is decided by comparing tuple version markers against the snapshot. The write path is where locking happens — row locks serialize concurrent writers, and waits can deadlock. The final stage shows the payoff: which anomalies each level tolerates. Choosing a level is choosing which of those stages behaves differently.