Page 1 CS162!Operating Systems and!Systems Programming!Lecture 18!Transactions"April 4, 2011!Ion Stoica!http://inst.eecs.berkeley.edu/~cs162!Lec 18.2!4/4! Ion Stoica CS162 ©UCB Spring 2011!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."Lec 18.3!4/4! Ion Stoica CS162 ©UCB Spring 2011!Recap: Read/Writer Example"Reader() { // check into system lock.Acquire(); while ((AW + WW) > 0) { WR++; okToRead.wait(&lock); WR--; } AR++; lock.release(); // read-only access AccessDbase(ReadOnly); // check out of system lock.Acquire(); AR--; if (AR == 0 && WW > 0) okToWrite.signal(); lock.Release(); }!Writer() { // check into system lock.Acquire(); while ((AW + AR) > 0) { WW++; okToWrite.wait(&lock); WW--; } AW++; lock.release(); // read/write access AccessDbase(ReadWrite); // check out of system lock.Acquire(); AW--; if (WW > 0){ okToWrite.signal(); } else if (WR > 0) { okToRead.broadcast(); } lock.Release(); }!Lec 18.4!4/4! Ion Stoica CS162 ©UCB Spring 2011!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!Page 2 Lec 18.5!4/4! Ion Stoica CS162 ©UCB Spring 2011!Locking Granularity"• What granularity to lock?!– Database!– Tables!– Rows!• 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!Database!Table 1 !Row!Tab le 2 ! Tab le 4 !Tab le 3 !Lec 18.6!4/4! Ion Stoica CS162 ©UCB Spring 2011!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?!Lec 18.7!4/4! Ion Stoica CS162 ©UCB Spring 2011!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. Lec 18.8!4/4! Ion Stoica CS162 ©UCB Spring 2011!Transaction - Example 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');!BEGIN; --BEGIN TRANSACTION COMMIT; --COMMIT WORKPage 3 Lec 18.9!4/4! Ion Stoica CS162 ©UCB Spring 2011!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!Lec 18.10!4/4! Ion Stoica CS162 ©UCB Spring 2011!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 – DBMS logs all actions so that it can undo the actions of aborted transactions Lec 18.11!4/4! Ion Stoica CS162 ©UCB Spring 2011!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) Lec 18.12!4/4! Ion Stoica CS162 ©UCB Spring 2011!Isolation"• Each transaction executes as if it was running by itself – Concurrency is achieved by DBMS, which interleaves operations (reads/writes of DB objects) of various transactions • Techniques: – Pessimistic – donʼt let problems arise in the first place – Optimistic – assume conflicts are rare, deal with them after they happen.Page 4 Lec 18.13!4/4! Ion Stoica CS162 ©UCB Spring 2011!Durability"• Data should survive in the presence of!– System crash!– Disk crash need backups!• 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! Lec 18.14!4/4! Ion Stoica CS162 ©UCB Spring 2011!This Lecture"• Deal with (I)solation, by focusing on concurrency control • For (A)tomicity, (C)onsistency, and (D)urability take cs186! Lec 18.15!4/4! Ion Stoica CS162 ©UCB Spring 2011!Example"• Consider two transactions:!– T1: moves $100 from account A to account B!!! !!– T2: moves $50 from account B to account A!• Each operation consists of (1) a read, (2) an addition/subtraction, and (3) a write !• Example: A = A-100!T1:A := A-100; B := B+100; !Read(A); // R(A) A := A – 100; Write(A); // W(A) T2:A := A+50; B := B-50; !Lec 18.16!4/4! Ion Stoica CS162 ©UCB Spring 2011!Example (contʼd)"• Database only sees reads and writes!• Assume initially: A = $1000 and B = $500!• What is the legal outcome of running T1 and T2?!– A = $950!– B = $550 !T1:R(A),W(A),R(B),W(B)!T1: A:=A-100; B:=B+100; !!T2:R(A),W(A),R(B),W(B)!T2: A:=A+50; B:=B-50;
View Full Document
Unlocking...