A CLASSIC HORROR STORY Ease of Performance Programming THE PIT - PowerPoint PPT Presentation

A CLASSIC HORROR STORY Ease of Performance Programming THE PIT AND THE PENDULUM Lorenzo Alvisi Database Programmer Ease Ease of Performance of Performance Programming Programming Database Programmer Database Programmer

ACID TRANSACTIONS: CONCURRENCY SIMPLE AND POWERFUL Pierre Franc Lamy Young girl on a balcony (1911) Atomicity Txn Txn ACID Txn Txn Txn Txn Consistency Txn z := x+y Txn Txn y++ Txn Txn Isolation Txn Txn x := 0 Txn Durability Carlo Carrà Concurrency, Woman on a balcony (1912) PERFORMANCE VIA WEAKER ANSI SQL-92 ISOLATION GUARANTEES ISOLATION LEVELS Database System Default Isolation Strongest Isolation Proscribed Phenomena MySQL Cluster Read Committed Read Committed Isolation Level • Defined in terms of three Dirty Fuzzy Phantom SAP HANA Read Committed Snapshot Isolation phenomena that can lead to Read Read violations of serializability Google Spanner Serializability Serializability Read Uncommitted Motivated by weakening locking • VoltDB Serializability Serializability implementations of serializability Read Committed Oracle 12C Read Committed Snapshot Isolation • Designed to be implementation Repeatable Read MemSQL Read Committed Read Committed independent (greater flexibility/ better performance) SQL Server Read Committed Serializability (Anomaly) Serializable Postgres Read Committed Serializability

FUZZY READS DIRTY READS A . K . A . N ON - REPEATABLE R EADS Root: Write-Read conflict Root: Read-Write conflict T 1 T 2 T 1 T 2 T 1 modifies a data item. T 1 reads a data item. ‣ ‣ BEGIN BEGIN R(A) A = 5 R(A) A = 5 T 2 reads that data item before T 1 T 2 then modifies or deletes that data ‣ ‣ W(A) A = 12 BEGIN BEGIN commits or aborts. item and commits. R(A) A = 12 W(A) A = 10 W(A) A = 15 COMMIT If T 1 then aborts, T 2 has read a data COMMIT If T 1 then attempts to reread the item, it ‣ ‣ R(B) R(A) A = 10 W(B) item that was never committed and so receives a modified value or discovers COMMIT ABORT never really existed. the item was deleted. THE PHANTOM MENACE WHAT’S NOT TO LIKE? Berenson et al, SIGMOD ‘95 Non-repeatable predicate-based reads • Ambiguous descriptions of proscribed behaviors On July 10 2018… T 1 reads a set of data items satisfying ‣ T 1 T 2 Dirty Reads <search condition>. BEGIN SELECT MAX (salary) T 2 then creates data items that satisfy T 1 T 2 ‣ 8M FROM players • Strict Interpretation (prohibits anomaly) BEGIN WHERE team = ‘Juve’ T 1 ’s <search condition> and commits. BEGIN R(A) A = 5 INSERT INTO players W(A) A1: W 1 [X] … R 2 [X] … (A 1 and C 2 in any order) A = 12 ‣ (salary = 30M, BEGIN If T 1 then repeats its read with the ‣ team = ‘Juve’) R(A) A = 12 same <search condition>, it gets a COMMIT W(A) A = 15 • Broad Interpretation (prohibits phenomenon) SELECT MAX (salary) FROM players COMMIT 30M different set of data R(B) WHERE team = ‘Juve’ P1: W 1 [X] … R 2 [X] … ((A 1 or C 1 ) and (A 2 or C 2 ) in any order) W(B) ‣ COMMIT ABORT similar distinctions for P2 (NR reads) and P3 (Phantoms)

PHENOMENA OR PHENOMENA OR ANOMALIES? ANOMALIES? Dirty Reads Non-repeatable Reads • Non serializable • Non serializable T 1 T 2 T 1 T 2 ‣ T 2 reads the wrong balance ‣ T 1 reads the wrong balance BEGIN BEGIN BEGIN BEGIN R(X= 50) • Yet fine by Strict Interpretation A 1 … R(X= 50) • Yet fine by Strict Interpretation A 2 … W(X = 10) R(X = 50) R(X = 10) W(X = 10) ‣ W 1 [X] … R 2 [X] … (A 1 and C 2 in any order) ‣ R 1 [X] … W 2 [X] … C 2 … R 1 [X] … C 1 R(Y = 50) R(Y = 50) COMMIT ‣ T 1 does not abort! ‣ No transaction reads same value twice W(Y = 90) R(Y=50) COMMIT W(Y = 90) R(Y=90) • but violates Broad Interpretation P 1 • but violates Broad Interpretation P 2 COMMIT COMMIT W 1 [X] … R 2 [X] … ((A 1 or C 1 ) and (A 2 or R 1 [X] … W 2 [X] … ((A 1 or C 1 ) and (A 2 or ‣ ‣ C 2 ) in any order) C 2 ) in any order) WHAT’S NOT TO LIKE? ANSI isolation levels • ANSI SQL phenomena are weaker than their locking counterpart should be intended to proscribe Isolation Level Read Locks Write Locks phenomena, Locking Read None Long † write locks Uncommitted not anomalies Short* read locks Locking Read Long write locks Committed (both) Long item locks Locking Long write locks Repeatable Read Short predicate locks Long read locks Locking Long write locks Serializable (both) Short*: Released after operation ends Long†: Released after transaction commits

WHAT’S NOT TO LIKE? still • ANSI SQL phenomena are weaker than their locking counterpart ANSI P3 should prevent phantoms due to Isolation Level Read Locks Write Locks deletions and updates, Locking Read None Long † write locks Uncommitted not just creations Short* read locks Locking Read Long write locks Committed (both) Long item locks Locking Long write locks Repeatable Read Short predicate locks Long read locks Locking Long write locks Serializable (both) Short*: Released after operation ends Long†: Released after transaction commits DIRTY WRITES Root: Write-Write conflicts ANSI isolation levels T 1 T 2 should include T 1 modifies a data item ‣ BEGIN W(A) A = 10 phenomenon P0 T 2 further modifies that data item ‣ BEGIN before T1 commits or aborts. W(A) A = 15 W(B) B = 15 COMMIT ‣ Conflicting writes can interleave, P0 : W 1 [X]…W 2 [X]…(C 1 or A 1 ) and (C 2 or A 2 ) in any order W(B) B = 10 violating invariants COMMIT

ANSI-92 ISOLATION LEVELS, AND YET… POST CRITIQUE • “P0, P1, P2, and P3 are a disguised version of locking” Locking Proscribed Read locks on data Write locks on data Isolation Level Phenomena items and phantoms items and phantoms ‣ no implementation independence Degree 0 none none Short* write locks ‣ Preventing concurrent execution of conflicting Degree 1 = Locking P0 none Long † write locks operations approach rules out optimistic and multi READ UNCOMMITTED version implementations Degree 2 = Locking P0, P1 Short read locks Long write locks READ COMMITTED Locking Long data-item read locks; P0, P1, P2 Long write locks • P0: W 1 [X] … W 2 [X] … (C 1 or A 1 ) REPEATABLE READ Short phantom read locks Degree 3 = Locking rules out optimistic implementations P0, P1, P2, P3 Long read locks Long write locks ‣ SERIALIZABLE similar argument holds for P1, P2. P3 ‣ Long†: Released after transaction commits Short*: Released after operation ends THE RUB SNAPSHOT ISOLATION • Phenomena expressed through single object histories • T reads from a snapshot of committed values • but consistency often involves multiple objects at T’s start time • T’s own writes are reflected in its snapshot • Same guarantees for running and committed transactions • When ready to commit, T receives a commit time • but optimistic approaches thrive on the difference • T commits if its updates do not conflict with • Definition in terms of objects, not versions those of any transaction which committed in the interval between T’s start time and commit time • no support for multiversion systems

WRITE SKEW ANOMALY WRITE SKEW ANOMALY T 1 : Change T 1 : Change green to red green to red T 2 : Change T 2 : Change red to green red to green GENERALIZED WRITE SKEW ANOMALY ISOLATION DEFINITIONS Adya et al, SIGMOD ‘95 * * T 1 : Change • Executions modeled as histories green to red a partial order of read/write operations that respects ‣ * * order of operations in each transaction * * ‣ a total order << of object versions created by committed transactions T 2 : Change Serial red to green * * or