Locking-based isolations levels

• serializable
  • 2PL (long-duration read/write locks)
• repeatable read
  • long w/r locks, short predicate read locks
  • could have phantom read
    • r1[P]…w2[y in P]…c2…r1[P]…c1
• read committed
  • long w locks, short r locks
  • could have non-repeatable (fuzzy) read
    • r1[x]…w2[x]…c2…r1[x]…c1
• read uncommitted
  • long write locks, no read locks.
  • could have dirty read
    • w1[x]…r2[x]…(a1 and c2 in any order)

ANSI

• English version
  • read committed cannot have dirty read
  • repeatable read cannot have dirty/fuzzy read
  • serializability cannot have any of: dirty/fuzzy/phantom read
• Simply forbidding dirty/fuzzy/phantom read does not give you serializability, or, “anomaly serializable != serializable”
  • H1: r1[x=50]w1[x=10]r2[x=10]r2[y=50]c2 r1[y=50]w1[y=90]c1
  • H2: r1[x=50]r2[x=50]w2[x=10]r2[y=50]w2[y=90]c2r1[y=90]c1
  • H3: r1[P] w2[insert y to P] r2[z] w2[z] c2 r1[z] c10
• Snapshot isolation
  • read skew, not possible with si
    • r1[x]…w2[x]…w2[y]…c2…r1[y]…(c1 or a1)
  • write skew, possible with si
    • r1[x]…r2[y]…w1[y]…w2[x]…(c1 and c2 occur)
  • stronger than read committed
  • stronger than “anomaly serializable”.
  • revisit the long fork example: read skew

    T1: R(A)=0, R(B)=0, W(A)=1 
    T2:                        R(A)=1, R(B)=0
    T3: R(A)=0, R(B)=0, W(B)=1 
    T4:                        R(A)=0, R(B)=1
    T5:                                       R(A)=1, R(B)=1
    

Graph

• Read uncommitted
  • disallows cycles consisting only of write-write edges
• Read committed/repeatable read
  • disallow cycles consisting of write-write/write-read edges
• Snapshot isolation, disallows:
  • write-write/write-read edges without corresponding start edges
  • cycles containing a single read-write edge