Review: The ACID properties A tomicity: All actions in the Xact - PDF document

Review: The ACID properties ❖ A tomicity: All actions in the Xact happen, or none happen. ❖ C onsistency: If each Xact is consistent, and the DB starts Crash Recovery consistent, it ends up consistent. ❖ I solation: Execution of one Xact is isolated from that of Chapter 20 other Xacts. ❖ D urability: If a Xact commits, its effects persist. If you are going to be in the logging business, one of the things that you have to do is to learn about heavy equipment. ❖ The Recovery Manager guarantees Atomicity & Durability. Robert VanNatta, Logging History of Columbia County Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 1 2 Motivation Assumptions ❖ Atomicity: ❖ Concurrency control is in effect. – Transactions may abort (“Rollback”). – Strict 2PL, in particular. ❖ Durability: ❖ Updates are happening “in place”. – What if DBMS stops running? (Causes?) – i.e. data is overwritten on (deleted from) the disk. ❖ Desired Behavior after crash! system restarts: T1 ❖ A simple scheme to guarantee Atomicity & – T1, T2 & T3 should be T2 Durability? durable. T3 – T4 & T5 should be T4 aborted (effects not seen). T5 Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 3 4 Handling the Buffer Pool More on Steal and Force ❖ STEAL (why enforcing Atomicity is hard) ❖ Force every write to disk? – To steal frame F: Current page in F (say P) is – Poor response time. No Steal Steal written to disk; some Xact holds lock on P. – But provides durability. ◆ What if the Xact with the lock on P aborts? Force Trivial ❖ Steal buffer-pool frames ◆ Must remember the old value of P at steal time (to support UNDOing the write to page P). from uncommited Xacts? ❖ NO FORCE (why enforcing Durability is hard) – If not, poor throughput. Desired No Force – What if system crashes before a modified page is – If so, how can we ensure written to disk? atomicity? – Write as little as possible, in a convenient place, at commit time,to support REDOing modifications. Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 5 6

Basic Idea: Logging Write-Ahead Logging (WAL) ❖ The Write-Ahead Logging Protocol: ❖ Record REDO and UNDO information, for € Must force the log record for an update before the every update, in a log. corresponding data page gets to disk. – Sequential writes to log (put it on a separate disk). � Must write all log records for a Xact before commit . – Minimal info (diff) written to log, so multiple ❖ #1 guarantees Atomicity. updates fit in a single log page. ❖ #2 guarantees Durability. ❖ Log: An ordered list of REDO/UNDO actions – Log record contains: <XID, pageID, offset, length, old data, new data> ❖ Exactly how is logging (and recovery!) done? – and additional control info (which we’ll see soon). – We’ll study the ARIES algorithms. Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 7 8 DB RAM WAL & the Log Log Records LSNs pageLSNs flushedLSN ❖ Each log record has a unique Log Sequence Possible log record types: Number (LSN). LogRecord fields: ❖ Update Log records flushed to disk – LSNs always increasing. prevLSN ❖ Commit XID ❖ Each data page contains a pageLSN. ❖ Abort type – The LSN of the most recent log record pageID ❖ End (signifies end of for an update to that page. length commit or abort) update ❖ System keeps track of flushedLSN. offset records ❖ Compensation Log – The max LSN flushed so far. before-image pageLSN only “Log tail” Records (CLRs) after-image in RAM ❖ WAL: Before a page is written, – for UNDO actions – pageLSN ≤ flushedLSN Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 9 10 Other Log-Related State Normal Execution of an Xact ❖ Transaction Table: ❖ Series of reads & writes, followed by commit or abort. – One entry per active Xact. – Contains XID, status (running/commited/aborted), – We will assume that write is atomic on disk. and lastLSN. ◆ In practice, additional details to deal with non-atomic writes. ❖ Dirty Page Table: ❖ Strict 2PL. – One entry per dirty page in buffer pool. ❖ STEAL, NO-FORCE buffer management, with – Contains recLSN -- the LSN of the log record which Write-Ahead Logging. first caused the page to be dirty. Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 11 12

Checkpointing The Big Picture: What’s Stored Where ❖ Periodically, the DBMS creates a checkpoint, in LOG order to minimize the time taken to recover in the RAM DB event of a system crash. Write to log: LogRecords Xact Table – begin_checkpoint record: Indicates when chkpt began. prevLSN Data pages lastLSN XID – end_checkpoint record: Contains current Xact table and each status type dirty page table . This is a `fuzzy checkpoint’: with a pageID pageLSN Dirty Page Table ◆ Other Xacts continue to run; so these tables accurate only as of length the time of the begin_checkpoint record. recLSN offset master record ◆ No attempt to force dirty pages to disk; effectiveness of before-image flushedLSN checkpoint limited by oldest unwritten change to a dirty page. after-image (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, 2 nd Edition. R. Ramakrishnan and J. Gehrke Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 13 14 Simple Transaction Abort Abort, cont. ❖ For now, consider an explicit abort of a Xact. ❖ To perform UNDO , must have a lock on data! – No crash involved. – No problem! ❖ We want to “play back” the log in reverse ❖ Before restoring old value of a page, write a CLR: order, UNDO ing updates. – You continue logging while you UNDO!! – Get lastLSN of Xact from Xact table. – CLR has one extra field: undonextLSN – Can follow chain of log records backward via the prevLSN field. ◆ Points to the next LSN to undo (i.e. the prevLSN of the record we’re currently undoing). – Before starting UNDO, write an Abort log record. – CLRs never Undone (but they might be Redone when ◆ For recovering from crash during UNDO! repeating history: guarantees Atomicity!) ❖ At end of UNDO , write an “end” log record. Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 15 16 Transaction Commit Crash Recovery: Big Picture Oldest log ❖ Write commit record to log. rec. of Xact ❖ Start from a checkpoint (found active at crash via master record). ❖ All log records up to Xact’s lastLSN are flushed. Smallest ❖ Three phases. Need to: recLSN in – Guarantees that flushedLSN ≥ lastLSN. – Figure out which Xacts dirty page table after committed since checkpoint, – Note that log flushes are sequential, synchronous Analysis which failed (Analysis). writes to disk. – REDO all actions. – Many log records per log page. ◆ (repeat history) Last chkpt ❖ Commit() returns. – UNDO effects of failed Xacts. ❖ Write end record to log. CRASH A R U Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 17 18