Goals for Today Transactions concurrency control Two phase lock Strict two phase lock CS162 Operating Systems and Systems Programming Lecture 18 Transactions April 4 2011 Ion Stoica http inst eecs berkeley edu cs162 Note Some slides and or pictures in the following are adapted from lecture notes by Mike Franklin 4 4 Recap Read Writer Example 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 while AW AR 0 while AW WW 0 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 AR else if WR 0 if AR 0 WW 0 okToRead broadcast okToWrite signal lock Release lock Release 4 4 Ion Stoica CS162 UCB Spring 2011 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 Lec 18 3 Page 1 Ion Stoica CS162 UCB Spring 2011 Lec 18 4 Locking Granularity What granularity to lock Database Tables Rows Database Table 1 From Multiprogramming to Transactions Users would like the illusion of running their programs on the machine alone Table 3 Row Table 2 Why not running the entire program in a critical section Table 4 Users want fast response time and operators want to increase machine utilization increase concurrency Interleave executions of multiple programs Fine granularity e g row high concurrency How can DBMS database management system help 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 4 4 Lec 18 5 Ion Stoica CS162 UCB Spring 2011 Transaction Example Concurrent Execution Transactions BEGIN BEGIN TRANSACTION UPDATE accounts SET balance balance 100 00 WHERE name Alice Concurrent execution essential for good performance Disk slow so need to keep the CPU busy by working on several user programs concurrently UPDATE branches SET balance balance 100 00 WHERE name SELECT branch name FROM accounts WHERE name Alice DBMS only concerned about what data is read written from to the database Not concerned about other operations performed by program on data 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 Transaction DBMS s abstract view of a user program i e a sequence of reads and writes COMMIT 4 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 6 4 4 Lec 18 7 Page 2 COMMIT WORK Ion Stoica CS162 UCB Spring 2011 Lec 18 8 The ACID properties of Transactions Atomicity Atomicity all actions in the transaction happen or none happen A transaction might commit after completing all its operations or it could abort or be aborted by the DBMS after executing some operations Consistency if each transaction is consistent and the DB starts consistent it ends up consistent Atomic Transactions a user can think of a transaction as always either executing all its operations or not executing any operations at all 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 DBMS logs all actions so that it can undo the actions of aborted transactions 4 4 Lec 18 9 Ion Stoica CS162 UCB Spring 2011 Consistency Isolation Data in DBMS is accurate in modeling real world follows integrity constraints ICs Each transaction executes as if it was running by itself 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 Ion Stoica CS162 UCB Spring 2011 4 4 Lec 18 11 Page 3 Concurrency is achieved by DBMS which interleaves operations reads writes of DB objects of various transactions Techniques 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 Lec 18 10 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 This Lecture Deal with I solation by focusing on concurrency control Data should survive in the presence of System crash Disk crash need backups 4 4 For A tomicity C onsistency and D urability take cs186 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 4 4 Lec 18 13 Ion Stoica CS162 UCB Spring 2011 Example Example cont d Consider two transactions Database only sees reads and writes T1 moves 100 from account A to account B T1 A A 100 B B 100 T1 A A 100 B B 100 T1 R A W A R B W B T2 moves 50 from account B to account A T2 A A 50 B B 50 T2 R A W A R B W B T2 A A 50 Database View B B 50 Assume initially A 1000 and B 500 What is the legal outcome of running T1 and T2 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 Lec 18 14 Ion Stoica CS162 UCB Spring 2011 A 950 B 550 4 4 Lec 18 15 Page 4 Ion Stoica CS162 UCB Spring 2011 Lec 18 16 Example cont d Example cont d What is the outcome of the following execution 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 T1 R A W A R B W B T2 R A W A R B W B A 900 B 550 B 550 A 950 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 B 450 A 1050 Answer A 950 B 550 4 4 Ion Stoica CS162 UCB Spring 2011 4 4 Lec 18 17 Ion Stoica CS162 UCB Spring 2011 Lec 18 18 Transaction Scheduling Serial schedule A schedule that does not interleave the operations of different transactions Why not run only one transaction at a time Transactions run serially one at a time Answer low system utilization Two transactions cannot run simultaneously even if they access different data Equivalent schedules For any database state the effect on the database and output of executing …
View Full Document
Unlocking...