Motivation for Recovery Atomicity Transactions may abort Rollback

Motivation for Recovery • Atomicity: – Transactions may abort (“Rollback”). • 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 crash!

Handling the Buffer Pool • Force every write to disk? – Poor response time. – But provides durability. No Steal Force • Steal buffer-pool frames from uncommitted No Force transactions? – If not, poor throughput. – If so, how can we ensure atomicity? Steal Trivial Desired

Basic Idea: Logging • Record REDO and UNDO information, for every update, in a log. – Sequential writes to log (put it on a separate disk). – Minimal info (difference) written to log, so multiple updates fit in a single log page. • Log: An ordered list of REDO/UNDO actions – Log record contains: <XID, page. ID, offset, length, old data, new data> – and additional control information. • The Write-Ahead Logging Protocol: Must force the log record for an update before the corresponding data page gets to disk. ‚ Must write all log records for a transaction before commit.

WAL & the Log DB LSNs page. LSNs RAM flushed. LSN • Each log record has a unique Log Sequence Number (LSN). – LSNs always increasing. Log records • Each data page contains a page. LSN. flushed to disk – The LSN of the most recent log record for an update to that page. • System keeps track of flushed. LSN. – The max LSN flushed so far. page. LSN • WAL: Before a page is written, “Log tail” in RAM – page. LSN £ flushed. LSN

Recovery Three steps: (a la` ARIES) Starting from a checkpoint: • Analysis: figure out which transactions committed since the checkpoint, and which failed. • REDO all actions in the log. • UNDO effects of failed transactions.

Summary • Users access the database concurrently, and sometimes there are crashes. • Transactions are sets of operations that are guaranteed to be atomic. • The DBMS guarantees: Atomicity, Consistency, Isolation, Durability. • Isolation and consistency are guaranteed via locking: 2 -phase (need special care for deadlocks). • Atomicity and durability are guaranteed by: • Logging • Recovery manager (that uses the log). There are MANY more missing details!