A critique of ANSI SQL isolation levels

  • Authors:
  • Hal Berenson;Phil Bernstein;Jim Gray;Jim Melton;Elizabeth O'Neil;Patrick O'Neil

  • Affiliations:
  • Microsoft Corp.;Microsoft Corp.;U.C. Berkeley;Sybase Corp.;UMss/Boston;-

  • Venue:
  • SIGMOD '95 Proceedings of the 1995 ACM SIGMOD international conference on Management of data
  • Year:
  • 1995

Quantified Score

Hi-index 0.02

Visualization

Abstract

ANSI SQL-92 [MS, ANSI] defines Isolation Levels in terms of phenomena: Dirty Reads, Non-Repeatable Reads, and Phantoms. This paper shows that these phenomena and the ANSI SQL definitions fail to properly characterize several popular isolation levels, including the standard locking implementations of the levels covered. Ambiguity in the statement of the phenomena is investigated and a more formal statement is arrived at; in addition new phenomena that better characterize isolation types are introduced. Finally, an important multiversion isolation type, called Snapshot Isolation, is defined.