Two people try to book the last seat on a flight at the same moment. A database that runs both bookings at once must decide what each person is allowed to see while the other is working. Let them see everything and you get nonsense. Make them wait for each other and the system crawls. Isolation levels are the dial between those two extremes.
The SQL standard defined four levels by listing bad things, called phenomena, that each level must prevent. In 1995 a group that included Jim Gray, a database pioneer, and Phil Bernstein wrote the paper that took that idea apart. They showed that the standard's definitions were ambiguous and incomplete, and that popular real systems did things the standard could not describe.
The paper argues that the three phenomena in the SQL standard are ambiguous and not enough to define isolation levels. It adds new ones, such as dirty writes, lost updates, read skew and write skew. It also defines snapshot isolation, a popular level that avoids the standard's anomalies but is not serializable, because write skew can still happen.
The bad things a level must prevent
The standard named three phenomena. Each is a pattern where one transaction sees or affects another's work in an unwelcome way:
- Dirty read (P1). Reading a value another transaction has changed but not yet committed. If it rolls back, you read something that never existed.
- Non-repeatable read (P2). Reading the same item twice and getting different values, because another transaction committed a change in between.
- Phantom (P3). Running the same search twice and seeing different rows, because another transaction added a matching row.
The paper shows two problems. First, the English wording of each phenomenon can be read in a strict way or a broad way, and only the broad reading rules out enough. Second, even the broad reading misses important problems. The paper adds dirty write (P0), lost update (P4), read skew (A5A) and write skew (A5B).
