Why Anomalies Have Names
The SQL standard defines isolation levels by which read phenomena they forbid. Knowing the three phenomena lets you reason about any level instead of memorizing a table.
The Three Phenomena
- Dirty read happens when a transaction reads data another transaction has written but not yet committed. If the writer rolls back, you read a value that never officially existed.
- Non repeatable read happens when a transaction reads the same row twice and gets different values because another transaction committed an update in between.
- Phantom read happens when a transaction runs the same range query twice and a new row appears or disappears because another transaction committed an insert or delete.
How Levels Map
- Read Uncommitted allows all three.
- Read Committed forbids dirty reads.
- Repeatable Read also forbids non repeatable reads.
- Serializable forbids all three including phantoms.
The key distinction is that non repeatable reads concern a single row while phantoms concern a set of rows matching a predicate.
Key idea
Isolation levels are defined by which read phenomena they prevent, climbing from dirty reads to non repeatable reads to phantoms.