1 / 12

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.

bardia
Download Presentation

A New Basis for the SQL Isolation Level Standard

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

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

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

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

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

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

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

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

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

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

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

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

More Related