16

1
16. Recovery - Review (only for DB with
updates..!)

• ACID Atomicity Durability
• Trading execution time for recovery time
• Mechanics of recovery
• What is recovered and how
• Write ahead logging
• Mechanics of logging
• Big Picture

2
Learning Objectives

• Describe Force and No Steal policies and their
  implications
• Describe write ahead log, commit and abort
• What is the role of each element in crash
  recovery
• LSN, before image, after image, FlushedLSN,
  pageLSN, dirty page table, transaction table,
  checkpoint
• Be able to carry out the three phases of crash
  recovery in a simple example.

3
Review The ACID properties
Recovery System

• Atomicity All actions in the transaction happen
  in their entirety or none of them happen.
• Consistency If each transaction is consistent,
  and the DB starts in a consistent state, it
  ends in a consistent state.
• Isolation Execution of one transaction is
  isolated from that of other
  transactions.
• Durability If a transaction commits, its
  effects persist.

Programmers
Concurrency Control System
Recovery System
4
Simple solutions to Crash Recovery

• The crash recovery subsystem has two problems to
  solve, atomicity and durability. Here are some
  really simple ways to handle them.
• Atomicity
• Example
• Deposit 100 into A, withdraw 100 from B.
• The OS crashes after 100 is deposited to A.
• The recovery manager must undo the deposit.
• Solution (NO STEAL) Make sure no updates (e.g.
  the deposit) are written to disk until the
  transaction commits. The transaction will
  disappear when the OS crashes.
• Durability
• Same example, now the OS crashes after the commit
  and before the withdrawal was written to disk.
• Solution (FORCE) Write every update to disk just
  before the transaction commits.

5
Why these simple solutions dont work

• No Steal keep updates in memory until commit
• If DBMS doesnt steal, memory is full of updates
  and other transactions are starved for memory.
  Poor throughput.
• Force write all updates to disk immediately on
  commit
• Force keeps the disk too busy and results in poor
  response time. The records being updated may be
  hot spots.
• Why do you have to warn your OS before removing
  an external device? Because of the OSs No Force
  Policy.

No Steal
Steal
Slow Execution Easy recovery
Force
Fast Execution Tough Recovery
No Force
6
The solution

• Given that we are faced with the No Force and
  Steal policies, the crash recovery subsystem of
  every DBMS uses a Write Ahead Log (WAL) to manage
  crash recovery (and aborts also).
• The WAL is put on a separate disk from the data
  (why?). It begins from each backup, which is
  typically taken each day.
• A log record is written for every insert, update,
  delete, begin-trans, commit, abort and
  checkpoint.
• A log record contains
• ltXID, ID of the DB record, action, old data, new
  datagt

before image
after image
7
Write Ahead Log (WAL)

• To be a write ahead log, the log must obey these
  rules
• The atomicity rule the log entry for an insert,
  update or delete must be written to disk before
  the change is made to the DB
• The durability rule All log entries for a
  transaction must be written to disk before the
  commit record is written to disk.

8
Practice with a log
T1,A,update,100,200 T2,C,update,1000,500 T2,D,upda
te,500,1000 T2,commit CRASH

• What did each transaction do before the crash?
• After a crash, what should the recovery manager
  do to ensure that each transaction is atomic?
• What guarantees that your solution (UNDO) will
  work?
• After a crash, what should the recovery manager
  do to ensure that each transaction is durable?
• What guarantees that your solution (REDO) will
  work?

9
Practice with a log
T1,A,update,abc,de T1,A,update,de,ab T2,D,
update,10,0 T2,commit CRASH

• What must a recovery manager do after a crash to
  ensure the atomic and durability properties of
  ACID?
• What are the final values of A and D?
• Does the recovery manager return the DB to its
  state at the time of the crash?

10
Real recovery is more complicated

• We have ignored many complexities in crash
  recovery
• Managing normal aborts, some of which may be in
  progress at the time of the crash
• Managing inserts and deletes
• Supporting multiple lock levels
• Managing updates to structures like B trees when
  pages split
• Handling crashes that happen in the middle of
  recovery
• In the early days of DBMSs many inflexible,
  inefficient and even incorrect recovery
  algorithms were implemented.

11
ARIES

• In the early 1990s, C. Mohan of IBM proposed a
  relatively simple recovery algorithm called
  ARIES
• ARIES differs from our simple model in a few ways
• It redoes every update, not just those of
  committed transactions. This simplifies the
  algorithm.
• It logs changes when normal aborts are undone.
  This handles recovery for normal aborts.
• It logs undos during recovery. This handles
  crashes during recovery.
• And.
• Algorithms for Recovery and Isolation
  Exploiting Semantics

12
ARIES Runs in Phases
time

• Three phases.
• Figure out which transactions are incomplete
  (ANALYSIS).
• REDO actions for all transactions.
• UNDO all actions of incomplete transactions.

Start of log
CRASH
A
R
U
13
ARIES Uses Checkpoints

• Typically a backup is taken each night and the
  log begins anew each morning. If there is a
  crash in the afternoon, it may take hours to
  recover. This is unacceptable.
• So the DBMS periodically (like every few minutes)
  takes a checkpoint.
• At a checkpoint the DBMS writes all dirty pages
  to disk and writes to the log a checkpoint record
  containing the IDs of all active transactions.
• Then the recovery algorithm can begin at the last
  checkpoint and recovery is much faster.
• This is not the text/ARIES definition of
  checkpoint but it is what most real DBMSs use

14
ARIES Phases with Checkpoints

• Start from a checkpoint.
• Figure out which transactions are incomplete
  (ANALYSIS).
• REDO all actions since the checkpoint.
• UNDO all actions of incomplete transactions.

time
Last chkpt
CRASH
A
R
U
15
Practice with a log
CHECKPOINT T3,T4 active T1,A,update,ABC,DEF T3
,X,update,ABC,DEF T2,C,update,1000,500 T4,Y,upd
ate,4.1,5.2 T2,D,update,500,1000 T4,commit T1,A,up
date,DEF,GHI T2,commit CRASH

• What does ARIES do?
• Why doesnt ARIES REDO T4s actions before the
  checkpoint?
• Why does UNDO process log entries before the
  checkpoint?

16
Using the WAL to manage aborts

• We have seen that a Write Ahead Log, with ARIES,
  makes atomicity and durability easy to achieve.
• A Write Ahead Log also makes transaction abort
  simple. A transaction does not have to keep
  track of the changes it has made to the DB so it
  can undo them in case of abort. It just looks at
  the Write Ahead Log!

17
Aborting a transaction
T1,A,update,ABC,DEF T2,C,update,1000,500 T2,D,
update,500,1000 T1,B,update,300,400 T1,A,update,D
EF,GHI T1,abort

• Note that this is normal processing no crash in
  sight
• What actions must the DBMS take to abort T1?
• In what order should these actions be taken?
• What guarantees that all of T1s changes to the
  DB have been undone?
• What if the update to B was not written to disk?

18
Chapter 18 Crash Recovery

• Write-Ahead Log
• ARIES Algorithm
• Parameters
• LSN, pageLSN, FlushedLSN
• Log Record Details
• PrevLSN, Before_image, After_image, CLR
• Transaction Table, Dirty Page Table
• Checkpointing
• Examples
• Analysis, Redo, Undo

19
Crash RecoveryReview The ACID properties
18. Crash

• A tomicity All actions in the Xact happen, or
  none happen.
• C onsistency If each Xact is consistent, and
  the DB starts consistent, it ends up consistent.
• I solation Execution of one Xact is isolated
  from that of other Xacts.
• D urability If a Xact commits, its effects
  persist.
• The Recovery Manager guarantees Atomicity
  Durability.

20
Basic Idea Logging
18. Crash

• Well study the ARIES algorithms.
• Algorithm for Recovery and Isolation Exploiting
  Semantics
• Record REDO and UNDO information, for every
  update, in a log.
• Sequential writes to log (put it on a separate
  disk).
• Minimal info (diff) written to log, so multiple
  updates fit in a single log page.
• Log An ordered list of REDO/UNDO actions
• Log record contains
• ltLSN, XID, pageID, offset, length, old data, new
  datagt
• and additional control info (which well see
  soon).

21
Example Log
LSN

• update T1 writes P5
• update T1 writes P3
• 30 T1 commit
• 40 T1 end
• 50 update T3 writes P1
• 60 update T3 writes P3
• CRASH, RESTART

?
22
Write-Ahead Logging (WAL)
18. Crash

• 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 Xact before
  commit.
• 1 guarantees Atomicity.
• 2 guarantees Durability.
• Exactly how is WAL managed? Key idea is PageLSN

23
Motivation PageLSN

• At any time, memory contains database, log pages
• Changes to database records are reflected in log
  records
• Suppose a database page is to be written to disk
• Must find all in-memory log records for changes
  to records on that database page
• Must write their log pages sequentially, before
  the database page
• How to keep track of those log records?
• Keep the last, largest, of the log records for
  that page
• Called PageLSN
• Stored with that page, in memory and in database
• Important conclusions
• If Log entry PageLSN is on disk, its safe to
  write the database page
• A log entry, with LSN lt the PageLSN on disk, has
  been written to disk.

24
WAL the Log
18. Crash

• Each log record has a unique Log Sequence Number
  (LSN).
• LSNs always increasing.
• Each data page contains a pageLSN.
• The LSN of the most recent log record
  for an update to
  that page.
• System keeps track of flushedLSN.
• The max LSN flushed so far.
• WAL Before a page is written,
• pageLSN flushedLSN

Log records flushed to disk
Log tail in RAM
25
Log Records
18. Crash

• Possible log record types
• Update
• Commit
• Abort
• End (signifies end of commit or abort)
• Compensation Log Records (CLRs)
• for UNDO actions

LogRecord fields
LSN
prevLSN
XID
type
pageID
length
update records only
offset
before-image
after-image
26
Compensation Log Records

• A CLR is written whenever an update is undone
• A CLR represents a change to the database
• CLRs are redone but not undone
• Each CLR contains a field undoNextLSN
• The LSN of the next log record to be undone

27
Key ARIES Ideas

• Write Ahead Logging
• Weve seen how that is managed with PageLSN
• Enables atomicity and durability with the other
  two ideas
• REDO of committed Xacts
• Why is it needed?
• What does it involve?
• UNDO of uncommitted Xacts
• Why is it needed?
• What does it involve?

28
Motivation recLSN

• A page in memory contains many records
• Some records may be dirty, making the page dirty.
• During recovery, need to redo dirty records in
  dirty pages
• Problem where in log to start redoing?
• Answer earliest log record of all dirty records
• Called recLSN
• Stored in dirty page table
• Important conclusion
• All updates before recLSN are clean

29
Other Log-Related State in Memory
18. Crash

• Dirty Page Table
• One entry per dirty page in buffer pool.
• Contains recLSN -- the LSN of the log record
  which first caused the page to be dirty.
• Transaction Table
• One entry per active Xact.
• Contains XID, status (running/commited/aborted),
  and lastLSN.

30
Normal Execution of an Xact
18. Crash

• Series of reads writes, followed by commit or
  abort.
• We will assume that write is atomic on disk.
• In practice, additional details to deal with
  non-atomic writes.
• Strict 2PL.
• STEAL, NO-FORCE buffer management, with
  Write-Ahead Logging.

31
Checkpointing
18. Crash

• Periodically, the DBMS creates a checkpoint, in
  order to minimize the time taken to recover in
  the event of a system crash. Write to log
• begin_checkpoint record Indicates when chkpt
  began.
• end_checkpoint record Contains current Xact
  table and dirty page table. This is a fuzzy
  checkpoint
• Other Xacts continue to run so these tables are
  accurate only as of the time of the
  begin_checkpoint record.
• No attempt to force dirty pages to disk
  effectiveness of checkpoint limited by oldest
  unwritten change to a dirty page. (So its a good
  idea to periodically flush dirty pages to disk!)
• Store LSN of chkpt record in a safe place (master
  record).

32
The Big Picture Whats Stored Where
18. Crash
LOG
RAM
DB
LogRecords
Xact Table lastLSN status Dirty Page
Table recLSN flushedLSN
LSN
Data pages each with a pageLSN
prevLSN
XID
type
pageID
length
master record
offset
before-image
after-image
33
Example Log
LOG
Undo nextLSN
prevLSN XID Type PgID length offset
before after
LSN 10 20 30 40 50
T1 update P5 3 21 ABC DEF T2
update P6 3 41 HIJ KLM T2 update P5
3 10 GDE QRS T1 update P7 3 21 TUV
WXY T1 abort
DIRTY PAGE TABLE
TRANSACTION TABLE
34
Simple Transaction Abort
18. Crash

• For now, consider an explicit abort of a Xact.
• No crash involved.
• We want to play back the log in reverse order,
  UNDOing updates.
• Get lastLSN of Xact from Xact table.
• Can follow chain of log records backward via the
  prevLSN field.
• Before starting UNDO, write an Abort log record.
• For recovering from crash during UNDO!

35
Abort, cont.
18. Crash

• To perform UNDO, must have a lock on data.
• No problem!
• Before restoring old value of a page, write a
  CLR
• You continue logging while you UNDO!!
• CLR has one extra field undonextLSN
• Points to the next LSN to undo (i.e. the prevLSN
  of the record were currently undoing).
• CLRs never Undone (but they might be Redone when
  repeating history guarantees Atomicity!)
• At end of UNDO, write an end log record.

36
Transaction Commit
18. Crash

• Write commit record to log.
• Release all locks
• All log records up to Xacts lastLSN are flushed.
• Guarantees that flushedLSN ³ lastLSN.
• Note that log flushes are sequential, synchronous
  writes to disk.
• Many log records per log page.
• Commit() returns.
• Write end record to log.

37
Crash Recovery Big Picture
18. Crash
Oldest log rec. of Xact active at crash

• Start from a checkpoint (found via master
  record).
• Three phases. Need to
• Figure out which Xacts committed since
  checkpoint, which failed (Analysis).
• REDO all actions.
• (repeat history)
• UNDO effects of failed Xacts.

Smallest recLSN in dirty page table after Analysis
Last chkpt
CRASH
A
R
U
38
Recovery The Analysis Phase
18. Crash

• Reconstruct state at checkpoint.
• via end_checkpoint record.
• Scan log forward from checkpoint.
• End record Remove Xact from Xact table.
• Other records Add Xact to Xact table, set
  lastLSNLSN, change Xact status on commit.
• Update record If P not in Dirty Page Table,
• Add P to D.P.T., set its recLSNLSN.

39
Example Log
LOG
Undo nextLSN
prevLSN XID Type PgID length offset
before after
LSN 10 20 30 40 50 60
T1 update P5 3 21 ABC DEF T2
update P6 3 41 HIJ KLM T2 update P5
3 10 GDE QRS T1 update P7 3 21 TUV
WXY T2 commit/end T1 update P3 3
14 ABC DEF CRASH
DIRTY PAGE TABLE

• Assume P6 (only) written to disk. What is its
  PageLSN?
• Assume LSN60 (only) not written to disk. What if
  P3 was written to disk?

TRANSACTION TABLE
40
Recovery The REDO Phase
18. Crash

• We repeat History to reconstruct state at crash
• Reapply all updates (even of aborted Xacts!),
  redo CLRs.
• Scan forward from log rec containing smallest
  recLSN in D.P.T. For each CLR or update log rec
  LSN, REDO the action unless
• Affected page is not in the Dirty Page Table, or
• Affected page is in D.P.T., but has recLSN gt LSN,
  or
• pageLSN (in DB) ³ LSN.
• To REDO an action
• Reapply logged action.
• Set pageLSN to LSN. No additional logging!

41
Example Log
LOG
Undo nextLSN
prevLSN XID Type PgID length offset
before after
LSN 10 20 30 40 50
T1 update P5 3 21 ABC DEF T2
update P6 3 41 HIJ KLM T2 update P5
3 10 GDE QRS T1 update P7 3 21 TUV
WXY T2 commit/end
DIRTY PAGE TABLE

• Assume P6 (only) written to disk.

42
Recovery The UNDO Phase
18. Crash

• ToUndo l l a lastLSN of a loser Xact
• Repeat
• Choose largest LSN among ToUndo.
• If this LSN is a CLR and undonextLSNNULL
• Write an End record for this Xact.
• If this LSN is a CLR, and undonextLSN ! NULL
• Add undonextLSN to ToUndo
• Else this LSN is an update. Undo the update,
  write a CLR, add prevLSN to ToUndo.
• Until ToUndo is empty.

43
Example
18. Crash
LSN LOG
begin_checkpoint end_checkpoint update T1
writes P5 update T2 writes P3 T1 abort CLR Undo
T1 LSN 10 T1 End update T3 writes P1 update T2
writes P5
00 05 10 20 30 40
45 50 60
PgID recLSN
prevLSNs
DIRTY PAGE TABLE
XID lastLSN
TRANSACTION TABLE
44
Example Crash During Restart!
18. Crash
LSN LOG
begin_checkpoint, end_checkpoint update T1
writes P5 update T2 writes P3 T1 abort CLR Undo
T1 LSN 10, T1 End update T3 writes P1 update T2
writes P5 CRASH, RESTART CLR Undo T2 LSN 60 CLR
Undo T3 LSN 50, T3 end CRASH, RESTART CLR Undo
T2 LSN 20, T2 end
00,05 10 20 30 40,45 50
60 70 80,85 90
undonextLSN
Xact Table lastLSN status Dirty Page
Table recLSN flushedLSN
ToUndo
45
Additional Crash Issues
18. Crash

• What happens if system crashes during Analysis?
  During REDO?
• How do you limit the amount of work in REDO?
• Flush asynchronously in the background.
• Watch hot spots!
• How do you limit the amount of work in UNDO?
• Avoid long-running Xacts.

46
Summary of Logging/Recovery
18. Crash

• Recovery Manager guarantees Atomicity
  Durability.
• Use WAL to allow STEAL/NO-FORCE w/o sacrificing
  correctness.
• LSNs identify log records linked into backwards
  chains per transaction (via prevLSN).
• pageLSN allows comparison of data page and log
  records.

47
Summary, Cont.
18. Crash

• Checkpointing A quick way to limit the amount
  of log to scan on recovery.
• Recovery works in 3 phases
• Analysis Forward from checkpoint.
• Redo Forward from oldest recLSN.
• Undo Backward from end to first LSN of oldest
  Xact alive at crash.
• Upon Undo, write CLRs.
• Redo repeats history Simplifies the logic!