The anomaly catalog
Every isolation anomaly with a formal name. The codes (G0, G1a, …) come from Adya's dependency-graph formalism, popularized by Hermitage, Martin Kleppmann's cross-database isolation test suite. This reference retains the named cases but does not claim complete Hermitage execution coverage; the engine catalogs state unexecuted variants explicitly.
| Code | Anomaly | In one line |
|---|---|---|
| G0 | Dirty write | two uncommitted transactions interleave writes to the same rows |
| G1a | Dirty read | reading data that is later rolled back |
| G1b | Intermediate read | reading a draft the writer later overwrites |
| G1c | Circular information flow | two transactions each read the other's uncommitted writes |
| OTV | Observed transaction vanishes | a committed transaction is visible in pieces |
| P2 | Non-repeatable read | the same row read twice gives two answers |
| G-single | Read skew | a multi-row combination that existed at no point in time |
| PMP | Phantom read | the same range query returns new rows |
| P4 | Lost update | read-modify-write silently overwrites a concurrent update |
| G2-item / G2 | Write skew | two transactions jointly break an invariant each one checked |
| — | Read-only anomaly | even a pure report can observe an impossible state |
Which isolation level stops which anomaly is an engine answer, not a standard answer. The per-engine answer sheets distinguish D (asserted schedule), M (Documented contract), † (explained Entailed guarantee), and unexecuted cells: PostgreSQL's answers · MySQL's answers.
What the standard's table doesn't tell you
The SQL standard's three-anomaly table (dirty / non-repeatable / phantom, see isolation levels) dates from 1992. Lost updates, write skew, and the read-only anomaly were formalized later¹, and they're the ones that actually bite in modern applications, because they emerge from application logic (read, decide, write) rather than from raw statement visibility.
¹ Berenson et al., A Critique of ANSI SQL Isolation Levels (1995); Adya, Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions (MIT PhD thesis, 1999), the source of the G-codes; Fekete et al., Making Snapshot Isolation Serializable (ACM TODS, 2005).
The obscure ones
The five anomalies below rarely make it into blog posts, because on most databases at most levels they can't happen. They're worth knowing precisely because where they can happen tells you what a level is really made of.
Dirty write (G0)
Overwriting another transaction's uncommitted target is the dirty-write event. Interleaving statements or a mixed final state alone is not its definition. The displayed UPDATE waits are demonstrated at the catalogs' stated levels. † The all-level exclusion follows from their documented exclusive target locks, which require the first writer to end before a conflicting overwrite. No Transcript executes every level or excludes every read/write cycle. See it proven: PostgreSQL · MySQL.
Intermediate read (G1b)
Reading a draft: the writer changes a value twice, and you catch the version that no committed history ever contained. Impossible from READ COMMITTED up on both engines, but MySQL's READ UNCOMMITTED really serves it. See it: PostgreSQL · MySQL.
Circular information flow (G1c)
Two concurrent transactions each read the other's uncommitted writes, an information exchange no serial order could explain. See it: PostgreSQL · MySQL.
Observed transaction vanishes (OTV)
A committed transaction should be visible as a whole or not at all. Under dirty reads it can show through in pieces: one row already overwritten by an uncommitted successor, the other still visible. See it: PostgreSQL · MySQL.
The read-only anomaly
A transaction that only reads can observe a result with no compatible serial ordering of the committed participating transactions. The PostgreSQL demonstration models a report with SELECT results, not printing or publication. SERIALIZABLE supplies documented serial equivalence; other suitably designed coordination can also protect a particular application rule. MySQL has no corresponding read-only Scenario catalogued here. See it: PostgreSQL.
Further reading
- Hermitage: runnable isolation tests for PostgreSQL, MySQL, Oracle, and more; it is a separate suite, not claimed full execution coverage here
- PostgreSQL's anomaly catalog · MySQL's anomaly catalog: same anomalies, meaningfully different answers