1 / 24

Transactions

Transactions. B.Ramamurthy Ch.13. 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. ACID Properties.

mae
Download Presentation

Transactions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related