Scenarios

Each classic anomaly as a runnable schedule, with the framing that makes the stakes obvious and the levels that permit it.

Reading a value that was rolled back
dirty-read
Documents: Dirty read P1 in the ANSI SQL-92 list
The situation: A refund is being processed. The refund transaction has already reduced the balance to 0 when a reporting query reads it, and then the refund fails and rolls back.
What it teaches: ANSI defines READ UNCOMMITTED as the level that permits this. PostgreSQL accepts the name and gives you READ COMMITTED, so the reporting query sees 100 at every level — the balance that was actually committed. MySQL InnoDB is the contrast: its READ UNCOMMITTED is real, and the reporting query there reads 0 — a balance that never existed. SQL Server’s READ UNCOMMITTED is real too, and it reads 0 for the same reason. Oracle refuses the level name outright: it has no READ UNCOMMITTED and never permits a dirty read at all.
Open in the score
```
 0  T1  b1
 1  T2  b2
 2  T1  w1[1=0]
 3  T2  r2[1]
 4  T1  a1
 5  T2  r2[1]
 6  T2  c2
```
Permitted at
PostgreSQL 16
not permitted at any level of this engine
MySQL InnoDB 8.4
RU
Microsoft SQL Server 2022
RU
Microsoft SQL Server 2022
RU
Oracle Database 23ai Free
not permitted at any level of this engine
Two transactions writing the same row before either ends
dirty-write
Documents: Dirty write P0 not in the ANSI SQL-92 list
The situation: Two administrators edit the same setting at the same time. The first one’s edit is still uncommitted when the second one writes.
What it teaches: The second write waits for the first transaction to end rather than overwriting an uncommitted value — first updater wins. Because the first transaction rolls back, the second proceeds from the original row.
Open in the score
```
 0  T1  b1
 1  T2  b2
 2  T1  w1[1=10]
 3  T2  w2[1=20]
 4  T1  a1
 5  T2  c2
```
Permitted at
PostgreSQL 16
not permitted at any level of this engine
MySQL InnoDB 8.4
not permitted at any level of this engine
Microsoft SQL Server 2022
not permitted at any level of this engine
Microsoft SQL Server 2022
not permitted at any level of this engine
Oracle Database 23ai Free
not permitted at any level of this engine
The same row, read twice, with two different values
non-repeatable-read
Documents: Non-repeatable read P2 in the ANSI SQL-92 list
The situation: A checkout re-reads the price of an item to compute tax after having read it to compute the subtotal. Between the two reads, a price update commits.
What it teaches: At READ COMMITTED every statement takes a fresh snapshot, so the two reads legitimately disagree. REPEATABLE READ takes one snapshot for the transaction and both reads return 100.
Open in the score
```
 0  T1  b1
 1  T1  r1[1]
 2  T2  b2
 3  T2  w2[1=150]
 4  T2  c2
 5  T1  r1[1]
 6  T1  c1
```
Permitted at
PostgreSQL 16
RU, RC
MySQL InnoDB 8.4
RU, RC
Microsoft SQL Server 2022
RU, RC
Microsoft SQL Server 2022
RU, RC
Oracle Database 23ai Free
RC
A transfer seen half-finished
read-skew
Documents: Read skew A5A not in the ANSI SQL-92 list
The situation: Accounts 1 and 2 hold 100 each and the invariant is that they total 200. An audit reads account 1, a transfer of 50 from account 1 to account 2 commits, and then the audit reads account 2.
What it teaches: The audit reads 100 and then 150 and reports a total of 250. Neither read saw uncommitted data and both values were committed — but not at the same time. This is why a report needs one snapshot, not two correct reads.
Open in the score
```
 0  T1  b1
 1  T1  r1[1]
 2  T2  b2
 3  T2  w2[1=50]
 4  T2  w2[2=150]
 5  T2  c2
 6  T1  r1[2]
 7  T1  c1
```
Permitted at
PostgreSQL 16
RU, RC
MySQL InnoDB 8.4
RU, RC
Microsoft SQL Server 2022
RU, RC
Microsoft SQL Server 2022
RU, RC
Oracle Database 23ai Free
RC
Two stock decrements become one
lost-update
Documents: Lost update P4 not in the ANSI SQL-92 list
The situation: Ten units in stock. Two orders each read the stock level, subtract one, and write the result back. Nine units are recorded, and one unit has been sold twice.
What it teaches: Neither transaction did anything wrong on its own, no error was raised, and the count is wrong. READ COMMITTED re-applies the second write to the newly committed version, so it writes 9 over 9. REPEATABLE READ refuses instead, aborting with 40001 — an error your code can retry. MySQL InnoDB permits it at REPEATABLE READ too: its DML always acts on the freshest committed row and never raises a serialization error, so the same schedule that fails loudly on PostgreSQL succeeds quietly there.
Open in the score
```
 0  T1  b1
 1  T2  b2
 2  T1  r1[1]
 3  T2  r2[1]
 4  T1  w1[1=9]
 5  T1  c1
 6  T2  w2[1=9]
 7  T2  c2
```
Permitted at
PostgreSQL 16
RU, RC
MySQL InnoDB 8.4
RU, RC, RR
Microsoft SQL Server 2022
RU, RC
Microsoft SQL Server 2022
RU, RC
Oracle Database 23ai Free
RC
The same two decrements, with the row locked
lost-update-locked
Documents: Lost update P4 not in the ANSI SQL-92 list
The situation: The same two orders, but each reads the stock level with SELECT ... FOR UPDATE before writing it back.
What it teaches: The second order’s locking read waits for the first to commit. At READ COMMITTED it then returns the new value, 9, so the second decrement is computed from what is actually there. At REPEATABLE READ the locking read of a row that changed under the snapshot aborts with 40001 instead — the same protection, delivered as an error.
Open in the score
```
 0  T1  b1
 1  T2  b2
 2  T1  r1[1]•
 3  T2  r2[1]•
 4  T1  w1[1=9]
 5  T1  c1
 6  T2  w2[1=8]
 7  T2  c2
```
Permitted at
PostgreSQL 16
not permitted at any level of this engine
MySQL InnoDB 8.4
not permitted at any level of this engine
Microsoft SQL Server 2022
not permitted at any level of this engine
Microsoft SQL Server 2022
not permitted at any level of this engine
Oracle Database 23ai Free
not permitted at any level of this engine
A booking that appears inside one transaction
phantom-read
Documents: Phantom read P3 in the ANSI SQL-92 list
The situation: Slots 1 and 2 of a five-slot calendar are booked. A report counts the bookings, someone books slot 3 and commits, and the report counts again to render a total.
What it teaches: At READ COMMITTED the second count returns three rows where the first returned two. PostgreSQL’s REPEATABLE READ prevents this — which the SQL standard does not require of that level, and which is exactly why the level name cannot be trusted across engines. SQL Server settles the argument about what the level name means: its REPEATABLE READ holds shared locks on the rows it read but cannot lock a row that does not exist yet, so the phantom appears there and not on PostgreSQL — the same level name, opposite answers, and ANSI permits both.
Open in the score
```
 0  T1  b1
 1  T1  r1[P:1..5]
 2  T2  b2
 3  T2  i2[3=1]
 4  T2  c2
 5  T1  r1[P:1..5]
 6  T1  c1
```
Permitted at
PostgreSQL 16
RU, RC
MySQL InnoDB 8.4
RU, RC
Microsoft SQL Server 2022
RU, RC, RR
Microsoft SQL Server 2022
RU, RC, RR
Oracle Database 23ai Free
RC
The on-call roster empties
write-skew
Documents: Write skew A5B not in the ANSI SQL-92 list
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.
Open in the score
```
 0  T1  b1
 1  T2  b2
 2  T1  r1[P:1..2]
 3  T2  r2[P:1..2]
 4  T1  w1[1=0]
 5  T2  w2[2=0]
 6  T1  c1
 7  T2  c2
```
Permitted at
PostgreSQL 16
RU, RC, RR
MySQL InnoDB 8.4
RU, RC, RR
Microsoft SQL Server 2022
RU, RC, SI
Microsoft SQL Server 2022
RU, RC, SI
Oracle Database 23ai Free
RC, SER
The same roster, read with FOR UPDATE
write-skew-locked
Documents: Write skew A5B not in the ANSI SQL-92 list
The situation: The same two doctors, but each reads both rows with SELECT ... FOR UPDATE before writing, which is the usual advice when you cannot use SERIALIZABLE.
What it teaches: The locks serialize the two transactions. The second doctor’s locking read waits and then returns a roster in which the first doctor is already off call, so this schedule is equivalent to running T1 and then T2 — there is no anomaly left for the database to permit. The roster still empties, because this schedule writes without re-checking what the locking read returned; that remaining bug is the application’s, and it is now visible in the value read rather than hidden. At REPEATABLE READ the locking read aborts with 40001 instead.
Open in the score
```
 0  T1  b1
 1  T2  b2
 2  T1  r1[1]•
 3  T1  r1[2]•
 4  T2  r2[1]•
 5  T1  w1[1=0]
 6  T1  c1
 7  T2  r2[2]•
 8  T2  w2[2=0]
 9  T2  c2
```
Permitted at
PostgreSQL 16
not permitted at any level of this engine
MySQL InnoDB 8.4
not permitted at any level of this engine
Microsoft SQL Server 2022
not permitted at any level of this engine
Microsoft SQL Server 2022
not permitted at any level of this engine
Oracle Database 23ai Free
not permitted at any level of this engine
Two bookings for a calendar that was empty when both looked
phantom-insert-race
Documents: Write skew A5B not in the ANSI SQL-92 list
The situation: Nobody has booked slots 1 to 5. Two people each check that the range is empty, and each book a different slot in it.
What it teaches: Both range reads return nothing, and both inserts succeed, so the calendar ends with two bookings where each booker believed there would be one. No row was written twice, so nothing conflicts — this is write skew wearing a phantom’s clothes, and only SERIALIZABLE stops it.
Open in the score
```
 0  T1  b1
 1  T2  b2
 2  T1  r1[P:1..5]
 3  T2  r2[P:1..5]
 4  T1  i1[1=1]
 5  T2  i2[2=1]
 6  T1  c1
 7  T2  c2
```
Permitted at
PostgreSQL 16
RU, RC, RR
MySQL InnoDB 8.4
RU, RC, RR
Microsoft SQL Server 2022
RU, RC, RR, SI
Microsoft SQL Server 2022
RU, RC, RR, SI
Oracle Database 23ai Free
RC, SER
Two transfers that lock the same accounts in opposite order
deadlock
Documents an engine’s response rather than an anomaly
The situation: Two transfers run at once. One moves money from account 1 to account 2 and locks them in that order; the other moves money from 2 to 1 and locks them the other way round. Each holds what the other needs next.
What it teaches: Neither transaction did anything unusual, and no isolation level prevents this — locking in a consistent order is the application’s job. What differs is the engine’s answer. PostgreSQL and MySQL InnoDB roll a transaction back and name it, so the other proceeds. SQL Server chooses its victim by internal cost estimate and Oracle says plainly that either session could get the error, so for those three this model refuses to say who loses rather than inventing it — and Oracle would roll back only the statement in any case, leaving the transaction open.
Open in the score
```
 0  T1  b1
 1  T2  b2
 2  T1  r1[1]•
 3  T2  r2[2]•
 4  T1  r1[2]•
 5  T2  r2[1]•
 6  T1  c1
 7  T2  c2
```
Permitted at
PostgreSQL 16
not permitted at any level of this engine
MySQL InnoDB 8.4
not permitted at any level of this engine
Microsoft SQL Server 2022
not permitted at any level of this engine
Microsoft SQL Server 2022
not permitted at any level of this engine
Oracle Database 23ai Free
not permitted at any level of this engine

Scenarios

Reading a value that was rolled back

Permitted at

Two transactions writing the same row before either ends

Permitted at

The same row, read twice, with two different values

Permitted at

A transfer seen half-finished

Permitted at

Two stock decrements become one

Permitted at

The same two decrements, with the row locked

Permitted at

A booking that appears inside one transaction

Permitted at

The on-call roster empties

Permitted at

The same roster, read with FOR UPDATE

Permitted at

Two bookings for a calendar that was empty when both looked

Permitted at

Two transfers that lock the same accounts in opposite order

Permitted at