Transactions and concurrency Introduction to databases CSCC43 - PowerPoint PPT Presentation

Transactions and concurrency Introduction to databases CSCC43 Winter 2013 Ryan Johnson Thanks to Arnold Rosenbloom and Renee Miller for material in these slides

2 Transactions: an old idea • Database models business transactions – Customer pays, shopkeeper hands over goods – Bank gives money, debits account – Traveler reserves seat, airline doesn’t sell it to others – etc. • Key: transactions are all-or-nothing – Customer pays, shopkeeper keeps goods • Called a rip-off or scam (not a transaction) – Customer pays nothing, shopkeeper hands over goods • Usually called theft or robbery (again, not a transaction) Transactions protect/coordinate business dealings

3 Transactions: a programming model • Manage information rather than goods – Concerned with reads and updates to database objects … and how those changes propagate through the system • All-or-nothing execution – On success, commit : apply outstanding changes atomically – On failure, abort : outstanding changes rolled back (undone) • Some finer-grained control – Savepoint: back up instead of starting over on error – Nested transactions: compose small-scale operations

4 Transactions vs. other languages • C – Error detection: manual (always check return codes) – Partial changes: manual undo (very hard!) • Java/python/etc. – Error detection: try…catch – Error recovery: manual undo (still very hard!) • Transaction – Error detection: automatic abort unless overridden – Error recovery: rollback of all partial changes (no confusion)

5 Examples: error detection, recovery // C language // Java int do_something(int input) { void do_something(int input) { void* ptr = malloc(input); char[] tmp = new char[input]; int err = do_some_work(ptr); do_some_work(buf); if(err) { try { free(ptr); // never mind do_some_more_work(); return err; } catch(Exception e) { } // undo some_work? err = do_some_more_work(); throw; if(err) { } // undo some_work? } // free ptr? return err; // Transactional } void do_something(int input) { return 0; char[] tmp = new char[input]; } do_some_work(buf); do_some_more_work(); }

6 Transaction pros and cons • Pro: clean code (previous slides) • Pro: well-suited to concurrent execution (next) • Pro: transactions compose – E.g. wrap two smaller transactions in an outer one • Con: transaction footprint tends to “snowball” – E.g. searching a linked list uses only 1-2 nodes at a time … but transaction remembers all nodes (bad for concurrency) • Con: some actions can’t be rolled back – Fire missiles, dispense cash, ship package, overwrite file, … • Con: transaction tracks all updates – Irrelevant: no need to undo (e.g. temp variables) – Independent: should not undo (e.g. caching)

7 Properties of transactions in DBMS • The infamous ACID properties – A tomic (updates applied in all-or-nothing fashion) – C onsistent (committed state always “makes sense”) – I solated (transactions can’t see each others’ updates) – D urable (committed updates never lost) Note: C and I are closely related

8 Concurrent transactions • Problem: serial execution is slow – Imagine a grocery store with only one checkout lane – Or a bus with only one seat • Alternative: concurrent execution – Allow “non - conflicting” requests to proceed in parallel – Exact definition of “conflicting” application -dependent ... but same basic rules usually apply

9 Dealing with concurrent updates • Semantics when transactions overlap? • Ideal: coherence – Reads always return the most recently-written value – Writes always replace older values – Property of individual objects • Ideal: consistency – Reads and writes appear in program order – OK to interleave different transactions, but not reorder them – Property of system as a whole (or at least sets of objects)

10 Example: incoherent execution Time Process A Process B x = 10 x = 20 x += 5 Read stale print x version of x “15” Older version print x persists “20” after update A and B not using the same definition of “time”

11 Example: inconsistent execution Time Process A Process B x = 10 y= 1 y = 5 y assigned 5 x = 20 before print x,y x assigned 20 “20, 1” Broken no matter print x,y where in time “10,5” we put B’s updates Above implies reordering of operations in A or B

12 Serializable execution • The observed sequence of reads and writes corresponds to some non-interleaved (serial) execution of the transactions/processes Process A Process B x = 10 x = 20 What change to the x += 5 schedule serializes A and B? print x “15” print x “20” Always preserves both coherence and consistency

13 What can go wrong in parallel? • Phenomenon: an interleaving of actions (schedule) where “something” might go wrong – Write-after-write (uncommitted value overwritten) – Read-after- write (uncommitted write “leaked”) – Write-after-read (in-use value overwritten) • Anomaly: a schedule where “something” did go wrong (the phenomenon affected correctness) – two people reserve the same theater seat – airfare goes up while you’re trying to buy it

14 Phenomena and related anomalies P – Write after Write (WAW) A – dirty write w 1 (x) w 2 (x) ... c 2 c 1 Lost committed write by T 1 P – Read after Write (RAW) A1 – dirty read w 1 (x) r 2 (x) ... [a 1 and c 2 ] T 2 uses rolled-back value of x A2 – read skew r 1 (x) w 2 (x) w 2 (y) c 2 r 1 (y) T 1 sees old x and new y P – Write after Read (WAR) A1 – fuzzy read r 1 (x) w 2 (x) ... r 1 (x) Value of x not stable during xct A2 – lost update r 1 (x) w 2 (x) ... w 1 (x) T 1 ’ s update depends on stale x A3 – phantom r 1 (P) w 2 (add x to P) ... c 2 P as originally read is missing x P – snapshot-related A1 – write skew r 1 (x) r 2 (y) w 1 (y) w 2 (x) [c 1 and c 2 ] Two updates cross in flight A2 – read-only r 2 (x) r 1 (y) w 1 (y) c 1 r 3 (x) r 3 (y) c 3 T 3 sees mix of past/future data w 2 (x) c 2

15 Examples: phenomena and anomalies • Dirty write (WAW) – You make offer on a house but the Realtor waits several days before responding that somebody else made a higher offer. • Dirty read (RAW) – Your friend invites you to go with her to a concert; you buy a ticket; she changes her mind and decides not to go after all. No refunds. • Read skew (RAW) – You park for three minutes in 10-minute parking to hand in an assignment; five minutes down the road you realize that part of the assignment is still in the car, so you turn around and park again to hand in the rest. When you come back three minutes later you have a parking ticket. • Fuzzy read (WAR) – You grab the last tag for a hot item at a Boxing Day doorbuster sale, but the cashier says it’s actually out of stock (has actually happened to me)

16 Examples: phenomena and anomalies • Lost update (WAR) – You save a minor edit to your project report, not realizing that your project partner saved major edits since you last opened the file. • Phantom (WAR) – You buy a new textbook from the bookstore because you couldn’t find a used copy; meanwhile 100 cheap used copies go on sale. • Write skew (snapshot) – You agree to go out with some friends on Friday because your ex- girlfriend is *not* coming; meanwhile, she decides to go because she thinks you won’t be there. • Read-only anomaly (snapshot) – End of month, no money. Rent deduction hits checking, tax return hits savings. You log into online banking, see the tax return but not rent, and assume overdraft protection will kick when the rent arrives. Bank says the rent arrived first and charges a $20 overdraft fee. The timestamps in the database show the rent arrived before you made your printout.

17 Weakened consistency models • Repeatable read (SQL) – Allows phantoms • Read committed (SQL) – Allows WAR anomalies • Read uncommitted (SQL) – Allows RAW and WAR anomalies • Snapshot isolation (Oracle) – Allows snapshot anomalies and phantoms • Cursor stability (IBM) – Allows lost updates • Eventual consistency (internet) – Only guarantee: values read were written at some point in the past – Just about anything else can happen (WAW, lost updates, skew...)