Database Management Recovery and the ACID properties Systems A - PowerPoint PPT Presentation

Lecture 9 Database Management Recovery and the ACID properties Systems A tomicity: “all or nothing” A C onsistency: “DB starts consistent and ends consistent” Winter 2004 C I solation: “isolated from the effects of other transactions” I CMPUT 391: Implementing Durability D urability: “ effects of commited transaction persist” D • The Recovery Manager is responsible for ensuring Atomicity and Durability . Dr. Osmar R. Zaïane – Atomicity is guaranteed by undoing the actions of the transactions that did not commit (aborted). – Durability is guaranteed by making sure that all actions Chapter 25 of committed transactions survive crashes and failures. University of Alberta of Textbook Based on slides by Lewis, Bernstein and Kifer. 1 2 Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta General Idea Types of Failures • Transaction failures • If a transaction T i is aborted (e.g., for concurrency control – overflow, interrupt, data not available, explicit rollback, reasons), all its actions have to be undone . concurrency enforcement, programming errors • Active transactions at the time of the crash have to be – no memory loss. aborted, i.e., their effects have to be undone when the • System crashes system comes back. – due to hardware or software errors � DBMS has to maintain enough information to undo actions – main memory content is lost of transactions (the LOG File) • Media failures Desired Behavior after system restarts: crash! – problems with disk head, unreadable media surface – T1, T2 & T3 should be durable. T1 – (parts of ) information on secondary storage may be lost T2 – T4 & T5 should be rolled back, i.e., • Natural disasters T3 effects undone. – fire, flood, earthquakes, theft, etc. T4 T5 – physical loss of all information on all media Dr. Osmar Zaïane, 2004 3 Dr. Osmar Zaïane, 2004 4 CMPUT 391 – Database Management Systems University of Alberta CMPUT 391 – Database Management Systems University of Alberta

Log Log • Sequence of records (sequential file) – Modified by appending (no updating) • Each modification of the database causes an • Contains information from which database can be restored update record to be appended to the log • Log and database stored on different mass storage devices • Update record contains: • Often replicated to survive single media failure – Identity of data item modified • Contains valuable historical data not in database – Identity of transaction (tid) that did the – How did database reach current state? modification – Before image (undo record) - value of data item before update occurred New Stable Old Update DB state • Referred to as physical logging Stable Operation DB state Database Log 5 6 Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Log Transaction Abort Using Log • Scan log backwards using tid to identify transaction’s x y z u y w z update records T 1 T 1 T 2 T 3 T 1 T 4 T 2 • Reverse each update using before image 17 A 2.4 18 ab 3 4.5 • In a strict system, new values are unavailable to • Update records in a log concurrent transactions (as a result of long term exclusive locks); hence rollback makes transaction atomic • Problem : terminating scan (log can be long) • Solution : append begin record containing tid prior to first update record Dr. Osmar Zaïane, 2004 7 Dr. Osmar Zaïane, 2004 8 CMPUT 391 – Database Management Systems University of Alberta CMPUT 391 – Database Management Systems University of Alberta

Crash Recovery Using Log Transaction Abort Using Log B U U U U U U U • Abort all transactions active at time of crash x y z u y w z • Problem : How do you identify them? T 1 T 1 T 1 T 2 T 3 T 1 T 4 T 2 17 A 2.4 18 ab 3 4.5 • Solution : abort record or commit record appended to log when transaction terminates abort T 1 Key: • Recovery Procedure: B – begin record – Scan log backwards - if first of T’s records is update U – update record record, T was active at time of crash. Roll it back • Scan back to begin record to abort • Transaction not committed until commit record in log a transaction 9 10 Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Crash Recovery Using Log Crash Recovery Using Log • Problem : Scan must retrace entire log • Solution : Periodically append checkpoint B U U U U U C U A U x y z u y w z record to log. Contains tid’s of all active T 1 T 1 T 1 T 2 T 3 T 1 T 3 T 4 T 1 T 2 transactions at time of append 17 A 2.4 18 ab 3 4.5 – Backward scan goes at least as far as last checkpoint record appended Key: crash – Transactions active at time of crash determined from B – begin record log suffix that includes last checkpoint record • T 1 and T 3 were not active at U – update record time of crash C – commit record – Scan continues until those transactions have been A – abort record rolled back Dr. Osmar Zaïane, 2004 11 Dr. Osmar Zaïane, 2004 12 CMPUT 391 – Database Management Systems University of Alberta CMPUT 391 – Database Management Systems University of Alberta

Write-Ahead Log Example • When x is updated two writes must occur: update x in Backward scan database, append of update log record – Which goes first? B2 B3 U2 B1 C2 B5 U3 U5 A5 CK U1 U4 B6 C4 U6 U1 …………………..update x ; append to log ……………. crash T1 crash crash crash (no before image in log) T4 Key: T3 U - update record ………………..append to log; update x …………………. B - begin record T 1 , T 3 and T 6 active C - commit record at time of crash crash crash crash A - abort record (use before image; CK - checkpoint record it has no effect) 13 14 Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Dr. Osmar Zaïane, 2004 CMPUT 391 – Database Management Systems University of Alberta Write-Ahead Logging Write-Ahead Log: Performance • An update record must always be appended to the • Problem : two I/O operations for each Log before the database is updated on disk. database update • The Write-Ahead Logging Protocol: • Solution : log buffer in main memory Must force the log record for an update before the corresponding data page gets to disk. – Extension of log on mass store – Periodically flushed to mass store guarantees Atomicity (and Durability). – Flush cost pro-rated over multiple log appends Dr. Osmar Zaïane, 2004 15 Dr. Osmar Zaïane, 2004 16 CMPUT 391 – Database Management Systems University of Alberta CMPUT 391 – Database Management Systems University of Alberta