Lecture 4: Transactions (Recovery)

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

46. Crash ! WHAT DO WE DO ?

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

48. Crash ! What do we do now ?

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

50. 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