A critique of ANSI SQL isolation levels

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.

REFERENCES

• ANSI: ANSI X3.135-1992, American National Standard for Information Systems -- Database Language -- SQL, November, 1992
• ABJ: V. Atluri, E. Bertino, S. Jajodia, "A Theoretical Formulation for Degrees of Isolation in Databases," Technical Report, George Mason University, Fairfax, VA, 1995
• BHG: Philip A. Bernstein , Vassco Hadzilacos , Nathan Goodman, Concurrency control and recovery in database systems, Addison-Wesley Longman Publishing Co., Inc., Boston, MA, 1987
• DAT: C. J. Date, An introduction to database systems: vol. 1 (5th ed.), Addison-Wesley Longman Publishing Co., Inc., Boston, MA, 1990
• DB2: C.J. Date and C. J. White, "A Guide to DB2," Third Edition, Addison-Wesley, 1989.
• EGLT: K. P. Eswaran , J. N. Gray , R. A. Lorie , I. L. Traiger, The notions of consistency and predicate locks in a database system, Communications of the ACM, v.19 n.11, p.624-633, Nov. 1976 [doi>10.1145/360363.360369]
• GLPT: J. N. Gray , R. A. Lorie , G. R. Putzolu , I. L. Traiger, Granularity of locks and degrees of consistency in a shared data base, Readings in database systems (3rd ed.), Morgan Kaufmann Publishers Inc., San Francisco, CA, 1998
• GR: Jim Gray , Andreas Reuter, Transaction Processing: Concepts and Techniques, Morgan Kaufmann Publishers Inc., San Francisco, CA, 1992
• HOB: Lilian Hobbs , Ken England, Rdb/VMS: a comprehensive guide, Digital Press, Newton, MA, 1991
• ILL: Illustra Information Technologies, "Illustra User's Guide," , Illustra information Technologies, Oakland, CA. 1994.
• MS: Jim Melton , Alan R. Simon, Understanding the new SQL: a complete guide, Morgan Kaufmann Publishers Inc., San Francisco, CA, 1993
• OOBBGM: P. O'Neil, E. O'Neil, H. Berenson, P. Bemstein, J. Gray, J. Melton, "An Investigation of Transactional Isolation Levels," UMass/Boston Dept. of Math & C.S. Preprint.
• ORA: "'PL/SQL User's Guide and Reference, Version 1.0," Part. 800-V 1.0, Oracle Corp., 1989.
• PAP: Christos Papadimitriou, The theory of database concurrency control, Computer Science Press, Inc., New York, NY, 1986
• PON: Patrick O'Neil, Database systems: principles, programming, performance, Morgan Kaufmann Publishers Inc., San Francisco, CA, 1994
• REE: David P. Reed, Implementing atomic actions on decentralized data, ACM Transactions on Computer Systems (TOCS), v.1 n.1, p.3-23, Feb. 1983 [doi>10.1145/357353.357355]
• STO: Michael Stonebraker, The design of the POSTGRES storage system, Readings in database systems (2nd ed.), Morgan Kaufmann Publishers Inc., San Francisco, CA, 1994
• THA: M. Thakaar, "Transaction Models in InterBase 4," Proceedings of the Borland international Conference, June 1994.

INDEX TERMS

Primary Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.4 Systems
          Subjects: Transaction processing

Additional Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.2 Physical Design
          Subjects: Deadlock avoidance
      H.2.3 Languages

          Nouns: SQL
      H.2.4 Systems
          Subjects: Concurrency

General Terms:
Algorithms, Design, Languages, Theory