A New Basis for the SQL Isolation Level Standard

A New Basis for the SQL Isolation Level Standard Atul Adya: Microsoft Research Barbara Liskov: LCS, MIT Patrick O’ Neil: Univ. Of Mass., Boston

ANSI Degrees of Isolation • Trade-off consistency for performance • Serializability  Disallow all “bad” situations • Implementation-independent

ANSI Definitions: Ambiguous • “Flexible” Interpretation: allows bad histories • “Locking” Interpretation: suggested approach • “Disguised” versions of locking • Multi-object constraints not observed as violated x1 10 y1 90 x0 = 50 y0 = 50 T1 T2 x1: 10 y0: 50 [BBG+ 95]: Berenson, Bernstein, Gray, Melton, O’Neil, O’Neil: SIGMOD 95

Need Locking-Independent Defns Current Interpretation • Overly restrictive: no concurrent conflicting opns Disallows optimistic and multi-version mechanisms Non-locking implementations: • Desirable in certain environments, e.g., mobile systems • Used in commercial products: [Oracle], [Gemstone] • Predicate-based operations: not handled properly

Our Approach • Specifications for ANSI levels (PL-1, PL-2, PL-3) • Implementation-independent and unambiguous • Compatible with existing ANSI definitions • Flexible guarantees for predicate-based operations • Definitions for existing commercial levels, e.g., Cursor Stability, Snapshot Isolation [BBG+95, Oracle] • Definitions for new levels weaker than serializability E.g., Weakest level that ensures consistent reads

First Observation Problem: Isolation provided as transactions run Idea:Provide separate guarantees for committed and running transactions – 3 Degrees for committed transactions Locking Interpretation 2 – – 1 | | | 1 2 3 Degrees for running transactions

Second Observation Problem: Multi-object constraints captured using conditions on single object histories Idea:Capture using invariants on complete read and write sets of transactions Use serialization graphs

Direct Serialization Graph History H x1 10 y1 90 T1 x0 = 50 y0 = 50 T2 x2 20 T3 y0: 50 x2: 20 WW WR DSG(H) T1 T2 T3 Committed transactions only RW

New Definitions: PL-3 and PL-2 Serializability (PL-3): • Capture essence of “no dirty reads”, i.e., ensure each committed transaction has onlyread modifications of committed transactions • No Aborted Reads • No Intermediate Reads • No Circular Information Flow: Disallow cyclesconsisting of only W  W or W  R edges • Disallow all cycles PL-2

Applying Definitions at PL-3 T1 Bad history disallowed (unlike “flexible”) Good history allowed (unlike “locking”) x1 10 y1 90 T1 WR RW T2 x1: 10 y0: 50 T2 x0 = 50 y0 = 50 T1 x1 10 y1 90 WR T1 T2 T2 x1: 10 y1: 90

Handling Predicates Approach: Add conflict edges for predicate opns T1: SELECT * FROM Emp WHERE Dept = Sales T2: UPDATE Dept = Legal FROM Emp WHERE Dept = Mktg T1 and T2 do not conflict T3: DELETE FROM Emp WHERE Location = Boston T3changes the set of tuples matched by T1’s read, i.e., T3overwrites T1’s predicate-based read T1 T3 R W

Summary • Our specifications can form the basis for fixing the ANSI standard • Implementation-independent • Handle predicates correctly • Backwards compatible and intuitive • Specifications for existing commercial levels, e.g., Cursor Stability, Snapshot Isolation • New useful isolation levels between degrees 2 and 3 http://www.pmg.lcs.mit.edu/~adya http://research.microsoft.com/~adya

A New Basis for the SQL Isolation Level Standard