Isolation AnomalyStep through the failure
Press Next to advance one statement at a time. The score shows who did what and when; the panels below show what the engine held at that exact moment. Change the engine or the isolation level and the same steps re-run instantly.
What to run
Runs as: READ COMMITTED · PostgreSQL 16
Step 0, T1: begin.
- Key
- 1 Dr A
- 2 Dr B
- Value
- 0 off call
- 1 on call
Nothing has gone wrong yet
PostgreSQL 16 · READ COMMITTEDNothing in the published definitions has happened up to this step. Keep stepping — an anomaly is something that becomes true at a particular moment, and the mark will appear on the step where it does.
How do I read this?Hide the key
How to read the score
Each horizontal line is one transaction — one database session, issuing its statements left to right. Everything in the same vertical column happened at the same point in the run, so reading straight down tells you what the two sessions were doing to each other.
- One line per transaction
- Each transaction gets its own colour and its own marker shape, so the two never depend on colour alone to be told apart.
- Hollow marker — a read
- The value it saw is printed above the marker. That value is the whole story in most anomalies.
- Filled marker — a write
- An insert, update or delete. The row it touched is named underneath.
- One bar line — commit
- The transaction finished and its work became permanent and visible to everyone.
- Two bar lines — rollback
- The transaction was undone, either because it asked to be or because the engine killed it.
- Dashed arc — a wait
- The statement could not proceed and blocked on a lock. The arc lands on the step that finally released it.
- Bracket under a stave — how long a view is frozen
- The span of that transaction’s snapshot: taken once, at the step it starts on, and used for every read until the transaction ends. Levels that take a fresh snapshot for each statement have no span to draw, which is the difference between REPEATABLE READ and READ COMMITTED in one picture.
- Dashed ring on the bracket — a commit you cannot see
- Another transaction committed inside your span. It succeeded and it is in the table, and this transaction keeps reading the older version anyway — not a bug, but the promise the level made.
- Solid line with a triangle — where you are
- The step currently shown in the panels below. Stepping moves it.
- Red bracket — the moment it went wrong
- It sits above the step where the anomaly became unavoidable. Red is used for nothing else on this site.
- The labels, e.g. w1[x]
- Standard schedule shorthand: r is a read, w a write, c a commit, a an abort. The digit is the transaction and the letter in brackets is the row.
Drag a mark sideways to re-interleave and re-run. A mark can only move between its own transaction’s neighbouring operations — a session issues its statements in order, so the interleaving is the only thing you get to choose. The left and right arrow keys step too.
About this schedule
The on-call roster empties
The situation: Two doctors are on call and at least one must remain. Each opens the roster, sees that the other is on call, and takes themselves off. Both commit.
What it teaches: This is the anomaly that is not in the ANSI list. PostgreSQL’s REPEATABLE READ is snapshot isolation, and permits it: both transactions commit and nobody is on call. Only SERIALIZABLE catches it, and it does so by aborting the second transaction to commit with a read/write dependency failure — not by blocking. MySQL InnoDB permits it at REPEATABLE READ as well, and at SERIALIZABLE it does not detect anything — it deadlocks, and one transaction is rolled back with 1213. SQL Server permits it at SNAPSHOT, which is the same anomaly under a name that at least admits what the level is; at REPEATABLE READ and SERIALIZABLE its shared locks turn the schedule into a deadlock, and because SQL Server picks its victim by cost estimate this model refuses to say which transaction loses. And Oracle settles the argument: it permits this at the level called SERIALIZABLE, because Oracle’s SERIALIZABLE is snapshot isolation. Both doctors go off call, both commit, and no error is raised — at the strongest level name the standard has.
Inside the engine at this step
The state of the database at the exact statement above — not the end of the run. Step backwards and forwards to watch these change.
Version chains
Every write creates a version rather than replacing one. xmin is the transaction that created it, xmax the transaction that superseded or deleted it.
The test: you see a version if the transaction in xmin had committed when your snapshot was taken, and xmax is either empty or names a transaction that had not. Walk the versions newest first and take the first one that passes.
The rule, in the vendor’s words
“When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed by concurrent transactions during the query's execution. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run.”
| Value | xmin | xmax | State of this version |
|---|---|---|---|
| 1 | initial | — | live |
| Value | xmin | xmax | State of this version |
|---|---|---|---|
| 1 | initial | — | live |