Transactional Recovery and Checkpoints

1

• Transactional Recovery and Checkpoints
• Chap. 10.6-10.8

2
Difference

• How is this different from schedule recovery?
• Lower level Operating System level
• Writing out dirty data, etc.

3
Recovery is needed for atomicity and durability

• Problems
• Reading - involves buffers, paging and LRU
  replacement (operating system)
• Writing - when to write updates to disk?  When
  LRU replaces (use dirty bit)?  
• What if crash and still in memory?
• No immediate durability  
• No atomicity
• Solution?
• Have a log buffer with log entries and a log file
  

4
Log Buffer and Files

• Write log entry from log buffer, write changes to
  disk (log file) at intervals
• log file used to perform recovery
• log buffer and log file guarantees atomicity and
  durability

5
How?

• log entries 
• W, T, data_item, before_value, after_value
• or RID, list of cols with before and after
  values
• also insert and delete tuple log entries
• why before values?
• in case must UNDO
• why after values?
• in case must REDO

6
When done?

• Log buffer written to log file, when a T commits
  or log buffer is full
• 2 concepts 
• entries (updates) to log file
• vs.
• making changes to data on disk

7
Actions

• For atomicity 1)  Rollback - make list of
  committed T's and UNDO uncommitted T's actions
• For durability 2)  Rollforward - to REDO
  committed T's actions

8
Log Entries

• In log entry keep track of 1)  committed T's (
  C, T)  2)  uncommitted T's - enter writes but
  not reads 3)  Start of Transactions (S, T)

9
Example

• R1(A,50) W1(A,20) R2(C,100)W2(C,50)C2
  R1(B,50)W1(B,8)C1
• 1   2         3            4 5          
  6             7    8            9          10
•          Log entries             1.  (S1)
• 2. no entry           
• 3. (W,1,A,50,20)             4. (S2)
• 5. no entry            6. (W,2,C,100,50)
              7. (C2)             8.  no entry
              9.  (W,1,B,50,80)            10.
  (C1)
•    What if crash on operation 9?

10
How to recover fro crash

• Must undo T1 (will restart later) and redo T2
• Rollback until log file empty  1.  C2 - T2 on
  committed list  2.  (W,2,C,100,50)  C2 on list,
  do nothing 3.  (S2)  T2 no longer active 4. 
  (W,1,A,50,20) UNDO this update, T1 not on the
  committed list 5.  (S1)  T1 no longer active

11
How to recover contd

• Rollforward  6.  S1 - no action  7. 
  (W,1,A,50,20) T1 uncommitted - no action 8. 
  (S,2) no action 9.  (W,2,C,100,50) Redo update
  10.  (C,2) no action
• 11. done

12
For durability

• When write log buffer to log file
• Use read after write protocol
• if error in disk write, will fail on read (so
  system knows it failed)
• When successful write
• written to 2 different disks for extra durability

13
Problems

• Potential problems with log files
• durability - commits successful only once log
  file written on disk
• atomicity - Be sure dirty pages of data not
  written to disk before log entry - OS
• if failure and log entry not written?
• can never UNDO or REDO

14
Solutions

• Can modify LRU replacement to ensure data not
  written to disk before log file
• REDO
• 1. updated data page not written to disk until
  commit
• no UNDO processing ever
• no before images needed in logs
• what if lot of updates?  can't keep all in memory
  
• UNDO
• 2. write ahead log guarantee (WAL) log sequence
  number (LSN)-
• every log entry keeps track of smallest LSN to
  log file since last write (lsn_buffmin)
• keep track of updates to data pages (lsn_pgmax)
• cannot write page to disk unless lsn_pgmax lsn_buffmin

15
Recovery

• Recovery Checkpoints used so only have to
  rollback to a certain point
• A Checkpoint
• Consistent snapshot of all of the database -
  values on durable disk
• A Checkpoint is a database event which
  synchronizes the modified data blocks in memory
  with the datafiles on disk.

16
Recovery

• T's short-lived (take a few seconds) therefore
  rollback is quick - only a few T's active that
  need to be UNDONE
• rollforward takes longer - many T's to REDO,
  keep track of start T's, etc.
• 3 approaches for checkpoints

17
Commit consistent

• 1.  Commit consistent -needed when count of log
  events exceeds some limit      Enter checkpoint
  state
• a)  no new T's start until checkpoint complete
• b)  DB processing continues until all existing
  T's commit and log entries out on disk
• c)  current log buffer written to log file, all
  dirty pages written to disk
• d)  when a)-c) complete, special log entry CKPT
  entered  (these steps are the same as an orderly
  shutdown of the system )

18
Commit consistent contd

• To recover   Rollback until CKPT, then REDO
  committed since CKPT
• Problems 
• But what if some transactions are long-lived?
• must wait a long-time for them to finish, with
  no new T's active

19
Cache-consistent

• 2.  Cache-consistent checkpoint - aim to reduce
  time if long transactions
• a)  No new T's permitted to start
• b)  existing T's cannot start any new ops
• c)  current log buffer written to disk, all
  dirty data pages to disk
• d)  log entry (CKPT, list of active T's) written
  on log file on disk

20
Cache-consistent contd

• To recover
• must rollback past checkpoint
• rollback the same as for commit consistent, then
  keep rolling back until list in CKPT all undone
  (if not committed yet)
• Rollback - given the list of active T's, remove
  those committed then left with a list of T's to
  UNDO
• Rollforward - REDO all updates by committed T's
  start at first op after last CKPT

21
Cache-consistent contd

• Problems
•   Time to flush dirty pages to disk
• R1(A,10)W1(A,1)C1R2(A,1)R3(B,2)W2(A,3)
• R4(C,5) CKPT W3(B,4)C3R4(B,4)W4(C,6)
• Crash during C4

22
Fuzzy

• 3.  Fuzzy checkpoint
• aim to reduce time to perform a checkpoint
  (CKPTn1)
• makes use of 2 checkpoint events  CKPTn-1 and
  CKPTn
• a)  Prior to checkpoint, remaining pages dirty
  since
• forced to disk
• b)  no new T's starts - existing T's no new ops
• c)  current log buffer written to disk with
  (CKPTn, list)
• d)  set of dirty pages in buffer that
  accumulated since prior CKPTn-1 is noted
• background process makes sure pages changed out
  on disk by next checkpoint CKPTn1

23
Fuzzy

• Rollforward starts with first log entry following
  2nd to last checkpoint log        CKPTn-1
  ......  CKPTn    
• (start at CKPTn-1)
• set of dirty pages since CKPTn-1 will hopefully
  be written to disk by CKPTn1 else buffer
  flushing at time of CKPTn

24
Checkpoints in Oracle

• The mechanism of writing modified blocks on disk
  in Oracle is not synchronized with the commit of
  the corresponding transactions.
• all database changes up to the checkpoint have
  been recorded in the datafiles, making it
  unnecessary to apply redo log entries prior to
  the checkpoint.

25
Checkpoints in Oracle

• Oracle writes the dirty buffers to disk only on
  certain conditions
• A shadow process must scan more than one-quarter
  of DB_BLOCK_BUFFER
• Every three seconds
• When a checkpoint is produced

26
Checkpoints in Oracle

• A checkpoint is realized on five types of events
  
• At each switch of the redo log files
• When the delay for LOG_CHECKPOINT_TIMEOUT is
  reached.
• When the size in bytes corresponding
  to (LOG_CHECKPOINT_INTERVAL size of IO OS
  blocks) is written on the current redo log file.
• Directly by the ALTER SYSTEM SWITCH LOGFILE
  command.
• Directly with the ALTER SYSTEM CHECKPOINT command
  

27
Checkpoints in Oracle

• During a checkpoint the following occurs
• The database writer (DBWR) writes all modified
  database blocks in the buffer cache back to
  datafiles
• Log writer (LGWR) updates both the controlfile
  and the datafiles to indicate when the last
  checkpoint occurred (SCN).