A Critique of ANSI SQL Isolation Levels

Who should read it

Read it when isolation levels should stop being a table to memorize: it shows why the ANSI definitions based on forbidden phenomena are ambiguous, adds dirty write and lost update, and gives the first careful definition of snapshot isolation, including write skew. It is short and pays off for database research, for distributed-systems work that reasons about consistency models, and for senior backend interviews. It is not needed if you only have to pick an isolation level for an application: the PostgreSQL isolation page and the transactions chapter of DDIA cover that practical ground faster.

Study objectives

• Explain the difference between the broad (P) and strict (A) interpretations of dirty read, fuzzy read and phantom, and why the strict reading is too permissive.
• Explain why dirty write (P0) has to be excluded at every isolation level.
• Map each ANSI level to a locking implementation in terms of lock duration (short versus long) and predicate locks.
• Define snapshot isolation with first-committer-wins and show why it prevents lost update and read skew but allows write skew.
• Place READ COMMITTED, Cursor Stability, REPEATABLE READ, Snapshot Isolation and SERIALIZABLE in the paper’s partial order and justify each edge with a history.

Prerequisites

• Transactions, isolation anomalies and MVCC

Main concepts to understand

• Histories as sequences of reads, writes, commits and aborts; phenomena as forbidden patterns within them.
• Broad versus strict interpretations of the three ANSI phenomena.
• Dirty write (P0) and lost update (P4) as phenomena the ANSI text did not name.
• Locking isolation levels characterized by how long read, write and predicate locks are held.
• Cursor Stability as a level between READ COMMITTED and REPEATABLE READ.
• Snapshot isolation: reads from a start-time snapshot, first-committer-wins on write conflicts.
• Read skew (A5A) and write skew (A5B) as constraint-violation anomalies.
• Isolation levels form a partial order; snapshot isolation and locking REPEATABLE READ are incomparable.

What to inspect in the evaluation

• There are no experiments: evaluate each claim by constructing small histories yourself and checking them against the definitions.
• For every stated relationship (stronger, weaker, incomparable), find the witness history that separates the two levels.
• Check which conclusions assume single-version locking schedulers and which carry over to multi-version implementations.
• Compare the paper’s definitions with a current system’s documentation (for example PostgreSQL’s isolation table) and note where level names and guarantees diverge.

Guarantees versus empirical results

Reading plan

1. Pass 1 (20 minutes): introduction, the first table of ANSI levels, the final summary table and the diagram of level relationships; write down the paper’s three main complaints about the standard.
2. Pass 2 (50 minutes): Sections 2 and 3 with pen and paper; rewrite each phenomenon as a two-transaction history and check where the broad and strict forms disagree.
3. Pass 3 (50 minutes): Section 4 on Cursor Stability, snapshot isolation and other multi-version systems; then reproduce write skew under PostgreSQL REPEATABLE READ and confirm that SERIALIZABLE rejects it.

Discussion prompts

• Why is defining isolation by forbidden phenomena fragile, and what does a dependency-graph definition (as in Adya’s later work) offer instead?
• Some systems give snapshot isolation a different level name. What can application developers get wrong as a result?
• Describe an invariant in a typical web application that write skew could break, and two different ways to protect it.
• Why did serializable snapshot isolation have to be invented later, and what information does it track that plain snapshot isolation ignores?

Related topics

• Consistency models and isolation guarantees
• Transactions, isolation anomalies and MVCC
• Concurrency control research and isolation correctness
• Database paper route: CMU courses to Spanner