Automating the Detection of Snapshot Isolation Anomalies Sudhir

Automating the Detection of Snapshot Isolation Anomalies Sudhir Jorwekar (IIT Bombay) n Alan Fekete (Univ. Sydney) n Krithi Ramamritham (IIT Bombay) n S. Sudarshan (IIT Bombay) n Presented By: Ajitav Sahoo (08305011) 1

Motivation n Non-serializable executions are possible in Snapshot Isolation. n Many industry applications run on systems that use Snapshot Isolation as the isolation level (highest level of consistency). q n e. g. Oracle, Postgre. SQL, SQL Server etc. Theory for identifying such anomalies already exists (Needs manual analysis) 2

Agenda n n Background on OLTP and Concurrency control Introduction to Snapshot Isolation Protocol q q n n Examples of SI-Anomalies Existing Theory for Detecting SI-Anomalies Analyzing the transaction programs Reducing the false positive Results Conclusion 3

OLTP Environment n Data stored in DBMS q n Static collection of application programs q q q n Integrity constraints applied Possibly parameterized Can query and/or modify database Written to assume integrity constraints Users run application programs q Same program run many times with different parameters, perhaps concurrently 4

Concurrency Problems n Interleaving of read/writes by different applications can leave data not matching. q n Usual Problems q q n Violation of Integrity constraints Lost update (txn states not present in final state) Inconsistent read (partial effect of txn seen) DBMS must control concurrent execution of transactions, to ensure consistency. 5

Serializability n A schedule/history is serializable, q q provided it is equivalent (in outputs and in impact on final state) to an execution with each application running alone after another serially. n It specifies that all transactions occur in a completely isolated fashion. n If the system detects a concurrent txn in progress which would violate the serializability, it must force that txn to roll back. Highest level of Consistency 6

Two-Phase Locking Protocol n n This is a protocol which ensures conflict-serializable schedules. Phase 1: Growing Phase q q n Phase 2: Shrinking Phase q q n transaction may obtain locks transaction may not release locks transaction may not obtain locks Although assures serializability, doesn’t ensure freedom from deadlocks and cascading roll-back is possible. 7

Two-Phase Locking Protocol (contd. . ) n Strict Two-phase locking q q n Rigorous Two-phase locking q q n Modified version of 2 PL A transaction must hold all its exclusive locks till it commits/aborts. Even stricter than strict-2 PL Here all locks are held till commit/abort. Multiversion Two-phase locking q q keep old versions of data item to increase concurrency. Differentiates between read-only transactions and update transactions 8

Multiversion Two-Phase Locking n Update transactions acquire read and write locks, and hold all locks up to the end of the transaction. That is, update transactions follow rigorous two-phase locking. q q n Each successful write results in the creation of a new version of the data item written. To read a data item, it obtains a shared lock on it, and reads the latest version. Read-only transactions are assigned a timestamp by reading the current value of ts-counter before they start execution; they follow the multiversion timestamp-ordering protocol for performing reads. 9

Other Weaker Isolation Levels n Read Uncommitted (one txn may see uncommitted changes by some other txn). n Read Committed (data records retrieved by a query are not prevented from modification by some other transaction). n Repeatable Read (the txn acquires read locks on all retrieved data, but does not acquire range locks. These weaker levels bring anomalies like Nonrepeatable read, Dirty read, and Phantom read. 10

Drawbacks of Serializability n Best known way to ensure serializable execution is strict 2 PL, which get locks before reading or writing. Holds locks until application commits. n Application programs are blocked (even read-only) and leads to low concurrency. Throughput is drastically lowered. 11

What is Snapshot Isolation? n A transaction T executing with Snapshot Isolation Ø Ø Ø takes snapshot of committed data at start always reads/modifies data in its own snapshot updates of concurrent transactions are not visible to T writes of T complete when it commits Intuition: snapshot should be consistent, if the database was consistent before. n Read doesn’t give current value (instead gives value at it was when transaction started) n Not equivalent to a serial execution q In a serial execution, one transaction would see the other 12

Implementation of Snapshot Isolation n n Commits only if no other concurrent transaction has already written data that T 1 intends to write. (First Committer wins) Most implementations use exclusive locks on modified rows (First Updater wins Policy) Concurrent updates not visible Own updates are visible Not first-committer of X Serialization error, T 2 is rolled back T 1 T 2 T 3 R(Y) 0 Commit Start R(X) 0 W(Y : = 1) W(X: =2) W(Z: =3) Commit R(Z) 0 R(Y) 1 W(X: =3) Commit-Req Abort 13

First Committer Wins T 1: deposits 40 in X T 2: deposits 70 in X R(X, 100) W(X, 170) W(X, 140) Commit Request : Serialization problem detected by SI, ABORT! 14

Benefits of SI n No extra storage for multiple versions. Reading is never blocked, even by concurrent writer. So, throughput is good. Performance similar to Read Committed Better concurrency than serializable isolation level n Prevents classical anomalies: n n n q q No lost update (because first committer wins) No inconsistent read (all txn reads see the same set of complete txn) 15

Snapshot Isolation Anomalies SI breaks serializability when transactions modify. Among concurrent transaction, neither sees the effect of the other. Doesn’t always give serializable execution, integrity constraints can be violated. n n n Two common types of anomalies: [Fekete et al. SIGMOD’ 05] n q q Write skew anomaly Read-only Transactional anomaly 16

Anomaly: Write Skew (with Updates) Constraint: X+Y>=0 Initially, X = 100 and Y = 0 T 1: withdraw 70 from X T 2: withdraw 90 from Y R(X, 100) R(Y, 0) W(Y, -90) W(X, 30) Commit X + Y = -60 Skewed Write 17

Anomaly: Write Skew (with Inserts) • A voucher with unique voucher# is to be created for every bill • Programmer codes : m = select max(vno) ; insert new tuple (billno, voucher#=m+1) ; • Let max(vno)=10 and new vouchers for bill numbers X and Y are to be created T 1: Insert bill no. X T 2: Insert bill no. Y R(max(vno), 10) Insert(X, 11) Insert(Y, 11) Commit Duplicate voucher# created Skewed Write 18

Detecting Anomalies Goal is to ensure that every possible execution in given application is serializable (not just a particular execution). n Application consists of transaction programs q from which different transactions are generated depending on n n the parameter values (input) the control structures (decision based on value) Transactions might interleave in different ways. Hence, it is infeasible to enumerate every possible execution. 19

Detecting Anomalies: Static Analysis SDG: Static Dependency Graph [Fekete et al. TODS’ 05] n n Nodes : Transaction Programs as nodes. Edges : Let T 1 and T 2 be any execution instances of transaction program P 1 and P 2 respectively q q P 1 → P 2 if there can exist some T 1 that conflicts with some T 2 it is marked vulnerable if dependency does not prevent concurrent execution P 1 VUL R P 2 20

Conflicts under SI n Read Dependency (WR) P 1→ P 2 q q n Write Dependency (WW) P 1→ P 2 q q n P 1 modifies a data item, which is seen by P 2’s read. P 1 must completely precede P 2 (because of snapshot read) P 1 modifies a data item, that is later modified by P 2 P 1 must completely precede P 2 (because of first-committer-wins) Antidependency (RW) P 1→ P 2 q q P 1 reads a data item, and doesn’t see P 2’s modifications Either P 1 precedes P 2, or P 1 and P 2 are concurrent. 21

Static Analysis (contd…) n Conditions for Vulnerability q q RW conflict from T 1 to T 2, without WW conflict T 1 and T 2 are concurrent. Pivot P n n T 1 RW T 2 and T 1 WW T 2 S R Pivot (Dangerous Structure) q Q A transaction program P is a pivot in a SDG, if there is a cycle containing subpath with P 1 VUL R P 2 VUL R P 3 22

Static Analysis (contd…) n Theorem [Fekete TODS’ 05] q n What if pivot exists in the SDG ? q q n Absence of pivot in the SDG(A) serializable execution under SI. Modify some application programs without changing business logic. Change at least one vulnerable edge to be non-vulnerable. Not always possible, also may have different impacts on concurrency. 23

Avoiding Anomalies n S 2 PL for Pivots q q n Materializing Conflicts q q n Assumption is that the SI transactions obtain write locks for data items written. Introduces commit order on vulnerable edge. Make both transactions write a common data item (in a new table). converts vulnerable edge to non-vulnerable (or protected). Promoting reads to writes q q q Promoting reads in P 1 to writes will cause T 1 WW T 2 converts outgoing vulnerable edge to non-vulnerable (or protected). select for update (in case of Oracle) 24

Transaction Programs in SQL n Identifying Set of Transaction Programs (SQL) q Program Analysis. n q SQL traces at backend. n n May not be possible for large applications. May not cover all the transaction programs. Characteristics of Transaction Programs (in SQL) q SQL statements n q SELECT, INSERT, DELETE etc. Parameterization n WHERE col=: User. Input 25

Mini Banking example n Schema q q q n account (accno, balance, acctype) customer (id, name, address) owner (id, accno) txn (txnid, txntype, accno, id, amount, timestamp) batchaudit (bid, start, end, inamount, outamount) Transactions q q q Creating new Account (CA 1, CA 2) Update Customer Info (UCI) Deposit (DEP) Withdraw (W 1, W 2) End-of-day Audit (EOD) 26

Identifying Dependencies n Syntactic read and write sets q q n rset(P) is the set of columns read by transaction program P wset(P) is the set of columns written by P. For UCI transaction shown below: begin; select * from customer where id=: id; update customer set name=? , address=? where id=: id; commit; rset(UCI) = {customer. id, customer. name, customer. address} wset(UCI) = {customer. name, customer. address} 27

Syntactic Column-based Analysis n Column-based Syntactic Dependency Graph (CSDG) q q Nodes are transaction programs An edge Pi → Pj is marked when n q rset(Pi) wset(Pj) or wset(Pi) rset(Pj) or wset(Pi) wset(Pj) An edge Pi → Pj is marked when as pseudovulnerable (PVUL) whenever rset(Pi) wset(Pj) q P is a syntactic pseudopivot, if some cycles of edges in CSDG contains a subpath P 1 PVUL R P 2 PVUL R P 3 28

Syntactic Analysis (contd…) n n An edge in CSDG is pseudovulnerable, when corresponding edge in SDG is vulnerable. Every pivot is a syntactic pseudopivot. [but not vice versa] Theorem: If a set of transaction programs contains no syntactic pseudopivots, then every execution under SI will in fact be serializable. n n Syntactic column-based analysis is safe. No false negatives (where a potential anomaly is not identified) 29

False Positives Syntactic Pseudopivot Many transactions which can never cause any anomaly are detected as syntactic pseudopivot. False Positives Pseudovulnerable CSDG for Banking Application 30

Eliminating False Positives 1: Modification Protected Readset (MPR) n UCI has a pseudovulnerable self edge. q q due to syntactic conflict between select and update. seems to imply that two copies of UCI could create an anomaly. n But selected row is updated subsequently so first committer wins, the other aborts. n Lowest level data item in Postgre. SQL and Oracle: Row. rset and wset are in terms of rows. First Committer Wins rule applied at row level. n n 31

Modification Protected Readset (MPR) n MPR-Select A statement S in a transaction program P 1 is said to be MPR w. r. t transaction program P 2, if either q rset(S) wset(P 2) , or q the WHERE clause predicate C used in S is stable w. r. t P 2. n Stable Predicate q Predicate C used in P 1 is stable w. r. t P 2, iff for every possible schedule H containing execution instances of P 1 and P 2 as T 1 and T 2 resp, the set of rows identified by C in T 1 doesn’t depend on the serializable order. 32

MPR Transactions n MPR Transaction: q q n A transaction program P 1 is said to be MPR w. r. t P 2 if, every select query as well as every subquery of an insert, delete or update in P 1 is an MPR-Select w. r. t P 2. WHERE clause predicates of every update/delete statement in P 1 are stable w. r. t. P 2. If a transaction program P 1 is MPR w. r. t P 2, and if the DBMS uses row-level granularity for the first-committerwins check, the edge from P 1 to P 2 can not be vulnerable. (Theorem) 33

Eliminating False Positives 2: New Identifier Generation Test begin; select max(accno)+1 as m from account; insert into account(accno, balance, type) values (: m, 0, : type); commit; n rset(CA 1) = {account. accno}, wset(CA 1) = {account. *} n New Identifier Generation Analysis: q q q for assigning new primary key (numeric) if two transactions read same max value and create same identifier, SI will not prevent concurrent execution n but primary key or referential constraint will! Explicitly check Select-max conflict, outside snapshot 34

Eliminating False Positives 3: Existence Check Before Insert begin; select accno as found from account where accno=: m; if(found==null) insert into account values (: m, 0, : type); else print ‘Error: Requested account number is already in use’; endif commit; nrset(CA 1) = {account. accno}, wset(CA 1) = {account. *} n. Select with given PK. . . if not found (Insert values with same PK) n. Select using primary key can not conflict with Insert of other transaction having same pattern. 35

After Eliminating False Positives Eliminated False Positives: • UCI: MPR • DEP: MPR • CA 1 & CA 2: NIGA / ECIA Remaining Syntactic Pseudopivot 36

Tool for Analyzing an application The automated tool has the following flow of activities: n 1. 2. 3. 4. 5. Find the set of transaction programs. Extract the syntactic read and write sets. Create CSDG using conservative syntactic analysis and detect syntactic pseudopivots. Eliminate/Reduce false positives. Select appropriate techniques to avoid anomalies (currently done manually). After using the techniques to avoid anomalies, we can rerun the analysis to check whether they worked. 37

Experimental Results TPC-C Mini Bank Acad. Financ e Distinct Transactions 7 7 26 34 Syntactic Pseudopivots detected 4 7 25 34 EFP 1: MPR Detected 3 2 11 4 EFP 2: New Identifier Generation Protection detected 0 2 3 3 EFP 3: Existence check before Insert protection 0 0 2 0 38

Conclusion n Theory of Syntactic Analysis to obtain a superset of transactions that may cause anomalies. n Studied some general patterns of false positives and proposed sufficient conditions for identifying such transactions. n Developed a tool that can automate the testing of database applications for safety against SI anomalies q q identified some genuine problems in production code. Implementation issues discussed in paper. 39

Thank You! 40

References 1. S. Jorwekar, A. Fekete, K. Ramamritham, S. Sudarshan in VLDB 2007: “Automating the Detection of Snapshot Isolation Anomalies” 2. A. Fekete, D. Liarokapis, E. O'Neil, P. O’Neil, D. Shasha in TODS 2005: “Making Snapshot Isolation Serializable” 3. A. Fekete, E. O'Neil, P. O’Neil in SIGMOD 2004: “A Readonly Transaction Anomaly under Snapshot Isolation” 41