Lecture 4: Transactions (Recovery) - PowerPoint PPT Presentation

kumiko
lecture 4 transactions recovery n.
Skip this Video
Loading SlideShow in 5 Seconds..
Lecture 4: Transactions (Recovery) PowerPoint Presentation
Download Presentation
Lecture 4: Transactions (Recovery)

play fullscreen
1 / 123
Download Presentation
Lecture 4: Transactions (Recovery)
75 Views
Download Presentation

Lecture 4: Transactions (Recovery)

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Lecture 4:Transactions (Recovery) Wednesday, October 19, 2011 Dan Suciu -- CSEP544 Fall 2011

  2. Homework 3 The key concepts here: • Connect to db and call SQL from java • Dependent joins • Integrate two databases • Transactions Amount of work: • 20 SQL queries+180 lines Java ≈ 12 hours (?)

  3. Outline • Transaction basics • Recovery • Concurrency control (next lecture) Dan Suciu -- CSEP544 Fall 2011

  4. Reading Material for Lectures 4/5 Textbook (Ramakrishnan): Ch. 16, 17, 18 Second textbook (Garcia-Molina) • Ch. 17.2, 17.3, 17.4 • Ch. 18.1, 18.2, 18.3, 18.8, 18.9 Optional: M. Franklin, Concurrency Control and Recovery

  5. Transaction Definition: a transaction is a unit of program execution that accesses/updates various data items • It consists of all operations between: BEGIN TRANSACTION END TRANSACTION • The collection of steps must appear to the user as a single, indivisible unit Dan Suciu -- CSEP544 Fall 2011

  6. Transaction May be omitted:first SQL querystarts txn BEGIN TRANSACTION [SQL statements] COMMIT or ROLLBACK (=ABORT) In ad-hoc SQL: each statement = one transaction Dan Suciu -- CSEP544 Fall 2011

  7. Turing Awards to Database Researchers • Charles Bachman 1973 for CODASYL • Edgar Codd 1981 for relational databases • Jim Gray 1998 for transactions Dan Suciu -- CSEP544 Fall 2011

  8. Implementing Transactions What can go wrong ? • System crash • Division by 0, power failure • Interferences with other users • “Anomalies” – 3 have famous names Dan Suciu -- CSEP544 Fall 2011

  9. System Crash Client 1: BEGIN TRANSACTION UPDATE Accounts SET balance= balance - 500WHERE name= ‘Fred’ UPDATE Accounts SET balance = balance + 500WHERE name= ‘Joe’ COMMIT Crash ! What can go wrong? Dan Suciu -- CSEP544 Fall 2011

  10. 1st Famous Anomaly:Lost Update Client 1: BEGIN TRANSACTION UPDATE CustomerSET rentals= rentals + 1WHEREcname= ‘Fred’ COMMIT Client 2: BEGIN TRANSACTION UPDATE CustomerSET rentals= rentals + 1WHEREcname= ‘Fred’ COMMIT What can go wrong ? Dan Suciu -- CSEP544 Fall 2011

  11. 2nd Famous Anomaly:Inconsistent Read Client 1: move from gizmogadget BEGIN TRANSACTION UPDATE Products SET quantity = quantity + 5WHERE product = ‘gizmo’ UPDATE Products SET quantity = quantity - 5WHERE product = ‘gadget’ COMMIT Client 2: inventory…. BEGIN TRANSACTION SELECT sum(quantity) FROMProduct COMMIT What can go wrong ? Dan Suciu -- CSEP544 Fall 2011

  12. 3rd Famous Anomaly:Dirty Reads -- Client 1: transfer $100 acc1 acc2 BEGIN TRANSACTIONX = Account1.balance; Account2.balance += 100 If (X>=100 and other stuff OK…) { Account1.balance -=100; COMMIT }else {print(“Denied !”); ROLLBACK} -- Client2: transfer $100 acc2 acc3 BEGIN TRANSACTIONX = Account2.balance; Account3.balance += 100 If (X>=100 and other stuff OK…) { Account2.balance -=100; COMMIT }else {print(“Denied !”); ROLLBACK} What can go wrong ?

  13. The Three Famous anomalies • Lost update • Two tasks T and T’ both modify the same data • T and T’ both commit • Final state shows effects of only T, but not of T’ • Dirty read • T reads data written by T’ while T’ has not committed • What can go wrong: T’ write more data (which T has already read), or T’ aborts • Inconsistent read • One task T sees some but not all changes made by T’

  14. ACID Properties • Atomic • State shows either all the effects of txn, or none of them • Consistent • Txn moves from a state where integrity holds, to another where integrity holds • Isolated • Effect of txns is the same as txns running one after another (ie looks like batch mode) • Durable • Once a txn has committed, its effects remain in the database Dan Suciu -- CSEP544 Fall 2011

  15. ACID: Atomicity • Two possible outcomes for a transaction • It commits: all the changes are made • It aborts: no changes are made • That is, transaction’s activities are all or nothing Dan Suciu -- CSEP544 Fall 2011

  16. ACID: Isolation • A transaction executes concurrently with other transaction • Isolation: the effect is as if each transaction executes in isolation of the others Dan Suciu -- CSEP544 Fall 2011

  17. ACID: Consistency • The database satisfies integrity constraints • Account numbers are unique • Stock amount can’t be negative • Sum of debits and of credits is 0 • Consistency = if the database satisfied the constraints at the beginning of the transaction, and if the application is written correctly, then the constraints must hold at the end of the transactions • Introduced as a requirement in the 70s, but today we understand it is a consequence of atomicity and isolation

  18. ACID: Durability • The effect of a transaction must continue to exists after the transaction, or the whole program has terminated • Means: write data to disk • Sometimes also means recovery Dan Suciu -- CSEP544 Fall 2011

  19. Reasons for Rollback • Explicit in the application • E.g. use it freely in HW 3 • System-initiated abort • System crash • Housekeeping, e.g. due to timeouts Dan Suciu -- CSEP544 Fall 2011

  20. Outline • Recovery from failures (the A in ACID) • Today • Concurrency Control (the C in ACID) • Next lecture Dan Suciu -- CSEP544 Fall 2011

  21. Log-based Recovery Simple recovery algorithms (Garcia-Molina Ch. 17.2, 17.3, 17.4) • Undo logging • Redo logging • Redo/undo logging State of the art (Ramakrishnan Ch. 18) • Aries Dan Suciu -- CSEP544 Fall 2011

  22. DB Buffer Management in a DBMS Application (Database server) READ WRITE BUFFER POOL disk page free frame INPUT OUTUPT Large gap between disk I/O and memory  Buffer pool

  23. Page Replacement Policies • LRU = expensive • Next slide • Clock algorithm = cheaper alternative • Read in the book Both work well in OS, but not always in DB Dan Suciu -- CSEP544 Fall 2011

  24. Least Recently Used (LRU) Most recent Least recent P5, P2, P8, P4, P1, P9, P6, P3, P7 Read(P6) ?? Dan Suciu -- CSEP544 Fall 2011

  25. Least Recently Used (LRU) Most recent Least recent P5, P2, P8, P4, P1, P9, P6, P3, P7 Read(P6) P6, P5, P2, P8, P4, P1, P9, P3, P7 Dan Suciu -- CSEP544 Fall 2011

  26. Least Recently Used (LRU) Most recent Least recent P5, P2, P8, P4, P1, P9, P6, P3, P7 Read(P6) P6, P5, P2, P8, P4, P1, P9, P3, P7 Read(P10) ?? Dan Suciu -- CSEP544 Fall 2011

  27. Least Recently Used (LRU) Most recent Least recent P5, P2, P8, P4, P1, P9, P6, P3, P7 Read(P6) P6, P5, P2, P8, P4, P1, P9, P3, P7 Input(P10) Read(P10) P10, P6, P5, P2, P8, P4, P1, P9, P3 Dan Suciu -- CSEP544 Fall 2011

  28. Recovery

  29. Transactions • Assumption: the database is composed of elements • Usually 1 element = 1 block • Can be smaller (=1 record) or larger (=1 relation) • Assumption: each transaction consists of a sequence of reads/writes of elements Dan Suciu -- CSEP544 Fall 2011

  30. Primitive Operations of Transactions • READ(X,t) • copy element X to transaction local variable t • WRITE(X,t) • copy transaction local variable t to element X • INPUT(X) • read element X to memory buffer • OUTPUT(X) • write element X to disk Dan Suciu -- CSEP544 Fall 2011

  31. BEGIN TRANSACTION READ(A,t); t := t*2; WRITE(A,t); READ(B,t); t := t*2; WRITE(B,t) COMMIT; Example Atomicity: Both A and Bare multiplied by 2,or none is. Dan Suciu -- CSEP544 Fall 2011

  32. READ(A,t); t := t*2; WRITE(A,t); READ(B,t); t := t*2; WRITE(B,t) Transaction Buffer pool Disk

  33. Is this bad ? Crash !

  34. Yes it’s bad: A=16, B=8…. Is this bad ? Crash !

  35. Is this bad ? Crash !

  36. Yes it’s bad: A=B=16, but not committed Is this bad ? Crash !

  37. Is this bad ? Crash !

  38. No: that’s OK Is this bad ? Crash !

  39. Atomic Transactions • FORCE or NO-FORCE • Should all updates of a transaction be forced to disk before the transaction commits? • STEAL or NO-STEAL • Can an update made by an uncommitted transaction overwrite the most recent committed value of a data item on disk? Dan Suciu -- CSEP544 Fall 2011

  40. Force/No-steal • FORCE: Pages of committed transactions must be forced to disk before commit • NO-STEAL: Pages of uncommitted transactions cannot be written to disk Easy to implement (how?) and ensures atomicity Dan Suciu -- CSEP544 Fall 2011

  41. No-Force/Steal • NO-FORCE: Pages of committed transactions need not be written to disk • STEAL: Pages of uncommitted transactions may be written to disk In either case, Atomicity is violated; need WAL Dan Suciu -- CSEP544 Fall 2011

  42. Write-Ahead Log The Log: append-only file containing log records • Enables NO-FORCE and STEAL • Records every single action of every TXN • Force log entry to disk • After a system crash, use log to recover Three types: UNDO, REDO, UNDO-REDO Dan Suciu -- CSEP544 Fall 2010

  43. UNDO Log FORCEand STEAL Dan Suciu -- CSEP544 Fall 2011

  44. Undo Logging Log records • <START T> • transaction T has begun • <COMMIT T> • T has committed • <ABORT T> • T has aborted • <T,X,v> • T has updated element X, and its old value was v Dan Suciu -- CSEP544 Fall 2011

  45. Crash ! WHAT DO WE DO ?

  46. Crash ! WHAT DO WE DO ? We UNDO by setting B=8 and A=8

  47. Crash ! What do we do now ?

  48. Crash ! What do we do now ? Nothing: log contains COMMIT

  49. After Crash • In the first example: • We UNDO both changes: A=8, B=8 • The transaction is atomic, since none of its actions has been executed • In the second example • We don’t undo anything • The transaction is atomic, since both it’s actions have been executed Dan Suciu -- CSEP544 Fall 2011