Crash Recovery Chapter 18 Database Management Systems 3

Crash Recovery Chapter 18 Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 1

Review: The ACID properties v A tomicity: All actions in the transaction happen, or none happen. v v C onsistency: If each transaction is consistent, and the DB starts consistent, it ends up consistent. I solation: Execution of one transaction is isolated from that of other transactions. v D urability: v The Recovery Manager guarantees Atomicity & Durability. If a transaction commits, its effects persist. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 2

Motivation v Atomicity: § Transactions may abort (“Rollback”). v Durability: § What if DBMS stops running? (Causes? ) v Desired Behavior after system restarts: – T 1, T 2 & T 3 should be durable. – T 4 & T 5 should be aborted (effects not seen). T 1 T 2 T 3 T 4 T 5 Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke crash! 3

Assumptions v Concurrency control is in effect. § Strict 2 PL, in particular. v Updates are happening “in place”. § i. e. data is overwritten on (deleted from) the disk. § No versioning. v A simple scheme to guarantee Atomicity & Durability? Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 4

Handling the Buffer Pool v Force every write to disk? § Poor response time. § But provides durability. No Steal § If not, we have good response time, but how can we ensure Force Trivial durability? v Steal buffer-pool frames from uncommited transactions? No Force § If not, poor throughput. § If so, we have good throughput, but how can we ensure atomicity? Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke Steal Desired 5

More on Steal and Force v STEAL (why enforcing Atomicity is hard) § To steal frame F, current page in F (say P) is written to disk; some transaction holds lock on P. • What if the transaction with the lock on P aborts? • Must remember the old value of P at steal time (to support UNDOing the write to page P). v NO FORCE (why enforcing Durability is hard) § What if system crashes before a modified page is written to disk? • Write as little as possible, in a convenient place, at commit time, to support REDOing modifications. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 6

Basic Idea: Logging v Record REDO and UNDO information, for every update, in a log. § Sequential writes to log (put it on a separate disk). § Minimal info (diff) written to log, so multiple updates fit in a single log page. v Log: An ordered list of records of done actions § Log record contains: <XID, page. ID, offset, length, old data, new data> § and additional control info (which we’ll see soon). § Actions to be logged: Page update (i. e. , write), Commit, Abort, End, Undoing an update Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 7

Write-Ahead Logging (WAL) v The Write-Ahead Logging Protocol: 1. Must force the log record for an update before the corresponding data page gets to disk. 2. Must write all log records for a transaction before commit. #1 guarantees Atomicity. v #2 guarantees Durability. v The ARIES algorithms will tell exactly how logging (and recovery!) is done? v Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 8

WAL & the Log v DB LSNs page. LSNs RAM flushed. LSN Each log record has a unique Log Sequence Log records Number (LSN). flushed to disk § LSNs always increasing. v Each data page contains a page. LSN. § The LSN of the most recent log record for an update to that page. v System keeps track of flushed. LSN. § The max LSN flushed so far. v WAL: Before a page is written, § page. LSN £ flushed. LSN Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke page. LSN “Log tail” in RAM To be flushed to disk 9

Log Records Log. Record fields: update records only prev. LSN XID type page. ID length offset before-image after-image Possible log record types: v Update v Commit v Abort v End (signifies end of commit or abort) v Compensation Log Records (CLRs) § for UNDO actions Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 10

Other Log-Related State v Transaction Table: § One entry per active transaction. § Contains XID, status (running/commited/aborted), and last. LSN. v Dirty Page Table: § One entry per dirty page in buffer pool. § Contains rec. LSN -- the LSN of the log record which first caused the page to be dirty. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 11

Normal Execution of an transaction v Series of reads & writes, followed by commit or abort. § We will assume that write is atomic on disk. • In practice, additional details to deal with non-atomic writes. v Strict 2 PL. v STEAL, NO-FORCE buffer management, with Write- Ahead Logging. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 12

The Big Picture: What’s Stored Where LOG DB Log. Records prev. LSN XID type page. ID length offset before-image after-image Data pages each with a page. LSN master record Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke RAM transaction Table last. LSN status Dirty Page Table rec. LSN flushed. LSN 13

Checkpointing v Periodically, the DBMS creates a checkpoint, in order to minimize the time taken to recover in the event of a system crash. Write to log: § begin_checkpoint record: Indicates when chkpt began. § end_checkpoint record: Contains current transaction table and dirty page table. This is a `fuzzy checkpoint’: • Other transactions continue to run; so these tables accurate only as of the time of the begin_checkpoint record. • No attempt to force dirty pages to disk; effectiveness of checkpoint limited by oldest unwritten change to a dirty page. (So it’s a good idea to periodically flush dirty pages to disk!) § Store LSN of chkpt record in a safe place (master record). Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 14

Compensating Log Records v Written just before the change documented in an update log record U is undone. § Has an extra field: undone. Next. LSN: LSN of the next log record to be undone for the transaction that did the update U. § undone. Next. LSN is set to be the value of prev. LSN of U. § Other fields are: trans. ID, page. ID, length, offset, and beforeimage from U. § CLR records the undo action of reversing the change U. § CLRA describes an action that will never be undone! Reason: xc CLR describes an action whose decision to be aborted has already been made. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 15

Simple Transaction Abort v For now, consider an explicit abort of a transaction. § No crash involved. v We want to “play back” the log in reverse order, UNDOing updates. § Get last. LSN of transaction from transaction table. § Can follow chain of log records backward via the prev. LSN field. § Before starting UNDO, write an Abort log record. • For recovering from crash during UNDO! Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 16

Abort, cont. v To perform UNDO, must have a lock on data! § No problem! v Before restoring old value of a page, write a CLR: § You continue logging while you UNDO!! § CLR has one extra field: undonext. LSN • Points to the next LSN to undo (i. e. the prev. LSN of the record we’re currently undoing). § CLRs never Undone (but they might be Redone when repeating history: guarantees Atomicity!) v At end of UNDO, write an “end” log record. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 17

Transaction Commit Write commit record to log. v All log records up to transaction’s last. LSN are flushed. v § Guarantees that flushed. LSN ³ last. LSN. § Note that log flushes are sequential, synchronous writes to disk. § Many log records per log page. Commit() returns. v Write end record to log. v Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 18

Crash Recovery: Big Picture Oldest log rec. of transaction active at crash Smallest rec. LSN in dirty page table after Analysis Start from a checkpoint (found via master record). v Three phases. Need to: v Last chkpt CRASH A R U – Figure out which transactions committed since checkpoint, which failed (Analysis). – REDO all actions. u (repeat history) – UNDO effects of failed transactions. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 19

Recovery: The Analysis Phase v Reconstruct state at checkpoint. § via end_checkpoint record. v Scan log forward from checkpoint. § End record: Remove transaction from transaction table. § Other records: Add transaction to transaction table, set last. LSN=LSN, change transaction status on commit. § Update record: If P not in Dirty Page Table, • Add P to D. P. T. , set its rec. LSN=LSN. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 20

Recovery: The REDO Phase v We repeat History to reconstruct state at crash: § Reapply all updates (even of aborted transactions!), redo CLRs. v Scan forward from log rec containing smallest rec. LSN in D. P. T. For each CLR or update log rec LSN, REDO the action unless: § Affected page is not in the Dirty Page Table, or § Affected page is in D. P. T. , but has rec. LSN > LSN, or § page. LSN (in DB) ³ LSN. v To REDO an action: § Reapply logged action. § Set page. LSN to LSN. No additional logging! Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 21

Recovery: The UNDO Phase To. Undo={ l | l a last. LSN of a “loser” transaction} Repeat: § Choose largest LSN among To. Undo. § If this LSN is a CLR and undonext. LSN==NULL • Write an End record for this transaction. § If this LSN is a CLR, and undonext. LSN != NULL • Add undonext. LSN to To. Undo § Else this LSN is an update. Undo the update, write a CLR, add prev. LSN to To. Undo. Until To. Undo is empty. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 22

Example of Recovery LSN RAM transaction Table last. LSN status Dirty Page Table rec. LSN flushed. LSN To. Undo LOG 00 begin_checkpoint 05 end_checkpoint 10 update: T 1 writes P 5 20 update T 2 writes P 3 30 T 1 abort 40 CLR: Undo T 1 LSN 10 45 T 1 End 50 update: T 3 writes P 1 60 update: T 2 writes P 5 prev. LSNs CRASH, RESTART Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 23

Example: Crash During Restart! LSN 00, 05 RAM transaction Table last. LSN status Dirty Page Table rec. LSN flushed. LSN To. Undo LOG begin_checkpoint, end_checkpoint 10 update: T 1 writes P 5 20 update T 2 writes P 3 30 T 1 abort 40, 45 undonext. LSN CLR: Undo T 1 LSN 10, T 1 End 50 update: T 3 writes P 1 60 update: T 2 writes P 5 CRASH, RESTART 70 80, 85 CLR: Undo T 2 LSN 60 CLR: Undo T 3 LSN 50, T 3 end CRASH, RESTART 90 CLR: Undo T 2 LSN 20, T 2 end Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 24

Additional Crash Issues What happens if system crashes during Analysis? During REDO? v How do you limit the amount of work in REDO? v § Flush asynchronously in the background. § Watch “hot spots”! v How do you limit the amount of work in UNDO? § Avoid long-running transactions. Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 25

Summary of Logging/Recovery Manager guarantees Atomicity & Durability. v Use WAL to allow STEAL/NO-FORCE w/o sacrificing correctness. v LSNs identify log records; linked into backwards chains per transaction (via prev. LSN). v page. LSN allows comparison of data page and log records. v Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 26

Summary, Cont. Checkpointing: A quick way to limit the amount of log to scan on recovery. v Recovery works in 3 phases: v § Analysis: Forward from checkpoint. § Redo: Forward from oldest rec. LSN. § Undo: Backward from end to first LSN of oldest transaction alive at crash. Upon Undo, write CLRs. v Redo “repeats history”: Simplifies the logic! v Database Management Systems, 3 ed, R. Ramakrishnan and J. Gehrke 27