Transaction oriented database recovery
Download
1 / 25

Transaction-Oriented Database Recovery - PowerPoint PPT Presentation


  • 594 Views
  • 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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)

Application

SophisticatedApplicationProgrammer(e.g., SAP admin)

QueryProcessor

Indexes

Storage Subsystem

Concurrency Control

Recovery

DBA,Tuner

Operating System

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


Outline l.jpg
Outline

  • 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

Transaction UNDO

System

Global UNDO, Partial REDO

Media

Global REDO


Log for undo redo l.jpg
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)


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

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?


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

Checkpoint

Crash

T1

Nothing

T2

REDO

T3

T4

UNDO

T5

Analyze

Recovery

Process

UNDO

REDO


Transaction oriented checkpoint toc l.jpg
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


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

Summary

  • Failure types

Failure Type

Recovery Action

Transaction

Transaction UNDO

System

Global UNDO, Partial REDO

Media

Global REDO