Transactions

1. Transactions B.Ramamurthy Ch.13 B.Ramamurthy

2. Introduction • A transaction is a unit of program execution that accesses and possibly updates various data items. • Transaction Management is a core operation in a DBMS. B.Ramamurthy

3. ACID Properties • It is requires that a DBMS maintain the following properties of transactions: Atomicity : All or nothing execution. Consistency : Execution of transaction results in consistent database. Isolation : Many transactions may execute concurrently but each is unaware of the others. Durability : Persistence. B.Ramamurthy

4. Example T1: read(A); A := A - 50; write (A); read (B); B := B + 50; write (B). Lets analyze ACID property with this transaction. B.Ramamurthy

5. Transaction State • Active, partially committed, failed, aborted, committed. partially committed committed active Disk IO completed failed aborted B.Ramamurthy

6. Concurrent Executions • A transaction consists of multiple steps. • There may be a mix of transactions running on the system. • (somewhat similar to multiprogramming in operating systems) • Concurrency control : maintain ACID property in the presence of concurrency. B.Ramamurthy

7. Example T2: read(A); temp := a*0.1; A := A - temp; write (A); read (B); B := B + temp; write (B). T1: read(A); A := A - 50; write (A); read (B); B := B + 50; write (B). Let A and B be 1000 and 2000 respectively. B.Ramamurthy

8. T1 T2 read(A); A := A - 50; write (A); read (B); B := B + 50; write (B). read(A); temp := a*0.1; A := A - temp; write (A); read (B); B := B + temp; write (B). Schedule 1 - Serial B.Ramamurthy

9. Schedule 2 - Serial T1 T2 read(A); temp := a*0.1; A := A - temp; write (A); read (B); B := B + temp; write (B). read(A); A := A - 50; write (A); read (B); B := B + 50; write (B). B.Ramamurthy

10. T1 T2 read(A); A := A - 50; write (A); read(A); temp := a*0.1; A := A - temp; write (A); read (B); B := B + 50; write (B). read (B); B := B + temp; write (B). Schedule 3 - Concurrent B.Ramamurthy

11. Schedule 4 - Concurrent but Incorrect T1 T2 read(A); A := A - 50; read(A); temp := a*0.1; A := A - temp; write (A); read (B); write (A); read (B); B := B + 50; write (B). B := B + temp; write (B). B.Ramamurthy

12. T1 T2 read(A); write (A); read(A); write (A); read (B); write (B). read (B); write (B). Schedule 3 : Only Read and Write B.Ramamurthy

13. Conflict Serializability • If Ti and Tj refer to same data, when executing them currently we are faced with: 1. Ri(Q), Rj(Q) 2. Ri(Q), Wj(Q) 3. Wi(Q), Rj(Q) 4. Wi(Q), Wj(Q) B.Ramamurthy

14. Schedule 5 and Schedule 6 T1 T2 T1 T2 read(A); write (A); read(A); write (A); read(A); write (A); read (B); write (B). read (B); write (B). read(A); write (A); read (B); write (B). read (B); write (B). A schedule is conflict serializable if it is conflict equivalent to a Serial schedule. 5 6 was arrived at by a series of exchanges. Schedule 3 is conflict serializable as shown by schedule 6. B.Ramamurthy

15. Schedule 7: Not Conflict Serialiable T4 T3 Read (q) Write (q) Write (q) This is not equivalent to either serial schedule <T3,T4> or <T4,T3> B.Ramamurthy

16. Schedule 8 T1 T2 read(A); A := A - 50; write (A); read (B); B := B - 10; write (B). read (B); B := B + 50; write (B). read (A); A := A + 10; write (A). B.Ramamurthy

17. Schedule 8 • Even though it cannot be proven to be conflict serializable it is indeed equivalent to a serial schedule <T1, T5>. • How? We should not only consider read’s and write’s but also the other statements within a transaction. B.Ramamurthy

18. T3 T6 T4 Read (Q) Write (Q) Write (Q) Write (Q) Schedule 9 This is a view serializable concurrent schedule equivalent to <T3,T4,T6> B.Ramamurthy

19. Schedule 11 T8 T9 Write(A) Read (B) Read (A) Read (A) Example of irrecoverable schedule. What is T9 commits after Read(A) and T8 fails after that? We desire schedules to be Recoverable. B.Ramamurthy

20. Schedule 12 T10 T12 T11 Read (B) Read (A) Write (A) Read (A) Write (A) Read (A) Cascaded rollback: T10 fails when T12 is reading. T10, T11 and T12 Have to rollback. It is desirable to have cascadeless rollback. B.Ramamurthy

21. Realizing Concurrent Schedules • Goal of concurrency control schemes is to provide high degree of concurrency while ensuring all the schedules generated are conflict or view serializable, and are cascadeless. • A simple example of a concurrency control: A transaction acquires a lock on the entire database before it starts, and releases it after it has committed. • Concurrency control leads to poor performance. B.Ramamurthy

22. Transactions in SQL • See the handout enclosed. • Commands: • Commit • Rollback • Serializable • Repeatable read • Read committed • Read uncommitted B.Ramamurthy

23. Test for Conflict Serializability • Let S be he schedule. Get the precedence graph G = (V,E). • V, Vertices are Transactions and E are edges Ti  Tj for • Ti Write(Q) before Tj Read(Q) • Ti Read(Q) before Tj Write(Q) • Ti Write(Q) before Tj Write(Q) • If there are no cycles in G then it is conflict-serializable. • Serial schedule is obtained by topological sorting. See examples. B.Ramamurthy

24. Summary • Transactions are an important part of any database system. • ACID property specifies the operating policy for transactions. • Concurrency control schemes provide the concurrency and maintain the ACID property. B.Ramamurthy