CS162 Operating Systems and Systems Programming Lecture 18 Transactions April 4 2011 Ion Stoica http inst eecs berkeley edu cs162 Goals for Today Transactions concurrency control Two phase lock Strict two phase lock Note Some slides and or pictures in the following are adapted from lecture notes by Mike Franklin 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 2 Recap Read Writer Example Writer Reader check into system check into system lock Acquire lock Acquire AW AR 0 while AW WW 0 while WW WR okToWrite wait lock okToRead wait lock WW WR AW lock release AR lock release read write access AccessDbase ReadWrite read only access AccessDbase ReadOnly check out of system lock Acquire AW check out of system if WW 0 lock Acquire okToWrite signal else if WR 0 AR okToRead broadcast if AR 0 WW 0 okToWrite signal lock Release lock Release 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 3 Recap Read Writer Example Properties Allow multiple concurrent active readers if no active writer Only one writer at a time If a writer waits no new active readers are allowed Locking granularity entire database 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 4 Locking Granularity What granularity to lock Database Tables Rows Database Table 1 Table 3 Row Table 2 Table 4 Fine granularity e g row high concurrency Multiple users can update the database and same table simultaneously Coarse granularity e g database table simple but low concurrency 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 5 From Multiprogramming to Transactions Users would like the illusion of running their programs on the machine alone Why not running the entire program in a critical section Users want fast response time and operators want to increase machine utilization increase concurrency Interleave executions of multiple programs How can DBMS database management system help 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 6 Concurrent Execution Transactions Concurrent execution essential for good performance Disk slow so need to keep the CPU busy by working on several user programs concurrently DBMS only concerned about what data is read written from to the database Not concerned about other operations performed by program on data Transaction DBMS s abstract view of a user program i e a sequence of reads and writes 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 7 Transaction Example BEGIN BEGIN TRANSACTION UPDATE accounts SET balance balance 100 00 WHERE name Alice UPDATE branches SET balance balance 100 00 WHERE name SELECT branch name FROM accounts WHERE name Alice UPDATE accounts SET balance balance 100 00 WHERE name Bob UPDATE branches SET balance balance 100 00 WHERE name SELECT branch name FROM accounts WHERE name Bob COMMIT 4 4 COMMIT WORK Ion Stoica CS162 UCB Spring 2011 Lec 18 8 The ACID properties of Transactions Atomicity all actions in the transaction happen or none happen Consistency if each transaction is consistent and the DB starts consistent it ends up consistent Isolation execution of one transaction is isolated from that of all others Durability if a transaction commits its effects persist 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 9 Atomicity A transaction might commit after completing all its operations or it could abort or be aborted by the DBMS after executing some operations Atomic Transactions a user can think of a transaction as always either executing all its operations or not executing any operations at all 4 4 DBMS logs all actions so that it can undo the actions of aborted transactions Ion Stoica CS162 UCB Spring 2011 Lec 18 10 Consistency Data in DBMS is accurate in modeling real world follows integrity constraints ICs If DBMS is consistent before transaction it will be after System checks ICs and if they fail the transaction rolls back i e is aborted DBMS enforces some ICs depending on the ICs declared in CREATE TABLE statements Beyond this DBMS does not understand the semantics of the data e g it does not understand how the interest on a bank account is computed 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 11 Isolation Each transaction executes as if it was running by itself Techniques 4 4 Concurrency is achieved by DBMS which interleaves operations reads writes of DB objects of various transactions Pessimistic don t let problems arise in the first place Optimistic assume conflicts are rare deal with them after they happen Ion Stoica CS162 UCB Spring 2011 Lec 18 12 Durability Data should survive in the presence of System crash Disk crash need backups 4 4 All committed updates and only those updates are reflected in the database Some care must be taken to handle the case of a crash occurring during the recovery process Ion Stoica CS162 UCB Spring 2011 Lec 18 13 This Lecture Deal with I solation by focusing on concurrency control For A tomicity C onsistency and D urability take cs186 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 14 Example Consider two transactions T1 moves 100 from account A to account B T1 A A 100 B B 100 T2 moves 50 from account B to account A T2 A A 50 B B 50 Each operation consists of 1 a read 2 an addition subtraction and 3 a write Example A A 100 Read A R A A A 100 Write A W A 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 15 Example cont d Database only sees reads and writes Database View T1 A A 100 B B 100 T1 R A W A R B W B T2 A A 50 B B 50 T2 R A W A R B W B Assume initially A 1000 and B 500 What is the legal outcome of running T1 and T2 A 950 B 550 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 16 Example cont d What is the outcome of the following execution T1 R A W A R B W B T2 A 900 B 600 R A W A R B W B A 950 B 550 Answer A 950 B 550 What is the outcome of the following execution T1 R A W A R B W B T2 R A W A R B W B A 950 B 550 A 1050 B 450 Answer A 950 B 550 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 17 Example cont d What is the outcome of the following execution T1 R A W A R B W B T2 A 900 R A W A R B W B B 550 A 950 B 450 Answer A 950 B 550 What is the outcome of the following execution T1 R A W A R B W B T2 R A W A R B W B A 900 B 550 A 1050 B 450 Answer A 900 B 550 lost 50 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 18 Transaction Scheduling Why not run only one transaction at a time Answer low system utilization Two transactions cannot run simultaneously even …
View Full Document
Unlocking...