CSE 544 Transactions Recovery Wednesday 4192006 Transactions Major

CSE 544: Transactions Recovery Wednesday, 4/19/2006

Transactions • Major component of database systems • Critical for most applications; arguably more so than SQL • Turing awards to database researchers: – Charles Bachman 1973 – Edgar Codd 1981 for inventing relational dbs – Jim Gray 1998 for inventing transactions 2

Why Do We Need Transactions • Concurrency control • Recovery 3

Concurrency Control Client 1: UPDATE Product SET Price = Price – 1. 99 WHERE pname = ‘Gizmo’ Client 2: UPDATE Product SET Price = Price*0. 5 WHERE pname=‘Gizmo’ What’s wrong ? Lost update 4

Concurrency Control Client 1: INSERT INTO Small. Product(name, price) SELECT pname, price FROM Product WHERE price <= 0. 99 DELETE Product WHERE price <=0. 99 Client 2: SELECT count(*) FROM Product SELECT count(*) FROM Small. Product What’s wrong ? Inconsistent reads 5

Concurrency Control Client 1: UPDATE SET Account. amount = 100000 WHERE Account. number = ‘my-account’ Aborted by system Client 2: SELECT Account. amount FROM Account WHERE Account. number = ‘my-account’ What’s wrong ? Dirty reads 6

Protection against crashes Client 1: INSERT INTO Small. Product(name, price) SELECT pname, price FROM Product WHERE price <= 0. 99 Crash ! DELETE Product WHERE price <=0. 99 What’s wrong ? 7

Definition • A transaction = one or more operations, single real-world transition • Examples – Transfer money between accounts – Purchase a group of products – Register for a class (either waitlist or allocated) 8

Transactions in SQL • In “ad-hoc” SQL: – Default: each statement = one transaction • In a program: May be omitted: first SQL query starts txn START TRANSACTION [SQL statements] COMMIT or ROLLBACK (=ABORT) 9

Revised Code Client 1: START TRANSACTION UPDATE Product SET Price = Price – 1. 99 WHERE pname = ‘Gizmo’ COMMIT Client 2: START TRANSACTION UPDATE Product SET Price = Price*0. 5 WHERE pname=‘Gizmo’ COMMIT 10

ROLLBACK • If the app gets to a place where it can’t complete the transaction successfully, it can execute ROLLBACK • This causes the system to “abort” the transaction – The database returns to the state without any of the previous changes made by activity of the transaction 11

Reasons for Rollback • User changes their mind (“ctl-C”/cancel) • Explicit in program, when app program finds a problem – e. g. when qty on hand < qty being sold • System-initiated abort – System crash – Housekeeping • e. g. due to timeouts 12

Transaction Properties ACID • Atomic • Consistent • Isolated • Durable 13

Atomic • Two possible outcomes for a transaction – It commits: all the changes are made – It aborts: no changes are made What it means for us: recovery 14

Consistent • The state of the database is restricted by integrity constraints • Constraints may be explicit or implicit • A transaction must take a database from a consistent state to a consistent state What it means for us: nothing, follows from Atomicity and Isolation 15

Isolated • A transaction executes concurrently with other transaction • Its effect must be as if each transaction executes in isolation of the others What it means for us: concurrency control 16

Durable • The effect of a transaction must continue to exists after the transaction, or the whole program has terminated What it means for us: write data to disk Note: some papers/books interpret this as need for recovery 17

Transactions in SQL • Isolation level “serializable” (i. e. ACID) – Golden standard – But often too inefficient • Weaker isolation levels – Sacrifice correctness for efficiency – Often used in practice – Sometimes are hard to understand 18

READ-ONLY Transactions Client 1: START TRANSACTION INSERT INTO Small. Product(name, price) SELECT pname, price FROM Product WHERE price <= 0. 99 DELETE Product WHERE price <=0. 99 COMMIT Client 2: SET TRANSACTION READ ONLY START TRANSACTION SELECT count(*) FROM Product SELECT count(*) FROM Small. Product COMMIT Makes it faster 19

Isolation Levels in SQL 1. “Dirty reads” SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 2. “Committed reads” SET TRANSACTION ISOLATION LEVEL READ COMMITTED 3. “Repeatable reads” SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 4. Serializable transactions (default): SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 20

Isolation Level: Dirty Reads Plane seat allocation function Allocate. Seat( %request) SET ISOLATION LEVEL READ UNCOMMITED START TRANSACTION Let x = What can go wrong ? SELECT Seat. occupied FROM Seat WHERE Seat. number = %request If (x == 1) /* occupied */ ROLLBACK What can go wrong if only the function Allocate. Seat modifies Seat ? UPDATE Seat SET occupied = 1 WHERE Seat. number = %request COMMIT 21

function Transfer. Money( %amount, %acc 1, %acc 2) START TRANSACTION Are dirty reads OK here ? Let x = SELECT Account. balance FROM Account WHERE Account. number = %acc 1 If (x < %amount) ROLLBACK What if we switch the two updates ? UPDATE Account SET balance = balance+%amount WHERE Account. number = %acc 2 UPDATE Account SET balance = balance-%amount WHERE Account. number = %acc 1 COMMIT 22

Isolation Level: Read Committed Stronger than READ UNCOMMITTED SET ISOLATION LEVEL READ COMMITED Let x = It is possible to read twice, and get different values SELECT Seat. occupied FROM Seat WHERE Seat. number = %request /*. . . More stuff here. . */ Let y = SELECT Seat. occupied FROM Seat WHERE Seat. number = %request /* we may have x y ! */ 23

Isolation Level: Repeatable Read Stronger than READ COMMITTED SET ISOLATION LEVEL REPEATABLE READ Let x = May see incompatible values: another txn transfers from acc. 55555 to 77777 SELECT Account. amount FROM Account WHERE Account. number = ‘ 555555’ /*. . . More stuff here. . */ Let y = SELECT Account. amount FROM Account WHERE Account. number = ‘ 777777’ /* we may have a wrong x+y ! */ 24

Isolation Level: Serializable Strongest level SET ISOLATION LEVEL SERIALIZABLE. . Default 25

The Mechanics of Disk Mechanical characteristics: • Rotation speed (5400 RPM) • Number of platters (1 -30) • Number of tracks (<=10000) • Number of bytes/track(105) Unit of read or write: disk block Once in memory: page Typically: 4 k or 8 k or 16 k Cylinder Disk head Spindle Tracks Sector Arm movement Arm assembly Platters 26

Disk Access Characteristics • Disk latency = time between when command is issued and when data is in memory • Disk latency = seek time + rotational latency – Seek time = time for the head to reach cylinder • 10 ms – 40 ms – Rotational latency = time for the sector to rotate • Rotation time = 10 ms • Average latency = 10 ms/2 • Transfer time = typically 40 MB/s • Disks read/write one block at a time 27

Buffer Management in a DBMS Page Requests from Higher Levels BUFFER POOL READ WRITE disk page free frame INPUT OUTUPT MAIN MEMORY DISK DB choice of frame dictated by replacement policy • Data must be in RAM for DBMS to operate on it! • Table of <frame#, pageid> pairs is maintained 28

Buffer Manager Needs to decide on page replacement policy • LRU • Clock algorithm Both work well in OS, but not always in DB Enables the higher levels of the DBMS to assume that the needed data is in main memory. 29

Least Recently Used (LRU) • Order pages by the time of last accessed • Always replace the least recently accessed P 5, P 2, P 8, P 4, P 1, P 9, P 6, P 3, P 7 Access P 6, P 5, P 2, P 8, P 4, P 1, P 9, P 3, P 7 LRU is expensive (why ? ); the clock algorithm is good approx 30

Buffer Manager Why not use the Operating System for the task? ? - DBMS may be able to anticipate access patterns - Hence, may also be able to perform prefetching -DBMS needs the ability to force pages to disk, for recovery purposes - need fine grained control for transactions 31

Recovery From which of the events below can a database actually recover ? • Wrong data entry • Disk failure • Fire / earthquake / bankrupcy / …. • Systems crashes 32

Recovery Most frequent Type of Crash Prevention Wrong data entry Constraints and Data cleaning Disk crashes Redundancy: e. g. RAID, archive Fire, theft, bankruptcy… Buy insurance, Change jobs… System failures: e. g. power DATABASE RECOVERY 33

Recovery from System Failures • Use a log – A file that records every single action of the transaction flush Log on disc Last entry Log tail in memory 34

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 reads/writes some elements 35

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 36

Example START TRANSACTION READ(A, t); t : = t*2; WRITE(A, t); READ(B, t); t : = t*2; WRITE(B, t) COMMIT; Atomicity: BOTH A and B are multiplied by 2 37

READ(A, t); t : = t*2; WRITE(A, t); READ(B, t); t : = t*2; WRITE(B, t) Transaction Action t INPUT(A) Buffer pool Mem A Disk Mem B Disk A Disk B 8 8 8 READ(A, t) 8 8 t: =t*2 16 8 8 8 WRITE(A, t) 16 16 8 8 INPUT(B) 16 16 8 8 8 READ(B, t) 8 16 8 8 8 t: =t*2 16 16 8 8 8 WRITE(B, t) 16 16 16 8 8 OUTPUT(A) 16 16 8 OUTPUT(B) 16 16 16 38

Action t INPUT(A) Mem A Mem B Disk A Disk B 8 8 8 READ(A, t) 8 8 t: =t*2 16 8 8 8 WRITE(A, t) 16 16 8 8 INPUT(B) 16 16 8 8 8 READ(B, t) 8 16 8 8 8 t: =t*2 16 16 8 8 8 WRITE(B, t) 16 16 16 8 8 OUTPUT(A) 16 16 8 OUTPUT(B) 16 16 Crash ! 16 Crash occurs after OUTPUT(A), before OUTPUT(B) We lose atomicity 39

The Log • An append-only file containing log records • Note: multiple transactions run concurrently, log records are interleaved • After a system crash, use log to: – Redo some transaction that didn’t commit – Undo other transactions that didn’t commit • Three kinds of logs: undo, redo, undo/redo 40

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 41

Action T Mem A Mem B Disk A Disk B Log <START T> INPUT(A) 8 8 8 READ(A, t) 8 8 t: =t*2 16 8 8 8 WRITE(A, t) 16 16 8 8 INPUT(B) 16 16 8 8 8 READ(B, t) 8 16 8 8 8 t: =t*2 16 16 8 8 8 WRITE(B, t) 16 16 16 8 8 OUTPUT(A) 16 16 8 OUTPUT(B) 16 16 16 COMMIT <T, A, 8> <T, B, 8> <COMMIT T> 42

Action T Mem A Mem B Disk A Disk B Log <START T> INPUT(A) 8 8 8 READ(A, t) 8 8 t: =t*2 16 8 8 8 WRITE(A, t) 16 16 8 8 INPUT(B) 16 16 8 8 8 READ(B, t) 8 16 8 8 8 t: =t*2 16 16 8 8 8 WRITE(B, t) 16 16 16 8 8 OUTPUT(A) 16 16 8 OUTPUT(B) 16 16 16 COMMIT <T, A, 8> <T, B, 8> Crash ! <COMMIT T> WHAT DO WE DO ? 43

Action T Mem A Mem B Disk A Disk B Log <START T> INPUT(A) 8 8 8 READ(A, t) 8 8 t: =t*2 16 8 8 8 WRITE(A, t) 16 16 8 8 INPUT(B) 16 16 8 8 8 READ(B, t) 8 16 8 8 8 t: =t*2 16 16 8 8 8 WRITE(B, t) 16 16 16 8 8 OUTPUT(A) 16 16 8 OUTPUT(B) 16 16 16 COMMIT <T, A, 8> <T, B, 8> <COMMIT T> WHAT DO WE DO ? Crash 44 !

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 45

Undo-Logging Rules U 1: If T modifies X, then <T, X, v> must be written to disk before OUTPUT(X) U 2: If T commits, then OUTPUT(X) must be written to disk before <COMMIT T> • Hence: OUTPUTs are done early, before the transaction commits 46

Action T Mem A Mem B Disk A Disk B Log <START T> INPUT(A) 8 8 8 READ(A, t) 8 8 t: =t*2 16 8 8 8 WRITE(A, t) 16 16 8 8 INPUT(B) 16 16 8 8 8 READ(B, t) 8 16 8 8 8 t: =t*2 16 16 8 8 8 WRITE(B, t) 16 16 16 8 8 OUTPUT(A) 16 16 8 OUTPUT(B) 16 16 16 COMMIT <T, A, 8> <T, B, 8> <COMMIT T> 47

Recovery with Undo Log After system’s crash, run recovery manager • Idea 1. Decide for each transaction T whether it is completed or not – <START T>…. <COMMIT T>…. = yes – <START T>…. <ABORT T>……. = yes – <START T>…………… = no • Idea 2. Undo all modifications by incomplete transactions 48

Recovery with Undo Log Recovery manager: • Read log from the end; cases: <COMMIT T>: mark T as completed <ABORT T>: mark T as completed <T, X, v>: if T is not completed then write X=v to disk else ignore <START T>: ignore 49

Recovery with Undo Log crash … … <T 6, X 6, v 6> … … <START T 5> <START T 4> <T 1, X 1, v 1> <T 5, X 5, v 5> <T 4, X 4, v 4> <COMMIT T 5> <T 3, X 3, v 3> <T 2, X 2, v 2> Question 1 in class: Which updates are undone ? Question 2 in class: How far back do we need to read in the log ? 50

Recovery with Undo Log • Note: all undo commands are idempotent – If we perform them a second time, no harm is done – E. g. if there is a system crash during recovery, simply restart recovery from scratch 51

Recovery with Undo Log When do we stop reading the log ? • We cannot stop until we reach the beginning of the log file • This is impractical Instead: use checkpointing 52

Checkpointing Checkpoint the database periodically • Stop accepting new transactions • Wait until all current transactions complete • Flush log to disk • Write a <CKPT> log record, flush • Resume transactions 53

Undo Recovery with Checkpointing During recovery, Can stop at first <CKPT> … … <T 9, X 9, v 9> … … (all completed) <CKPT> <START T 2> <START T 3 <START T 5> <START T 4> <T 1, X 1, v 1> <T 5, X 5, v 5> <T 4, X 4, v 4> <COMMIT T 5> <T 3, X 3, v 3> <T 2, X 2, v 2> other transactions T 2, T 3, T 4, T 5 54

Nonquiescent Checkpointing • Problem with checkpointing: database freezes during checkpoint • Would like to checkpoint while database is operational • Idea: nonquiescent checkpointing Quiescent = being quiet, still, or at rest; inactive Non-quiescent = allowing transactions to be active 55

Nonquiescent Checkpointing • Write a <START CKPT(T 1, …, Tk)> where T 1, …, Tk are all active transactions • Continue normal operation • When all of T 1, …, Tk have completed, write <END CKPT> 56

Undo Recovery with Nonquiescent Checkpointing During recovery, Can stop at first <CKPT> Q: why do we need <END CKPT> ? … … … <START CKPT T 4, T 5, T 6> … … <END CKPT> … … … earlier transactions plus T 4, T 5, T 6, plus later transactions 57

Redo 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 new value is v 58

Action T Mem A Mem B Disk A Disk B Log <START T> READ(A, t) 8 8 t: =t*2 16 8 8 8 WRITE(A, t) 16 16 8 8 READ(B, t) 8 16 8 8 8 t: =t*2 16 16 8 8 8 WRITE(B, t) 16 16 16 8 8 <T, A, 16> <T, B, 16> <COMMIT T> OUTPUT(A) 16 16 8 OUTPUT(B) 16 16 16 59

Redo-Logging Rules R 1: If T modifies X, then both <T, X, v> and <COMMIT T> must be written to disk before OUTPUT(X) • Hence: OUTPUTs are done late 60

Action T Mem A Mem B Disk A Disk B Log <START T> READ(A, t) 8 8 t: =t*2 16 8 8 8 WRITE(A, t) 16 16 8 8 READ(B, t) 8 16 8 8 8 t: =t*2 16 16 8 8 8 WRITE(B, t) 16 16 16 8 8 <T, A, 16> <T, B, 16> <COMMIT T> OUTPUT(A) 16 16 8 OUTPUT(B) 16 16 16 61

Recovery with Redo Log After system’s crash, run recovery manager • Step 1. Decide for each transaction T whether it is completed or not – <START T>…. <COMMIT T>…. = yes – <START T>…. <ABORT T>……. = yes – <START T>…………… = no • Step 2. Read log from the beginning, redo all updates of committed transactions 62

Recovery with Redo Log <START T 1> <T 1, X 1, v 1> <START T 2> <T 2, X 2, v 2> <START T 3> <T 1, X 3, v 3> <COMMIT T 2> <T 3, X 4, v 4> <T 1, X 5, v 5> … … 63

Nonquiescent Checkpointing • Write a <START CKPT(T 1, …, Tk)> where T 1, …, Tk are all active transactions • Flush to disk all blocks of committed transactions (dirty blocks), while continuing normal operation • When all blocks have been written, write <END CKPT> 64

Redo Recovery with Nonquiescent Checkpointing Step 1: look for The last <END CKPT> All OUTPUTs of T 1 are known to be on disk … <START T 1> … <COMMIT T 1> … <START T 4> … <START CKPT T 4, T 5, T 6> … … <END CKPT> … … … <START CKPT T 9, T 10> … Step 2: redo from the earliest start of T 4, T 5, T 6 ignoring transactions committed earlier 65

Comparison Undo/Redo • Undo logging: – OUTPUT must be done early – If <COMMIT T> is seen, T definitely has written all its data to disk (hence, don’t need to redo) – inefficient • Redo logging – OUTPUT must be done late – If <COMMIT T> is not seen, T definitely has not written any of its data to disk (hence there is not dirty data on disk, no need to undo) – inflexible • Would like more flexibility on when to OUTPUT: undo/redo logging (next) 66

Undo/Redo Logging Log records, only one change • <T, X, u, v>= T has updated element X, its old value was u, and its new value is v 67

Undo/Redo-Logging Rule UR 1: If T modifies X, then <T, X, u, v> must be written to disk before OUTPUT(X) Note: we are free to OUTPUT early or late relative to <COMMIT T> 68

Action T Mem A Mem B Disk A Disk B Log <START T> REAT(A, t) 8 8 t: =t*2 16 8 8 8 WRITE(A, t) 16 16 8 8 READ(B, t) 8 16 8 8 8 t: =t*2 16 16 8 8 8 WRITE(B, t) 16 16 16 8 8 OUTPUT(A) 16 16 8 <T, A, 8, 16> <T, B, 8, 16> <COMMIT T> OUTPUT(B) 16 16 16 Can OUTPUT whenever we want: before/after COMMIT 69

Recovery with Undo/Redo Log After system’s crash, run recovery manager • Redo all committed transaction, top-down • Undo all uncommitted transactions, bottom-up 70

Recovery with Undo/Redo Log <START T 1> <T 1, X 1, v 1> <START T 2> <T 2, X 2, v 2> <START T 3> <T 1, X 3, v 3> <COMMIT T 2> <T 3, X 4, v 4> <T 1, X 5, v 5> … … 71

The Log in Real Databases • Uses the ARIES algorithm • Same high level idea: undo/redo, lots of clever improvements (pointers between log entries etc) • ARIES is in the book - reading is optional • Franklin’s paper - reading is mandatory 72