1 / 44

Transaction

Transaction. Outline. Transaction and OLTP Desired properties of transactions Schedule Concurrent transactions and their properties. Transaction. A multi-billion dollar business OLTP http://www.tpc.org/default.asp. Transaction Definition.

heath
Download Presentation

Transaction

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. Transaction Yan Huang - CSCI5330 Database Implementation –Transaction

  2. Outline • Transaction and OLTP • Desired properties of transactions • Schedule • Concurrent transactions and their properties Yan Huang - CSCI5330 Database Implementation –Transaction

  3. Transaction • A multi-billion dollar business • OLTP • http://www.tpc.org/default.asp Yan Huang - CSCI5330 Database Implementation –Transaction

  4. Transaction Definition • A unit of program execution that accesses and possibly updates various data items • Transactions? • Book an airline ticket from DFW to Paris • Buy “The Dilbert Principle” from amazon.com • Sell 1000 shares of LU from your ameritrade account • Withdraw $100 from a ATM machine • Issue a SQL statement to sqlplus of Oracle 9i • Look at your grade of homework 3 • Check out your groceries at Walmart Yan Huang - CSCI5330 Database Implementation –Transaction

  5. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  6. Challenges to Maintain Transactions • Hardware failures • Cashed stuck in ATM machine • Power failure… • Software failures • Programming errors • System crash… • User interference • Termination of transactions • Concurrent users • Multiple users accessing the same item Yan Huang - CSCI5330 Database Implementation –Transaction

  7. Designed Properties of Database Systems • Atomicity • Consistency • Isolation • Durability Yan Huang - CSCI5330 Database Implementation –Transaction

  8. Atomicity • Transaction needs to be executed as a unit • Example • You should not cause the quantity of “The Dilbert Principle” of amazon.com decrease if you place your order and the order does not get through due to server errors • Who are responsible for atomicity? • Transaction management system and • Recovery system Yan Huang - CSCI5330 Database Implementation –Transaction

  9. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  10. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  11. Consistency • Database implicit/explicit constraints need to be maintained • Example: • Transferring money from one account to another in the same bank should not change your total amount of money • Who are responsible for consistency? • Transaction management system and • Programmer Yan Huang - CSCI5330 Database Implementation –Transaction

  12. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  13. Isolation • Transaction A should not see partial results of transaction B • Analogy: • When I update my website here and there, you should not see and think a tentative version as my final version • Who are responsible for isolation? • Transaction management system Yan Huang - CSCI5330 Database Implementation –Transaction

  14. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  15. Durability • Any transaction committed needs to be in database for ever • Example: • After you get the receipt of the water melon you buy from Alberson, the transaction is final and permanently reflected in the database system • If you want to cancel it, that is another transaction • Who are responsible for durability? • Transaction management system and • Recovery system Yan Huang - CSCI5330 Database Implementation –Transaction

  16. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  17. Transaction State (Cont.) a1, a2, a3, a4, …, an, commit Yan Huang - CSCI5330 Database Implementation –Transaction

  18. An Ideal World • No hardware failures • No software failures • No programming errors • Do we still need transaction management? Yan Huang - CSCI5330 Database Implementation –Transaction

  19. Why Concurrent Transactions? • Parallelism • Improved response time Yan Huang - CSCI5330 Database Implementation –Transaction

  20. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  21. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  22. 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. Yan Huang - CSCI5330 Database Implementation –Transaction

  23. 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 = ? Yan Huang - CSCI5330 Database Implementation –Transaction

  24. 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 = ? Yan Huang - CSCI5330 Database Implementation –Transaction

  25. 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 = ? Yan Huang - CSCI5330 Database Implementation –Transaction

  26. Where is the mystery? • How to preserve database consistency? Serializability! Yan Huang - CSCI5330 Database Implementation –Transaction

  27. Serializability • A (possibly concurrent) schedule is serializable if it is equivalent to a serial schedule. Yan Huang - CSCI5330 Database Implementation –Transaction

  28. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  29. 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. Yan Huang - CSCI5330 Database Implementation –Transaction

  30. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  31. 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) Yan Huang - CSCI5330 Database Implementation –Transaction

  32. 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) Yan Huang - CSCI5330 Database Implementation –Transaction

  33. 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) Yan Huang - CSCI5330 Database Implementation –Transaction

  34. 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) Yan Huang - CSCI5330 Database Implementation –Transaction

  35. 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) Yan Huang - CSCI5330 Database Implementation –Transaction

  36. 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) Yan Huang - CSCI5330 Database Implementation –Transaction

  37. 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 Yan Huang - CSCI5330 Database Implementation –Transaction

  38. 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 >. Yan Huang - CSCI5330 Database Implementation –Transaction

  39. Testing for Serializability • Precedence graph— a direct graph where the vertices are the transactions (names). Yan Huang - CSCI5330 Database Implementation –Transaction

  40. 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) Yan Huang - CSCI5330 Database Implementation –Transaction

  41. Precedence Graph for Schedule A T1 T2 T5 T4 T3 Yan Huang - CSCI5330 Database Implementation –Transaction

  42. Recoverability • Only commit after the transaction your read from commits Yan Huang - CSCI5330 Database Implementation –Transaction

  43. Cascadeless Schedule • Only read committed write Yan Huang - CSCI5330 Database Implementation –Transaction

  44. Check Schedules Schedule 4 Read(A) Read(A) Write(A) Read(B) Write(A) Read(B) Write(B) Write(B) Schedule 1 Read(A) Read(A) Write(A) Read(B) Write(A) Read(B) Write(B) Write(B) Schedule 2 Read(A) Write(A) Read(B) Write(B) Read(A) Write(A) Read(B) Write(B) Schedule 3 Read(A) Write(A) Read(A) Write(A) Read(B) Write(B) Read(B) Write(B) Conflict serializable? Recoverable? Cascadeless? Yan Huang - CSCI5330 Database Implementation –Transaction

More Related