A new basis for the sql isolation level standard
Download
1 / 12

A New Basis for the SQL Isolation Level Standard - PowerPoint PPT Presentation


  • 182 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'A New Basis for the SQL Isolation Level Standard' - despina


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
A new basis for the sql isolation level standard l.jpg

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 l.jpg
ANSI Degrees of Isolation

  • Trade-off consistency for performance

  • Serializability  Disallow all “bad” situations

  • Implementation-independent


Ansi definitions ambiguous l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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


ad