Transaction oriented database recovery
1 / 25

Transaction-Oriented Database Recovery - PowerPoint PPT Presentation

  • Updated On :

Transaction-Oriented Database Recovery. Application Programmer (e.g., business analyst, Data architect). Application. Sophisticated Application Programmer (e.g., SAP admin). Query Processor. Indexes. Storage Subsystem. Concurrency Control. Recovery. DBA, Tuner. Operating System.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Transaction-Oriented Database Recovery' - daniel_millan

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Slide2 l.jpg

ApplicationProgrammer(e.g., business analyst,

Data architect)


SophisticatedApplicationProgrammer(e.g., SAP admin)



Storage Subsystem

Concurrency Control



Operating System

Hardware[Processor(s), Disk(s), Memory]

Outline l.jpg

  • Principles of transaction-oriented database recovery

  • Recovery tuning

Transaction oriented database recovery4 l.jpg
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

Failures to recover from l.jpg
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

Recovery actions l.jpg
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 UNDO


Global UNDO, Partial REDO


Global REDO

Log for undo redo l.jpg

  • 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)

System framework l.jpg
System Framework

Source: T. Haerder, A. Reuter

Log timing l.jpg
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

Dependency with buffer management l.jpg


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


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?

Checkpointing to optimize recovery l.jpg
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

Crash recovery with checkpoint l.jpg
Crash Recovery with Checkpoint

Oldest Page

In Buffer
















Transaction oriented checkpoint toc l.jpg
Transaction-Oriented Checkpoint (TOC)



  • Frequently used pages need to be written out each time a transaction commits

  • Not suitable for large applications

Source: T. Haerder, A. Reuter

Transaction consistent checkpoint tcc l.jpg
Transaction-Consistent Checkpoint (TCC)

Source: T. Haerder, A. Reuter

Transaction consistent checkpoint tcc15 l.jpg
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

Action consistent checkpoint acc l.jpg
Action-Consistent Checkpoint (ACC)

Source: T. Haerder, A. Reuter

Action consistent checkpoint acc17 l.jpg
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

Fuzzy acc l.jpg
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

Archive recovery l.jpg
Archive Recovery

Source: T. Haerder, A. Reuter

Make sure the two paths are independent!!

Multi generation archive copies l.jpg
Multi-Generation Archive Copies

  • Archive copies are accessed very infrequently

  • Subject to magnetic decay

  • Keep several generations

Source: T. Haerder, A. Reuter

Duplicate archive logs l.jpg
Duplicate Archive Logs

Source: T. Haerder, A. Reuter

Duplicate archive logs22 l.jpg
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!

Decouple archive logs from eot l.jpg
Decouple Archive Logs from EOT

Source: T. Haerder, A. Reuter

Decouple archive logs from eot24 l.jpg
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

Summary l.jpg

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


  • Failure types

Failure Type

Recovery Action


Transaction UNDO


Global UNDO, Partial REDO


Global REDO