Exploiting Semantics and Speculation for Improving the Performance of Read-only Transactions

1. T. Ragunathan and P. Krishna Reddy Exploiting Semantics and Speculation for Improving the Performance of Read-only Transactions International Institute of Information Technology (IIIT-H), Hyderabad. E-mail: ragunathan@research.iiit.ac.in

2. Outline • Introduction • Concurrency Control Protocols • 2PL • SI-based Protocols • Speculative locking protocols • Speculative locking protocols for ROTs • Proposed Concurrency Control Protocol for ROTs • Basic Idea: compensatability • Synchronous Speculative Locking for ROTs with Semantics (SSLR-S) • Simulation results • Conclusions and Future Work

3. Introduction • Modern information systems supported by DBMS frequently receive read-only transactions (ROTs) or queries. Issues for Processing ROTs • Performance • High throughput performance • Correctness • Serializability criteria • Data Currency • Get the latest committed data

4. About Data Currency • Data currency • “The data currency of the data object provided to Ti is the value of “t” which is the time difference between the commit time of the transaction which created the latest version of the data object and the commit time of the transaction which created the version of that data object that was read by Ti. • If “t” is less/more, it means that transactions are provided with high/low data currency.”. • Example: cricket score, stock value

5. Motivation • Two-phase locking is a popular CC protocol • Performance of ROTs degrades with data contention. • Snapshot-isolation-based protocol is popular for ROTs • Suffers from data currency issues. • Correctness is compromised.

6. Motivation • Speculative locking protocol (SL) • A transaction carries out multiple executions under speculation • Improves the performance by trading extra processing resources • We are extending SL-based protocols to improve the performance of ROTs • 2PL is followed for UTs and SL is followed for ROTs • Issue: UTs are blocked in case of conflict with ROTs

7. Introduction • We have proposed a protocol by exploiting semantics of ROTs for improving the performance. • Does not violate serializability criterias • No data currency related issues • Improves performance over other protocols.

8. Outline • Introduction • Concurrency Control Protocols • Two-phase locking • SI-based Protocols • Speculative locking protocols • Speculative locking protocols for ROTs • Proposed Concurrency Control Protocol for ROTs • Basic Idea: compensatability • Synchronous Speculative Locking for ROTs with Semantics (SSLR-S) • Simulation results • Conclusions and Future Work

9. Two-phase locking • Popular concurrency control protocol. • Two phases • Growing phase: transaction obtains locks • Shrinking phase: Transaction releases locks. It is not allowed to obtain locks after releasing any lock.

10. Lock Request by Ti Lock Held by Tj R W R yes no W no no Example under 2PL T1 & T3 are UTs T2 is a ROT

11. SI-based Protocols • Transactions are processed at snapshot isolation (SI) level. • An ROT reads data from the snapshot of the (committed) data available when it has started or generated the first read operation. • An ROT running at SI is never blocked. • SI-protocol with ``First Committer Wins Rule’’ • Let Ti and Tj be UTs. Ti will successfully commit if and only if no concurrent Tj has already committed writes of data objects that Ti intends to write.

12. Example under SI-based protocol T1 & T3 are UTs and T2 is a ROT • About SI-protocols • Ignores the effect of concurrent update transactions (UTs). • High performance • Data currency is compromised • Violates serializability criteria

13. Example about Serializability violation under SI-based protocol T1 & T2 are UTs T3 is a ROT

14. Speculative Locking Protocol • Normally, a transaction starts producing after-images at the earlier stage of its execution. • Though these after-image values are not effected during remaining processing, the transaction releases locks only after commit processing. • In speculative approach, the transaction releases locks on a data object whenever it produces corresponding after-image value. • By accessing both before- and after-images, the waiting transaction carries out speculative executions. • A transaction commits only after receiving termination decisions of the preceding transactions.

15. r1[x0] w1[x1] r1[y0] w1[y1] T1 s1 c1 r2[x1] w2[x3] r2[z0] w2[z1] T2 s2 c2 Speculative Locking Protocol-Example • SL • 2PL Once the after-image of `x’ is available, T2 can begin speculative executions. (until then it has to wait). But in 2PL, T2 has to wait until T1 commits. Thus, SL avoids waiting of transactions.

16. SL-Based Protocols for ROTs • Straightforward extension of SL to process ROTs results into explosion of speculative executions. • Because, • If we process UTs with SL, it leads to explosion of speculative executions of ROTs and UTs. • Number of data object versions in data object trees also explode.

17. Growth of Object trees SL-based protocol for ROTS SL protocol

18. SL-based protocols for ROTs • Processing: • UTs are processed with two-phase locking (2PL)‏ • ROTs are processed with speculation. • Commitment: • ROTs commit whenever they complete. • Method of carrying out speculative executions • Synchronous: Speculative executions of ROTs are carried out synchronously • Asynchronous: Speculative execution of ROTs ae processed asynchronously .

19. SL for ROTs : Example T1 and T3 are UTs and T2 is ROT)‏ Once the after-image of `x’ is available, T2 can begin speculative executions. (until then it has to wait). T2 can commit without waiting for T1.

20. Lock Request by Ti Lock Held by Tj RR RU EW SPW RR yes yes no sp_yes RU yes yes no no EW no no no no Lock Compatibility Matrix for SSLR • We have used the following locks • RR - Read lock for ROTs • RU - Read lock for UTs • EW - Exclusive Write lock • SPW- Speculative Write lock • `sp_yes’ indicates that the requesting transaction (ROT) carries out speculative executions by reading after-image of the data object. But, this ROT can commit without waiting for preceding transaction.

21. Outline • Introduction • Concurrency Control Protocols • 2PL • SI-based Protocols • Speculative locking protocols • Speculative locking protocols for ROTs • Proposed Concurrency Control Protocol for ROTs • Basic Idea: compensatability • Synchronous Speculative Locking for ROTs with Semantics (SSLR-S) • Simulation results • Conclusions and Future Work

22. Basic Idea: Compensatability • SL-based protocol for ROTs blocks UTs which conflict with ROTs. • In the proposed protocol • UTs conflicting with ROTs are allowed to continue the execution. • However, such ROTs have to perform compensating computations during their commitment • The property of ROTs which allow compensation, is called “compensatability”. • The compensatable ROTs need not perform speculative executions.

23. Compensatability: An Example • T1 is an ROT and T2 is a UT. Both are concurrent transactions. • T1: r[x], r[y], z = x + y, d[z], commit. T2: r[y], y = y+10, w[y′], commit. • While T1 performs the computation (z = x + y), T2 modified the ‘y’ value to ‘y+10’ (‘y′’) • The computation performed by T1 (addition) satisfies the “compensatability” property. • Here the compensating function (g) for T1 • “z + (y′ – y)”.

24. Formal definition: Compensatability • Let Ti be an ROT and Tj be a UT. • Consider that Ti accesses data object ‘x’ at the time instant ‘ts’ and produces new data object ‘y’ at the time instant te (te > ts). • In parallel, Tj accesses ‘x’ and produces ‘x′’ at time instant tu (ts < tu < te). • Here ‘x′’ is the value of ‘x’ modified by Tj . Consider that if Ti would have accessed ‘x′’, it would have produced new data object ‘z’. • We say, the computation by Ti on ‘x’ is “compensatable”, if there exists a function or computation ‘g’ such that z=g(x′).

25. Overview of the proposed protocol • Based on the compensatability property of ROTs, the ROTs are classified into • Compensatable ROTs (CROTs) and • Non-compensatable ROTs (NCROTs) • CROTs are executed without blocking and NCROTs are executed with speculation (SSLR). • CROTs perform compensating computations once they are completed. • UTs in compatible with CROTs alone are processed without blocking.

26. Compensating Operations for CROTs • Whenever a CROT conflicts with UT the IDs of data objects which have been modified by that UT are recorded by the CROT. • After its completion, a CROT searches for the IDs of data objects in the transaction log. • The CROT performs the compensating computations as per the procedure available in the transaction program of that CROT.

27. Transaction Processing with SSLR-S T1 is ROT, T2 is UT

28. Lock compatibility matrix for SSLR-S CR lock – Compensatable Read lock NR lock- Non-compensatable Read lock

29. Outline • Introduction • Concurrency Control Protocols • 2PL • SI-based Protocols • Speculative locking protocols • Speculative locking protocols for ROTs • Proposed Concurrency Control Protocol for ROTs • Basic Idea: compensatability • Synchronous Speculative Locking for ROTs with Semantics (SSLR-S) • Simulation results • Conclusions and Future Work

30. Simulation Model • Discrete event simulator based on a closed-queuing model is developed.

31. Simulation Parameters • Discrete event simulator based on a closed-queuing model is developed.

32. Performance Metrics • Throughput # of transactions, ROTs, UTs completed per second • Average number of speculative executions per transaction. • Let ‘e’ be the number of speculative executions performed and ‘n’ be the number of transactions executed in the system. • Average number of speculative executions per transaction = e/n

33. % of CROTs versus Throughput (30% UTs)

34. % of CROTs versus Throughput (50% UTs)

35. % CROTs versus UTs Throughput ( 30% UTs)

36. % CROTs versus UT Throughput (50% UTs)

37. % CROTs versus ROT Throughput ( 30% UTs)

38. % of CROTs versus ROT Throughput (50% UTs)

39. % of CROTs versus Average number of speculative executions ( 30% UTs)

40. % of CROTs versus Average number of speculative executions ( 50% UTs)

41. “% of UTs” (u) denotes percentage of UTs currently active in the system. • “% of CROTs” (c) means percentage of CROTs active in the system. • “% of NCROTs” active in the system = (100 - u – c)

42. Outline • Introduction • Concurrency Control Protocols • 2PL • SI-based Protocols • Speculative locking protocols • Speculative locking protocols for ROTs • Proposed Concurrency Control Protocol for ROTs • Basic Idea: compensatability • Synchronous Speculative Locking for ROTs with Semantics (SSLR-S) • Simulation Results • Conclusions and Future Work

43. Conclusions and future work • Proposed an improved protocol for ROTs by exploiting the notion called “Compensatability” of ROTs and speculation. • Improves the performance of ROTs over 2PL, Snapshot Isolation and SSLR protocols. • Requires less number of speculative executions than SL protocol.

44. Conclusions and future work • Future work • Investigate more about how an ROT can be made compensatable by considering TPC benchmarks • Investigate the performance by applying the proposed protocol in data warehousing environment.

45. Thank you

46. Related Work • Four isolation levels are specified in ANSI/ISO SQL-92 standard • read uncommitted, read committed, repeatable read, and serializable. Satya Narayanan and Agrawal (1993). • Multiversion environment. ROTs are processed separately. The problem with this approach is delayed visibility. For example, an ROT executed immediately after a UT ‘Ti’ may not see the results of ‘Ti’ (Data currency problem).

47. Related Work • To get high currency, the ROTs have to be executed as pseudo-update transactions. So, the performance will be reduced. Mohan et.al. 1992 • Multiple versions of data objects are maintained, Also, a version of a data object is valid for a particular interval (version period). • The ROTs are allowed to access a particular version of the data object, based on their arrival time. • Any modification performed on that data object is visible only in the next version period. So ROTs are provided with low data currency.

48. Related Work - SI • Snapshot Isolation • Serializability Violation • Low Data Currency Problem • Recently Alan Fekete (ACM June 2005) has made an effort to make SI serializable. They suggested a theory using which static dependencies between transaction programs have to be identified. Then a SDG (Static dependency graph) has to be formed. In this SDG each node is a transaction program.

49. Related Work - SI • From the SDG, vulnerable edges have to be identified. If vulnerable edges form a cycle, pivot element is identified. • The program representing the pivot element has to be run in 2PL mode in order to avoid anomalies. • Materializing conflict (adding new tables and adding entries by the transaction programs. This may increase the aborts due to FCWR) and Promotion (modifying the program code) are other techniques to avoid anomalies.

50. Related Work - SI Sudhir and Fekete (VLDB 2007) • They are working on a tool to analyze the SQL transaction statements to identify the “pivot” programs which may cause anomalies (VLDB 2007). This tool requires description on tables and SQL transaction statements collected from the log or trace. The tool cannot directly work on the transaction code. • But, to avoid anomalies, still the DBA has to manually perform one of the three techniques (Running with 2PL, Materializing conflict or Promotion).