A Critique of ANSI SQL Isolation Levels
The SQL standard's isolation levels are ambiguous and incomplete; read broadly they are locking in disguise, and the snapshot level many products ship is not serializable.
Read the original ↗Six authors, three of them from Microsoft and one the editor of the SQL-92 standard itself, read the standard's definition of isolation and found it did not say what everyone believed it said. Then, almost in passing, they named the isolation level most databases now run by default and proved it was not serializable.
Included with a plan
This discussion is included with a plan. The first three lessons of every track are free, with their boards and videos. A plan unlocks every lesson, board, video, lab and paper for one price.
Related on chalk
The same idea as a board, lab, video or lesson.
- BoardLocks, Latches & Transactionsthe locks the paper shows the standard's phenomena turn into once their ambiguity is fixed
- BoardMVCC & Vacuumthe snapshot each transaction reads from, which is the paper's Snapshot Isolation as Postgres builds it
- BoardTransactionsdirty reads, lost updates, write skew and phantoms: this paper's anomalies, drawn
- BoardTransactions (MVCC)snapshot reads built by hand: the read half of the paper's Snapshot Isolation, with first-committer-wins left out
- LabIsolation levelsmove the level and watch each of the paper's anomalies appear and vanish
- LabThe vacuum lagthe bill for the paper's time travel: the oldest open snapshot is what holds back cleanup
- LessonLocks, Latches & TransactionsUses the board Locks, Latches & Transactions
- LessonThe Database That Won't AnswerUses the board Locks, Latches & Transactions
- LessonBuild a lock manager and deadlock detectorUses the board Locks, Latches & Transactions
- LessonMVCC & VacuumUses the board MVCC & Vacuum
- LessonTransactionsUses the board Transactions
- LessonTransactions (MVCC)Uses the board Transactions (MVCC)
Further reading
Read the original for the full argument and its evidence.
- The paper (Microsoft Research technical report, PDF) ↗MSR-TR-95-51, the SIGMOD 1995 paper (pages 1-10). Section 3 repairs the standard; section 4.2 defines Snapshot Isolation; Figure 2 and Table 4 are the whole result on one page.
- PostgreSQL: Transaction Isolation ↗Later work: the standard's table with a serialization anomaly column added, and a citation of this paper for what the table leaves out.
- Hermitage (Kleppmann, 2014) ↗Later work: the paper's histories turned into tests and run against real databases, with a column for what each level is called and one for what it is.
- Serializable Snapshot Isolation in PostgreSQL (Ports and Grittner, VLDB 2012) ↗Later work: how the snapshot level this paper proved non-serializable was made serializable in a production database.