Issue

  • Highest isolation + lower isolation transactions running concurrently
  • Lower isolation corrupt data

ANSI SQL Isolation levels

P1 Dirty Read

  • T1 modify -> T2 read -> T1 rollback
    • T2 read never committed data

P2 Non-repeatable or Fuzzy Read

  • T1 read -> T2 modify commit -> T1 read
    • T1 read inconsistent

P3 Phantom

  • T1 search <condition> -> T2 insert new data -> T1 search again
    • T1 search inconsistent

Locking

  • Share/Exclusive Locks
  • Predicate lock
    • lock on a set of data satisfying <condition>
    • might include phantom data
  • Well-formed
    • request a lock before using
  • Two phase lock
    • request all locks before releasing any
  • Duration
    • long: hold until the transaction commits or aborts
    • short: otherwise

New SQL Isolation levels

Snapshot Isolation

  • The transaction successfully commits only if no other transaction T2 with a Commit-Timestamp in T 1‘s interval [Start-Timestamp, Commit - Timestsamp] wrote data that T 1 also wrote.