Skip to content
A Critique of ANSI SQL Isolation Levels 4 min

Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil, Patrick O'Neil · ACM SIGMOD 1995

A Critique of ANSI SQL Isolation Levels

The 1995 paper that showed the SQL standard's isolation levels were ambiguous, and defined snapshot isolation. Replay anomalies at each level.

Read the original paper

Databases · Black Belt · Pro · 4 min read

A Critique of ANSI SQL Isolation Levels

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).

Based on Table 4 of the paper. Stronger levels forbid more anomalies. Snapshot isolation sits near the top, but one anomaly, write skew, still slips through. Only the serializable level forbids all of them.

Keep reading with Pro

This paper is part of Pro. Pro and Architect open the full breakdown, with animated diagrams, interactive elements you can play with and a quiz. A few papers stay free to read in full.

Still to read

2 more sections, 1 more diagram and 2 interactive elements to play with

  • Snapshot isolation: popular, and not serializable
  • What to take away as a developer

Found this breakdown useful?

Share it with someone else wrestling with this paper.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!

Enjoyed this? Get more like it.

New paper breakdowns, levels, and one concept worth knowing, straight to your inbox.

No spam, ever. Unsubscribe in one click.

Enjoyed this content?

Your support keeps us creating free resources

We put a lot of hours into researching and writing these guides. If it helped you, consider buying us a coffee. Every bit goes toward keeping ScaleDojo's content free and growing.

$

One-time payment via Stripe. ScaleDojo account required.