1 / 36

Chapter 15: Transactions

Chapter 15: Transactions. Transaction Concept Concurrent Executions Serializability Testing for Serializability. Transaction Concept. A transaction is a unit of program execution that accesses and possibly updates various data items. Two main issues to deal with:

tavon
Download Presentation

Chapter 15: 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. Chapter 15: Transactions • Transaction Concept • Concurrent Executions • Serializability • Testing for Serializability

  2. Transaction Concept • A transactionis a unit of program execution that accesses and possibly updates various data items. • Two main issues to deal with: • Failures of various kinds, such as hardware failures and system crashes • Concurrent execution of multiple transactions a1, a2, a3, a4, …, an, commit consistent Database may be inconsistent consistent a1, a2, a3, a4, …, an, commit b1, b2, b3, b4, …, bm, commit c1, c2, c3, c4, …, cl, commit

  3. ACID Properties To preserve integrity of data, the database system must ensure: • Atomicity • Consistency • Isolation • Durability

  4. Example of Fund Transfer • Transaction to transfer $50 from account A to account B: • read(A) • A := A – 50 • write(A) • read(B) • B := B + 50 • write(B) • commit

  5. Atomicity • Either all operations of the transaction are properly reflected • Or none are (1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit (1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50

  6. Consistency • A+B = TOT where TOT is a constant value • Consistency: DB satisfies all integrity and constraints • Examples: • - x is key of relation R • - x  y holds in R • - Domain(x) = {Red, Blue, Green} • - a is valid index for attribute x of R • no employee should make more than twice the average salary • A+B = TOT (1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit A+B= TOT A+B may not equal to TOT A+B= TOT

  7. Isolation A+B ≠ TOT?! • Intermediate transaction results must be hidden from other concurrently executed transactions. T2 (1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit A+B= TOT A+B may not equal to TOT A+B= TOT

  8. Durability • After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures. (1) read(A), (2)A := A -50,(3)write(A), (4) read(B), (5)B := B + 50, (6)write(B), (7) commit After this point, A and B are permanently updated

  9. Transaction State • Active • Partially committed • Committed • Failed • Aborted

  10. Transaction State (Cont.) a1, a2, a3, a4, …, an, commit

  11. Implementation of Atomicity and Durability The shadow-database scheme: • Assumes one transaction at a time • Useful for text editors, but extremely inefficient for large databases: executing a single transaction requires copying the entire database.

  12. Storage Hierarchy • Read(x) read x from memory, if it is not in memory yet, read from disk first • Write(x) writes x to memory and possibly to disk • read(A) • A := A – 50 • write(A) • read(B) • B := B + 50 • write(B) • commit x x Memory Disk

  13. Schedules Schedule 1 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) T1 Read(A) A:=A-50 Write(A) Read(B) B:=B+50 Write(B) T2 Read(A) Temp:=A*0.1 A:=A-temp Write(A) Read(B) B:=B+temp Write(B) T1 transfer $50 from A to B T2 transfer 10% of the balance from A to B

  14. Schedules • Schedules – sequences that indicate the chronological order in which instructions of concurrent transactions are executed • a schedule for a set of transactions must consist of all instructions of those transactions • must preserve the order in which the instructions appear in each individual transaction.

  15. Concurrent Executions • Multiple transactions are allowed to run concurrently in the system • Concurrency control schemes– mechanisms to achieve isolation, i.e., to control the interaction among the concurrent transactions in order to prevent them from destroying the consistency of the database

  16. Serial Schedule • T1 is followed by T2. Schedule 2 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) A = 100, B = 100 originally A = ? and B = ?

  17. Example Schedule (Cont.) • Schedule 3 is equivalent to Schedule 1. Schedule 3 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) A = 100, B = 100 originally In both Schedule 2 and 3, the sum A + B is preserved. A = ? and B = ?

  18. Example Schedules (Cont.) Schedule 4 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) A = 100, B = 100 originally Schedule 4 does not preserve the sum A + B A = ? and B = ?

  19. Where is the mystery? • How to preserve database consistency? Serializability!

  20. Serializability • A (possibly concurrent) schedule is serializable if it is equivalent to a serial schedule.

  21. Conflict Serializability • Transactions T1 and T2 • Two operations on the same item Q, • Intuitively, a conflict between T1 and T2 forces a (logical) temporal order between T1 and T2 . • Two consecutive non-conflict operations in a schedule can been interchanged Conflict? T1 T2

  22. Conflict Serializability (Cont.) • If a schedule S can be transformed into a schedule S´ by a series of swaps of non-conflicting instructions, we say that S and S´ are conflict equivalent.

  23. Note • Only read and write operations will cause conflict • Other operations (A:=A+10) are on local copy variables and do not interface with database

  24. Simplified Schedules Schedule 3 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 Read(A) Write(A) Read(A) Write(A) Read(B) Write(B) Read(B) Write(B) Schedule 2 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 2 Read(A) Write(A) Read(B) Write(B) Read(A) Write(A) Read(B) Write(B)

  25. Schedule 3 and Schedule 2 are conflict equivalent Schedule 3 Read(A) Write(A) Read(A) Write(A) Read(B) Write(B) Read(B) Write(B) Schedule 2 Read(A) Write(A) Read(B) Write(B) Read(A) Write(A) Read(B) Write(B)

  26. Schedule 3 and Schedule 2 are conflict equivalent Schedule 3 Read(A) Write(A) Read(A) Read(B) Write(A) Write(B) Read(B) Write(B) Schedule 2 Read(A) Write(A) Read(B) Write(B) Read(A) Write(A) Read(B) Write(B)

  27. Schedule 3 and Schedule 2 are conflict equivalent Schedule 3 Read(A) Write(A) Read(A) Read(B) Write(B) Write(A) Read(B) Write(B) Schedule 2 Read(A) Write(A) Read(B) Write(B) Read(A) Write(A) Read(B) Write(B)

  28. Schedule 3 and Schedule 2 are conflict equivalent Schedule 3 Read(A) Write(A) Read(B) Read(A) Write(B) Write(A) Read(B) Write(B) Schedule 2 Read(A) Write(A) Read(B) Write(B) Read(A) Write(A) Read(B) Write(B)

  29. Schedule 3 and Schedule 2 are conflict equivalent Schedule 3 Read(A) Write(A) Read(B) Write(B) Read(A) Write(A) Read(B) Write(B) Schedule 2 Read(A) Write(A) Read(B) Write(B) Read(A) Write(A) Read(B) Write(B)

  30. Conflict Serializability (Cont.) • We say that a schedule S is conflict serializable if it is conflict equivalent to a serial schedule • Schedule 3 is conflict serializable

  31. Conflict Serializability (Cont.) • Example of a schedule that is not conflict serializable: T3T4read(Q)write(Q)write(Q)We are unable to swap instructions in the above schedule to obtain either the serial schedule < T3, T4 >, or the serial schedule < T4, T3 >.

  32. Testing for Serializability • Consider some schedule of a set of transactions T1, T2, ..., Tn • Precedence graph— a direct graph where the vertices are the transactions (names). • We draw an arc from Tito Tjif the two transaction conflict, and Tiaccessed the data item on which the conflict arose earlier. • We may label the arc by the item that was accessed. • Example 1 x y

  33. Example Schedule (Schedule A) T1 T2 T3 T4 T5 read(X)read(Y)read(Z) read(V) read(W) read(W) read(Y) write(Y) write(Z)read(U) read(Y) write(Y) read(Z) write(Z) read(U)write(U)

  34. Precedence Graph for Schedule A T1 T2 T4 T5 T3

  35. Test for Conflict Serializability • A schedule is conflict serializable if and only if its precedence graph is acyclic. • Cycle-detection algorithms exist which take order n2 time, where n is the number of vertices in the graph. (Better algorithms take order n + e where e is the number of edges.) • If precedence graph is acyclic, the serializability order can be obtained by a topological sorting of the graph. This is a linear order consistent with the partial order of the graph.For example, a serializability order for Schedule A would beT5T1T3T2T4.

  36. Concurrency Control vs. Serializability Tests • Testing a schedule for serializability after it has executed is a little too late! • Goal – to develop concurrency control protocols that will assure serializability. They will generally not examine the precedence graph as it is being created; instead a protocol will impose a discipline that avoids nonseralizable schedules. • Tests for serializability help understand why a concurrency control protocol is correct.

More Related