Lecture 10 Logging Recovery ARIES Sept 28 2007 ChengXiang Zhai Most slides are adapted from Kevin Chang s lecture slides CS511 Advanced Database Management Systems 1 The ACID Properties of Transactions Atomicity Either all actions are done or none Consistency DB satisfies all the consistency constraints Transactions are expected to preserve consistency Isolation Is As if each transaction were executed alone Durability Once a transaction is completed its effect must persist Concurrency Lock Manager consistency isolation Recovery Log Manager atomicity durability CS511 Advanced Database Management Systems Motivation How to ensure atomicity and durability transactions may abort need to rollback what if DBMS stops running Desired status after system restarts T1 T2 T3 should be durable T4 T5 should be aborted effects not seen CS511 Advanced Database Management Systems T1 T2 T3 T4 T5 crash Assumptions Concurrency control is in effect strict 2PL in particular request s x locks before read write all the locks held until EOT strict locking Updates are happening in place no shadow pages Data is overwritten on or deleted from the disk A simple scheme to guarantee atomicity durability CS511 Advanced Database Management Systems Handling the Buffer Pool No Steal Force writing to disk at commit Force poor response time but provides durability Steal buffer pool frames from No Force uncommitted transactions if not inefficient use of the buffer if so how can we ensure atomicity Recovery scheme vs B M undo only can steal must force redo only no steal no force CS511 Advanced Database Management Systems Steal Trivial Desired Basic Idea Logging Record redo and undo information in 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 log ordered list of redo undo actions log record contains XID pageID offset length old data new data and additional control info which we ll see soon CS511 Advanced Database Management Systems Write Ahead Logging WAL Write Ahead Logging Protocol 1 guarantees atomicity undo Exactly how is logging and recovery done must force the log record for an update before the corresponding data page gets to disk must force all log records for a xact before commit 2 guarantees durability redo we ll study the ARIES algorithms CS511 Advanced Database Management Systems ARIES Main Principles WAL Enables Repeating history during REDO Logging changes during UNDO simplicity and flexibility finer granularity locking than a page updates to different parts of same page are streamed in redo undo redoing and undoing not necessarily exact physical inverse CS511 Advanced Database Management Systems 8 WAL the Log Each log record has unique Log Sequence Number LSNs always increasing Each data page contains a pageLSN Log records flushed to disk LSN of the most recent log record of latest update System keeps track of flushedLSN the max LSN flushed so far WAL before writing a page pageLSN flushedLSN pageLSN in flushed already CS511 Advanced Database Management Systems pageLSN Log tail in RAM Log Records Possible log record types LogRecord fields update records only prevLSN XID type pageID length offset before image after image CS511 Advanced Database Management Systems Update Commit Abort End end of commit or abort Compensation Log Records CLRs for UNDO actions Other Log Related State Transaction table one entry per active Xact contains XID status running committed aborted and lastLSN Dirty page table one entry per dirty page in buffer pool contains recLSN the LSN of the log record which first caused the page to be dirty CS511 Advanced Database Management Systems Normal Execution of an Xact Series of reads writes followed by commit or abort Strict 2PL STEAL NO FORCE buffer management with write ahead logging CS511 Advanced Database Management Systems Checkpointing Periodical checkpoint minimize the analysis time to recover from system crash Write to log begin checkpoint record indicates when chkpt began end checkpoint record contains current xact table and dirty page table Fuzzy checkpoint other xacts continue to run these tables accurate only as of the time of the begin checkpoint record no attempt to force dirty pages to disk effectiveness limited by earliest recLSN in dirty page table oldest unwritten change to a dirty page so a good idea to periodically flush dirty pages to disk Store LSN of chkpt record in master record CS511 Advanced Database Management Systems Big Picture What s Stored Where LOG DB LogRecords prevLSN XID type pageID length offset before image after image Data pages master record CS511 Advanced Database Management Systems each with a pageLSN RAM Xact Table lastLSN last log status Dirty Page Table recLSN first log flushedLSN Simple Transaction Abort For now consider an explicit abort of a xact e g validation error deadlock no crash involved Play back the log in reverse order UNDOing updates get lastLSN of xact from xact table can follow chain of log records backward via the prevLSN field can we do so in crash recovery undoing before starting undo write an abort log record for recovering from crash during undo CS511 Advanced Database Management Systems Abort cont To perform UNDO must have a lock on data no problem strict locking Before restoring old value of a page write a CLR you continue logging while you undo CLR has one extra field undoNextLSN points to the next LSN to undo i e the prevLSN of the record we re currently undoing CLRs never undone but might be redone when repeating history after another crash At end of UNDO write an End log record 120 CLR T1 abort 101 98 undo 101 T1 lastLSN 101 undonextLSN 98 T1 lastLSN 120 CS511 Advanced Database Management Systems Transaction Commit Write commit record to log All log records up to xact s lastLSN are flushed guarantees that flushedLSN lastLSN Commit returns after synchronous IO Write End record to log CS511 Advanced Database Management Systems Crash Recovery Big Picture Oldest log rec of Xact active at crash Smallest recLSN in dirty page table after Analysis Start from a checkpoint found via master record Three phases Need to figure out which xacts committed since checkpoint which failed Analysis REDO all actions repeat history UNDO effects of failed xacts Last chkpt CRASH A R U CS511 Advanced Database Management Systems Crash Recovery vs Transaction Abort What are the differences CS511 Advanced Database Management Systems 19 Crash Recovery vs