Transaction-Oriented Database Recovery

1. Transaction-Oriented Database Recovery

2. ApplicationProgrammer(e.g., business analyst, Data architect) Application SophisticatedApplicationProgrammer(e.g., SAP admin) QueryProcessor Indexes Storage Subsystem Concurrency Control Recovery DBA,Tuner Operating System Hardware[Processor(s), Disk(s), Memory]

3. Outline • Principles of transaction-oriented database recovery • Recovery tuning

4. Transaction-Oriented Database Recovery • Transaction properties • A: Atomicity • C: Consistency • I: Isolation • D: Duration • A database is transaction or logically consistent iff it contains the results of successful transactions

5. Failures To Recover From • Transaction failure • Self- or system-abort • To recover within time for normal transaction • 10-100 times per min. • System failure • OS or DBMS crash • To recover in same amount of time as required for all interrupted transactions • A few times per week • Media failure • Disk crash • To recover in hours • A few times per year

6. Recovery Actions • Transaction UNDO – roll-back a specific active trans • Global UNDO – roll-back all active trans • Partial REDO – re-instate some committed trans • Global REDO – re-instate all committed trans Failure Type Recovery Action Transaction Transaction UNDO System Global UNDO, Partial REDO Media Global REDO

7. Log for UNDO/REDO • Logical logging – operators & their arguments • Requires atomic actions from physical layer • Not always possible/justifiable • Physical state logging • Before and/or after image • Physical transition logging • Use XOR: commutative and associative • Log XOR before image  after image • Log XOR after image  before image • Lower space consumption (1 entry/change; compress long strings of 0s – small number of changes)

8. System Framework Source: T. Haerder, A. Reuter

9. Log Timing • UNDO entries must reach log file before changes are written out – Write-Ahead Logging (WAL) principle • To enable roll-back if necessary • REDO entries must reach log file before End-Of-Transaction (EOT) is acknowledged • To enable re-instatement after failure

10. UNDO STEAL: Modified pages may be written anytime ~STEAL: Modified pages kept in buffer till after transaction commits Large buffers required No global UNDO Transaction UNDO within memory No logging required for UNDO REDO FORCE: All modified pages written during EOT No need to log for partial REDO Need logging for global REDO ~FORCE: No propagation during EOT Dependency with Buffer Management At least one of global UNDO or partial REDO is always required. Why?

11. Checkpointing to Optimize Recovery • Problem • With LRU buffer replacement, frequently used pages will remain in buffer • Partial REDO has to go back very far • Checkpointing limits amount of partial REDO • Checkpoint • Write BEGIN-CHECKPOINT to temporary log • Write checkpoint data to log • Write END-CHECKPOINT to temporary log

12. Crash Recovery with Checkpoint Oldest Page In Buffer Checkpoint Crash T1 Nothing T2 REDO T3 T4 UNDO T5 Analyze Recovery Process UNDO REDO

13. Transaction-Oriented Checkpoint (TOC) • FORCE  TOC • EOT  (BEGIN-CHECKPOINT, END-CHECKPOINT) • Frequently used pages need to be written out each time a transaction commits • Not suitable for large applications Source: T. Haerder, A. Reuter

14. Transaction-Consistent Checkpoint (TCC) Source: T. Haerder, A. Reuter

15. Transaction-Consistent Checkpoint (TCC) • When checkpoint generation is triggered • All new update transactions are put on hold • All incomplete update transactions are completed • Write out all modified pages • Both REDO and UNDO are bounded • REDO starts from latest checkpoint • UNDO back to latest checkpoint • Drawback • Delay new update transactions; not suitable for large multi-user DBMS • High checkpointing costs

16. Action-Consistent Checkpoint (ACC) Source: T. Haerder, A. Reuter

17. Action-Consistent Checkpoint (ACC) • When checkpoint generation is triggered • All new actions are put on hold • All incomplete actions are completed • Write out all modified pages • Less disruptive than TCC • Partial REDO only from the most recent checkpoint • Global UNDO not bounded • Still costly when buffers are large

18. Fuzzy ACC • During checkpointing, the numbers of all dirty pages in buffer are written to the log • If a modified page is found in the previous checkpoint, and since then has not been written out, write it out now • Partial REDO from penultimate checkpoint

19. Archive Recovery Source: T. Haerder, A. Reuter Make sure the two paths are independent!!

20. Multi-Generation Archive Copies • Archive copies are accessed very infrequently • Subject to magnetic decay • Keep several generations Source: T. Haerder, A. Reuter

21. Duplicate Archive Logs Source: T. Haerder, A. Reuter

22. Duplicate Archive Logs • Archive log must extend back to the oldest archive copy • Log susceptible to magnetic decay as well • Duplicate archive log • Need to synchronize both archive logs with temporary log at EOT • Very expensive!

23. Decouple Archive Logs from EOT Source: T. Haerder, A. Reuter

24. Decouple Archive Logs from EOT • Log entries written only to temporary log during EOT • Asynchronous process copies REDO entries to archive log • Need to replicate temporary log • Synchronize both temporary logs at EOT

25. Crash recovery TOC: Per transaction TCC: Transaction boundary ACC: Action boundary Archive recovery Multi-generation archive copy Duplicate archive logs Decouple archive log from EOT Summary • Failure types Failure Type Recovery Action Transaction Transaction UNDO System Global UNDO, Partial REDO Media Global REDO