Automating the Detection of Snapshot Isolation Anomalies

1. Automating the Detection ofSnapshot Isolation Anomalies Sudhir Jorwekar (IIT Bombay) Alan Fekete (Univ. Sydney) Krithi Ramamritham (IIT Bombay) S. Sudarshan (IIT Bombay) Presented By: Shailendra Shrivastav(09305911)

2. Motivation • To get serializable execution with non-blocking reads. • Many industry applications run on systems that use Snapshot Isolation as the isolation level (highest level of consistency). • e.g. Oracle, PostgreSQL, SQL Server etc. • Automation of SI anomalies detection in a set of transaction programs. (manual analysis already exists)

3. Contents • Introduction • Snapshot Isolation Protocol • Examples of SI-Anomalies • Detecting SI-Anomalies • Removing SI-Anomalies • Syntactic Analysis • Eliminating False Positive • Tools used for Automation • Conclusion

4. Introduction • Snapshot Isolation(SI) provides significantly improved concurrency over 2PL, allowing reads to be non-blocking • Unfortunately it can lead to non-serializable execution but it is widely used. • SI Anomalies Detection/Correction: • Step 1: Find set of programs possibly leading to SI anomalies • Step 2: Address the problem of False positive(programs wrongly identified as possibly leading to anomalies). NOTE:- Unlike earlier work it is designed to be automated rather than manually carried out.

5. Some important terms to be Understood: • Isolation Level: ‘Isolation Level’ is a setting that decides how data which is a part of an ongoing transaction is made visible to other transactions. • High Level of Isolation(serializable): • High level of consistency • Low level of concurrency • Low Level of Isolation: • Low level consistency • High level of concurrency • Serializability: A Tx schedule will be called serializable, if it is equivalent to serial serial schedule.

6. Concurrency : Allowing, Interleaving of read/writes by different applications at a time. • Usual Problems • Lost update (txn states not present in final state) • Inconsistent read (partial effect of txn seen) • Solution: • Concurrency Control mechanism • Lock Based: • Shared Lock(S) • Exclusive Lock(X) • Timestamp based: • DBMS must control concurrent execution of transactions, to ensure consistency.

7. Two-Phase Locking(2PL) Protocol: This is a protocol which ensures conflict-serializable schedules. • Phase 1: Growing Phase • transaction may obtain locks • transaction may not release locks • Phase 2: Shrinking Phase • transaction may release locks • transaction may not obtain locks • Two-Phase Locking(2PL) Protocol: This is same as 2PL with some enhancement as “ It holds the exclusive lock until it commits”

8. Multiversion Protocol: With each data item Q, a sequence of versions <Q1, Q2, . . .,Qm> is associated. Each version Qk contains three data fields: • Content is the value of version Qk. • W-timestamp(Qk) is the timestamp of the transaction that created version Qk. • R-timestamp(Qk) is the largest timestamp of any transaction that successfully read version Qk. • Transaction (Ti) issues a read(Q) or write(Q) operation. Let Qk denote the version of Q and W-timestamp(Qk)<=TS(Ti). 1. If transaction Ti issues a read(Q), then the value returned is the content of version Qk and R-timestamp(Qk) is updated by the largest value of TS(Ti) and current R-timestamp(Qk). 2. If transaction Ti issues write(Q), and if TS(Ti)<R-timestamp(Qk), then the system rolls back transaction Ti. On the other hand, if TS(Ti) = W-timestamp(Qk), the system overwrites the contents of Qk; otherwise it creates a new version of Q.

9. SI is an attractive optimistic concurrency control protocol widely implemented and widely used. • Attractive features: • Reads are always non-blocking • Consistency problems are not widely seen • Reason: • Data items that are read are also updated • Violation of integrity constraint, so rollback eliminates the anomalies. • Drawback: • Vulnerable to anomalies e.g. “write skew”, “read-only Tx” • Tx can lead to non-serializable & cause database inconsistent.

10. Snapshot Isolation Protocol • SI is an extension of multiversion concurrency control. • A transaction T1 executing with Snapshot Isolation • takes snapshot of committed data at start of T1 called start- timestamp • always reads/modifies data in its own snapshot • updates of concurrent transactions are not visible to T1 • T1 is allowed to commit only when another Tx t2 running concurrently has not already written the data item that T1 intends to write. • Intuition:snapshot should be consistent, if the database was consistent before. • Read doesn’t give current value (instead gives value at it was when transaction started) • Not equivalent to a serial execution • In a serial execution, one transaction would see the other

11. Example: Transaction T2 and T3 read data from snapshot of committed data made by Transaction T1. T2 will not be allowed to commit if T3 has already written the data T2 intends to write. (First Committer wins) Concurrent updates not visible Own updates are visible Not first-committer of X Serialization error, T2 is rolled back

12. Benefits of SI • 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 • Prevents classical anomalies: • No lost update (because first committer wins) • No inconsistent read (all txn reads see the same set of complete txn)

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

14. Anomaly: Write Skew (with Updates) Constraint: X+Y>=0 Initially, X = 100 and Y = 0 X + Y = -60 Skewed Write

15. 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 Duplicate voucher# created Skewed Write

16. Detecting SI-Anomalies • Whether a transaction leads to an anomaly can by determined by a serialization graph for an execution. • Graph Structure: • Nodes: Transaction • Edges: Conflicts or Dependencies b/w Transactions. In the graph there will be an edge from Ti to Tj, if Ti is reading a version of and item and Tj produces a later version of the same item. Key Theorem: If graph is acyclic then execution will be serializable & no anomalies can occur. Ti Tj

17. Detecting SI-Anomalies(Cont…) Goal is to ensure that everypossible execution in given application is serializable (not just a particular execution). • Application consists of transaction programs • from which different transactions are generated depending on • 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.

18. Detecting SI-Anomalies: Static Analysis SDG: Static Dependency Graph [Fekete et al. TODS’05] It is drawn for a given collection of application programs A. and denoted by SDG(A). • Nodes : Application Programs • Edges : For two application programs p1 and p2, an edge from p1 to p2 will be marked vulnerable, if there is some execution of the system in which T1 and T2 be any execution instances of P1and P2 respectively where • There is dependency between T1 and T2 • T1 and T2 are concurrent • Vulnerable edge is shown by dashed line. P1 P2

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

20. Static Analysis (contd…) • Conditions for Vulnerability • RW conflict from T1 to T2, without WW conflict • T1 and T2 are concurrent. • T1 T2 and T1 T2 • Pivot (Dangerous Structure) • A transaction program P is a pivot in a SDG, if there is a cycle containing subpath with Pivot P RW WW S Q R VUL VUL P1 R P2 R P3

21. Static Analysis (contd…) • Theorem [Fekete TODS’05] • Absence of pivot in the SDG(A)  serializable execution under SI. • What if pivot exists in the SDG ? • 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.

22. Removing SI-Anomalies • Strict 2PL for Pivots • Run the pivot programs with true serializability (using 2PL), rather than using SI. • This doesn’t require any modification in pivot programs, except for configuration information for the session. • Implementation: • A version of an item X produced by an SI-Tx T must be protected by an exclusive lock before T commits. • The lock is obtained by following the normal locking rules. • Microsoft server 2005 and MySQL use both SI and 2PL • Oracle and PostgreSQL use SI for Isolation level serializability

23. Removing SI-Anomalies(Cont…) • Materializing Conflicts: • Programmers can explicitly introduce extra conflicts to transactions, in order to prevent the transactions from running concurrently. • Implementation: • One option: make both transactions write the same row of some table (for a given vulnerable edge). • This means “First-committer wins” is invoked, so Txs can’t run concurrently and edge becomes non vulnerable.

24. Removing SI-Anomalies(Cont…) • Promotion: • In this approach we change the program at the tail end of the edge (the one with the read with read-write conflict). E.g. Read is promoted to write. • Implementation: • By inserting an update statement before select statement. e.g. P1(prior): “ select T.c from T where …” P1(later): “update T set T.c=T.c” “ select T.c from T where …” • Similar effect is obtained by replacing “select ..” by “select ..for update” (which we abbreviate to SFU), used in Oracle. Reads by select statement are treated as updates. • Promotion can be applied to Pivot P or predecessor of P when Pred(P) has read-write conflict with Pivot, by promoting read to write. Note: Promotion does not solve the phantom problem

25. Transaction Programs in SQL • Identifying Set of Transaction Programs (SQL) • Extracting Tx programs from the source code of the application program. • By logging the SQL statements submitted to the Database. • If the application has control flow the application level Tx is split into multiple straight–line Txs, because different execution paths in the application may generate different set of SQL statements • Transaction Programs (in SQL) • These are set of SQL statements including SELECT, INSERT, DELETE, UPDATE. • Parameterization is achieved by WHERE clause e.g. WHERE col=:UserInput

26. Mini Banking example • Schema • 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 • Creating new Account (CA1, CA2) • Update Customer Info (UCI) • Deposit (DEP) • Withdraw (W1, W2) • End-of-day Audit (EOD)

27. Identifying Dependencies • Defining Syntactic read and write sets for each Tx. • rset(P) is the set of columns read by transaction program P • wset(P) is the set of columns written by P. • The entries in these sets are like “tablename.column” • 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}

28. Syntactic Column-based Analysis • Column-based Syntactic Dependency Graph (CSDG) • Nodes are transaction programs • An edge Pi →Pjis marked when (rset(Pj) ∩ wset(Pk) != ∅) ∨ (wset(Pj) ∩ rset(Pk) != ∅) ∨ (wset(Pj) ∩ wset(Pk) != ∅) • An edge Pi →Pjis marked as pseudovulnerable (PVUL) whenever rset(Pi) wset(Pj)   • P is a syntactic pseudopivot, if some cycles of edges in CSDG contains a sub path as: R P Q

29. Syntactic Analysis (contd…) • Syntactic Analysis is safe, that is there are no false-negative(a potential anomaly is not identified). That is CSDG has an edge if SDG has and edge • Every pivot is a syntactic pseudopivot. [but not vice versa] • Strictter Definition of pseudovulnerable edge: • An edge Pi →Pj is marked as pseudovulnerable (PVUL) whenever (rset(Pi)  wset(Pj)  ) and ( wset(Pi)  wset(Pj) = ) • this is not safe, because syntactic write set can be over approximation of the true write set. Theorem 1:If a set of transaction programs contains no syntactic pseudopivots, then every execution under SI will in fact be serializable.

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

31. Eliminating False Positives 1: Modification Protected Readset (MPR) • The Oracle and PostgreSQL, implementation of SI treat a tuple as the lowest level data item. • “First-committer wins” rule forces that two Txs are not concurrent if both commit updates on any columns of same row. • Now we are looking for a broad definition that covers a significant number of false positives among the pseudopivots. • Stable Predicate • Predicate C used in P1 is stable w.r.t P2, iff for every possible schedule H containing execution instances of P1 and P2 as T1 and T2 resp, the set of rows identified by C in T1 doesn’t depend on the serialization order.

32. Modification Protected Readset (MPR) • MPR-Select A statement S in a transaction program P1 is said to be MPR w.r.t transaction program P2, if either

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

34. MPR Analysis • MPR Analysis: We say that a transaction is found to be a false positive using MPR analysis if • It is detected as a syntactic pseudopivot, and • After eliminating vulnerable edges using theorem-2, the transaction is found not be a pivot • MPR analysis depends on understanding predicates • Goal: simplified sufficient conditions for MPR, based on columns read/updated by transaction programs • Insert-Delete Stable Table: A table t is said to be insert-delete stable w.r.t Tx program P, if P doesn’t contain any insert or delete statement which operates on table t.

35. MPR Analysis(Cont…) • Syntactically Stable Column: Column c of table t, denoted by t.c is said to be syntactically stable w.r.t. Tx program P if t.c  wset(P) • Syntactically Stable Predicate: Consider a predicate C and Tx program P. If for every tablename.column used in C is stable w.r.t. P and every table on which C operates is insert-delete stable w.r.t P, then C is syntactically stable w.r.t. P. • Syntactically MPR-select: Same as MPR-select, except • “WHERE clause predicate C must be syntactically stable w.r.t. Transaction program P2.” • The where clause predicate C used in select statement is of the form (D and D’) where D is the predicate used by update/delete statement

36. MPR Analysis(Cont…) Theorem 3:If S is a select statement in a transaction program P1 such that S is syntactically MPR w.r.t. Tx program P2 then S is MPR w.r.t. P2 • Example: Update customer transaction(UCI): • Here there is pseudovulnerable self-loop from UCI to itself, but with the theorem2 and theorem3 we will deduce that the edge is not vulnerable.

37. 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; • New Identifier Generation Analysis: • for assigning new primary key (numeric) • if two transactions read same max value and create same identifier, SI will not prevent concurrent execution • but primary key or referential constraint will! • Explicitly check Select-max conflict, outside snapshot rset(CA1) = {account.accno} wset(CA1) = {account.*}

38. 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; • rset(CA1) = {account.accno}, wset(CA1) = {account.*} • Select with given PK ... if not found (Insert values with same PK) • Select using primary key can not conflict with Insert of other transaction having same pattern.

39. After Eliminating False Positives • Eliminated False Positives: • UCI: MPR • DEP: MPR • CA1 & CA2: NIGA / ECIA Remaining Syntactic Pseudopivot

40. Tool for Analyzing an application • The automated tool has the following flow of activities: • 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.

41. Experimental Results

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

43. References • S. Jorwekar, A. Fekete, K. Ramamritham, S. Sudarshan in VLDB 2007: “Automating the Detection of Snapshot Isolation Anomalies”

44. Thank You!