Transaction isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE: Visual walkthrough

Every figure here uses the same convention: time runs left to right, each transaction is a horizontal lane, and the one red thing in each picture is the object that matters — the bad read, the lock, the snapshot line.

Step 1 — The stage: two lanes on a timeline

WHAT. Before any anomaly exists we need a way to draw concurrency. We put two transactions — call them $T_{1}$ and $T_{2}$ — on two horizontal lanes. A dot on a lane is an operation at a moment in time.

WHY. An anomaly is never a property of one operation. It is a property of the order in which operations from different lanes interleave. To reason about "order" we must first be able to see it, so we draw a timeline. The word we are earning here is interleaving: the two lanes' operations are shuffled together onto one shared clock.

PICTURE. Two lanes. $T_{1}$ does a read (R) then another read (R). $T_{2}$ slips a write (W) in between $T_{1}$ 's two reads. That "in between" is where all the trouble lives.

Figure — Transaction isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

Step 2 — The gold standard we are trying to fake: serial order

WHAT. Define the thing every isolation level is measured against. A serial execution is one where transactions run one whole lane at a time — $T_{1}$ fully finishes, then $T_{2}$ starts. No interleaving at all.

WHY. A serial run can never surprise you: each transaction saw a database that only itself was touching. So "serial" = "obviously correct". The goal of isolation is to run interleaved (fast) but produce a result equal to some serial order (correct). That target has a name:

PICTURE. Two clean serial timelines side by side (no overlap). These are our "correct answers". Everything in later steps is judged by "does the messy interleaving land on one of these?"

Step 3 — Anomaly #1 appears: the dirty read

WHAT. Let $T_{2}$ write row $x$ but not commit yet. In the gap before $T_{2}$ decides, $T_{1}$ reads $x$ — and sees $T_{2}$ 's uncommitted value. Then $T_{2}$ aborts ( $A_{2}$ ). Now $T_{1}$ is holding a value that, officially, never existed.

WHY. Why is this the first anomaly, the worst one? Because it breaks the most basic promise: that data you read is real. The read $R_{1} (x)$ happened at a moment when $x$ 's value was a guess $T_{2}$ later withdrew. The red arrow in the figure points from $T_{2}$ 's uncommitted write straight into $T_{1}$ 's read — that arrow is the dirty read.

PICTURE. Watch the sequence on the timeline:

W_{2} (x) \to reads the guess R_{1} (x) \to guess withdrawn A_{2}

$W_{2} (x)$ — $T_{2}$ writes a tentative value, no commit behind it.
$R_{1} (x)$ — $T_{1}$ reads it anyway. This is the dirty read.
$A_{2}$ — $T_{2}$ rolls back, so the value $T_{1}$ read is now provably fictional.

Step 4 — The first rule: only read committed data

WHAT. Add exactly one rule: a read may only see values from transactions that have already committed. Now $R_{1} (x)$ in Step 3 is forbidden from seeing $T_{2}$ 's uncommitted write — it either waits, or reads the last committed value instead.

WHY. This single rule surgically removes the dirty read and nothing else. That is what defines the level named READ COMMITTED. In the picture, the red barrier is a wall in front of $T_{2}$ 's uncommitted write: $T_{1}$ 's read bounces off it and grabs the older committed value behind it.

PICTURE. Same timeline, but a red "committed-only" wall now blocks the dangerous arrow. $R_{1}$ reaches past the uncommitted write to the last committed value.

Step 5 — Anomaly #2 appears: the non-repeatable read

WHAT. Now everyone obeys "read committed". $T_{1}$ reads $x$ and gets $100$ . Then $T_{2}$ commits an update $x = 50$ . Then $T_{1}$ reads $x$ again — and gets $50$ . Same query, same row, two different answers.

WHY. READ COMMITTED gives each statement a fresh look at the committed world. Between $T_{1}$ 's two reads, the committed world legitimately changed. Nothing dirty happened — $T_{2}$ committed for real — yet $T_{1}$ is now inconsistent with itself. The red object here is $T_{1}$ 's own row $x$ changing value under it.

PICTURE.

first look R_{1} (x) = 100 \to committed change W_{2} (x), C_{2} \to same row, new value R_{1} (x) = 50

The two red dots are the same row read twice; the value between them flips. That flip is the non-repeatable read.

Step 6 — The second rule: freeze one snapshot for the whole transaction

WHAT. Add a rule: the first read defines a snapshot, and every later read in the same transaction reuses that same snapshot. In MVCC engines this is literally a stored version-timestamp; in lock-based engines it is a read-lock held until commit so nobody can change the row you read.

WHY. If $T_{1}$ reads from a photograph taken at its start, then $T_{2}$ 's later committed update is invisible to $T_{1}$ — the photo doesn't update. Both of $T_{1}$ 's reads return $100$ . This defines REPEATABLE READ. The red line in the figure is the snapshot line: everything $T_{1}$ reads is pulled from that instant, not from "now".

PICTURE. A vertical red snapshot line at $T_{1}$ 's start. Both reads reach back to that line, so $T_{2}$ 's later write can't touch them.

Step 7 — The last anomaly: the phantom read

WHAT. $T_{1}$ asks a predicate question: SELECT count(*) WHERE age>30 → gets $5$ . $T_{2}$ inserts a brand-new row with age=40 and commits. $T_{1}$ re-runs the exact same query → gets $6$ . A row that did not exist when $T_{1}$ started now matches its condition.

WHY. Here is the subtle crack. REPEATABLE READ froze the rows $T_{1}$ read. But you cannot freeze a row that wasn't there to read. The snapshot protects existing rows' values, not the membership of a predicate. The red object is the phantom row — it materializes inside the queried range.

PICTURE. A range on a number line marking age > 30. Existing rows sit frozen (black). A red row drops into the range between $T_{1}$ 's two counts.

membership before count = 5 \to new member INSERT age = 40, C_{2} \to membership after count = 6

Step 8 — The final rule: lock the range, not just the rows

WHAT. Add the last rule: protect the predicate itself. Lock the entire range age > 30 so that no one can insert, update, or delete a row into or out of that range while $T_{1}$ is open. Lock-based engines call this a predicate/range lock (MySQL InnoDB: next-key locks). MVCC engines like PostgreSQL instead use SSI: they run optimistically and abort whichever transaction would have broken serializability.

WHY. With the range fenced, $T_{2}$ 's insert of age=40 is blocked (or $T_{2}$ is later aborted), so $T_{1}$ 's two counts must agree. Now every anomaly is gone and the interleaving is provably equal to some serial order — this is SERIALIZABLE, the level we defined as the goal back in Step 2. The red object is the fence around the whole range.

PICTURE. The same number line, now with a solid red fence around age > 30. The would-be phantom row bounces off the fence.

Step 9 — The degenerate cases (never leave a gap)

WHAT & WHY & PICTURE for each corner the staircase must still cover:

The one-picture summary

Here is the entire derivation compressed: four lanes stacked, each adding one red rule that peels off one anomaly. Read it top-to-bottom and you have rebuilt the parent's table from zero.

Formula The staircase we just built, as a table

Level	rule added	anomaly killed	still open
READ UNCOMMITTED	—	—	dirty, non-rep, phantom
READ COMMITTED	read only committed	dirty	non-rep, phantom
REPEATABLE READ	freeze one snapshot	non-repeatable	phantom
SERIALIZABLE	lock the range	phantom	—

Recall Feynman retelling — the whole walkthrough in plain words

Two people scribble in one shared notebook while a clock ticks left to right. Step 1–2: "Correct" means the notebook ends up looking as if they took turns. Our job: let them scribble at the same time but land on a took-turns result. Step 3: Person 2 pencils a number, Person 1 copies it, Person 2 erases it — Person 1 now owns a number that never existed. That's a dirty read. Step 4: New rule — only copy ink, never pencil. Dirty read gone → READ COMMITTED. Step 5: Person 1 reads a line, Person 2 re-inks it, Person 1 reads the same line again and it changed. That's a non-repeatable read. Step 6: New rule — Person 1 takes a photo at the start and reads from the photo. Old lines can't change under them → REPEATABLE READ. Step 7: Person 1 counts "lines about ages over 30" = 5. Person 2 adds a brand-new line about age 40. Recount = 6. The photo couldn't protect a line that wasn't there yet — a phantom. Step 8: New rule — fence off the whole "age over 30" region so no new line can drop in. Phantom gone → SERIALIZABLE, which is exactly "as if they took turns" from Step 2. Step 9: If they never overlap, or write in totally different parts of the notebook, none of this can happen — trouble only comes from touching the same place at the same time.

Recall Rebuild the whole table from one sentence

Each step down adds one rule and removes one anomaly, in order ::: READ COMMITTED removes dirty (read only committed), REPEATABLE READ removes non-repeatable (freeze a snapshot), SERIALIZABLE removes phantom (lock the range). What geometric difference distinguishes non-repeatable from phantom? ::: Non-repeatable = an existing dot changes height (value flips); phantom = a new dot appears inside the predicate fence (membership changes). Where do ALL anomalies come from? ::: Overlap in time on a shared row or shared predicate range — remove the overlap or disjoin the data and every level behaves correctly.

Flashcards

The rule that turns READ UNCOMMITTED into READ COMMITTED?

Reads may only see committed data.

The rule that turns READ COMMITTED into REPEATABLE READ?

Freeze one snapshot for the whole transaction (or hold read locks to commit).

The rule that turns REPEATABLE READ into SERIALIZABLE?

Lock the predicate range so no phantom can be inserted (or abort under SSI).