Transaction I

1. Transaction I Yan Huang - CSCI5330 Database Implementation –Transaction

2. Outline • Transaction and OLTP • Designed properties of transactions 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. 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

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

7. 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

8. 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

9. 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

10. 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

11. Transaction’s State Diagram Yan Huang - CSCI5330 Database Implementation –Transaction

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

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

14. Schedule • 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

15. Example Schedules Yan Huang - CSCI5330 Database Implementation –Transaction

16. Example Schedule (Cont.) Yan Huang - CSCI5330 Database Implementation –Transaction

17. Example Schedules Yan Huang - CSCI5330 Database Implementation –Transaction

18. Concurrency Control • Some schedules are bad because the outcome of the schedule is not “predictable” Yan Huang - CSCI5330 Database Implementation –Transaction