Isolation AnomalyA library of failures
Each classic anomaly as a runnable schedule, with the framing that makes the stakes obvious and the levels that permit it. Each strip shows the documented levels, one row per engine.
- Start hereWrite skewA5B · not in the ANSI SQL-92 list
The on-call roster empties
write-skewTwo 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.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: Permitted at SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: Permitted at SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: Permitted at SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: Permitted at SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: Permitted at REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: Permitted at Open in the labWhat 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.
- Dirty readP1 · in the ANSI SQL-92 list
Reading a value that was rolled back
dirty-readA 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.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: Permitted at READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: Permitted at READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: Permitted at READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: clean REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: clean Open in the labWhat 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.
- Dirty writeP0 · not in the ANSI SQL-92 list
Two transactions writing the same row before either ends
dirty-writeTwo administrators edit the same setting at the same time. The first one’s edit is still uncommitted when the second one writes.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: clean REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: clean Open in the labWhat 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.
- Non-repeatable readP2 · in the ANSI SQL-92 list
The same row, read twice, with two different values
non-repeatable-readA 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.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: Permitted at REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: clean Open in the labWhat 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.
- Read skewA5A · not in the ANSI SQL-92 list
A transfer seen half-finished
read-skewAccounts 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.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: Permitted at REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: clean Open in the labWhat 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.
- Lost updateP4 · not in the ANSI SQL-92 list
Two stock decrements become one
lost-updateTen 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.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: Permitted at SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: Permitted at REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: clean Open in the labWhat 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.
- Lost updateP4 · not in the ANSI SQL-92 list
The same two decrements, with the row locked
lost-update-lockedThe same two orders, but each reads the stock level with SELECT ... FOR UPDATE before writing it back.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: clean REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: clean Open in the labWhat 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.
- Phantom readP3 · in the ANSI SQL-92 list
A booking that appears inside one transaction
phantom-readSlots 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.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: Permitted at SNAPSHOT: clean SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: Permitted at SNAPSHOT: clean SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: Permitted at REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: clean Open in the labWhat 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.
- Write skewA5B · not in the ANSI SQL-92 list
The same roster, read with FOR UPDATE
write-skew-lockedThe same two doctors, but each reads both rows with SELECT ... FOR UPDATE before writing, which is the usual advice when you cannot use SERIALIZABLE.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: clean REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: clean Open in the labWhat 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.
- Write skewA5B · not in the ANSI SQL-92 list
Two bookings for a calendar that was empty when both looked
phantom-insert-raceNobody has booked slots 1 to 5. Two people each check that the range is empty, and each book a different slot in it.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: Permitted at SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: Permitted at SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: Permitted at SNAPSHOT: Permitted at SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: Permitted at READ COMMITTED: Permitted at REPEATABLE READ: Permitted at SNAPSHOT: Permitted at SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: Permitted at REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: Permitted at Open in the labWhat 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.
- Documents an engine’s response rather than an anomaly
Two transfers that lock the same accounts in opposite order
deadlockTwo 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.
Permitted at
RU RC RR SI SER PostgreSQL 16 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean MySQL InnoDB 8.4 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: level not offered SERIALIZABLE: clean SQL Server 2022 READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean SQL Server 2022 · RCSI READ UNCOMMITTED: clean READ COMMITTED: clean REPEATABLE READ: clean SNAPSHOT: clean SERIALIZABLE: clean Oracle 23ai READ UNCOMMITTED: level not offered READ COMMITTED: clean REPEATABLE READ: level not offered SNAPSHOT: level not offered SERIALIZABLE: clean Open in the labWhat 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.