Immediate Database Modification The immediate database modification scheme allows database updates of an uncommitted transaction to be made as the writes are issued since undoing may be needed,update logs must have both old value and new value Update log record must be written before database item is written We assume that the log record is output directly to stable storage Can be extended to postpone log record output,so long as prior to execution of an output(B)operation for a data block B,all log records corresponding to items B must be flushed to stable storage Output of updated blocks can take place at any time before or after transaction commit Order in which blocks are output can be different from the order in which they are written. Database System Concepts,5th Edition,Oct 5,2006 17.17 @Silberschatz,Korth and Sudarshan
Database System Concepts, 5 17.17 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Immediate Database Modification The immediate database modification scheme allows database updates of an uncommitted transaction to be made as the writes are issued since undoing may be needed, update logs must have both old value and new value Update log record must be written before database item is written We assume that the log record is output directly to stable storage Can be extended to postpone log record output, so long as prior to execution of an output(B) operation for a data block B, all log records corresponding to items B must be flushed to stable storage Output of updated blocks can take place at any time before or after transaction commit Order in which blocks are output can be different from the order in which they are written
Immediate Database Modification Example Log Write Output <To start> <T0,A,1000,950> ToB,2000,2050 A=950 B=2050 <To commit> <T1 start> <T1,C,700600> C=600 BB,Bc <T1 commit> BA Note:Bx denotes block containing X. Database System Concepts,5th Edition,Oct 5,2006 17.18 ©Silberschat乜，Korth and Sudarshan
Database System Concepts, 5 17.18 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Immediate Database Modification Example Log Write Output <T0 start> <T0 , A, 1000, 950> To , B, 2000, 2050 A = 950 B = 2050 <T0 commit> <T1 start> <T1 , C, 700, 600> C = 600 BB, BC <T1 commit> BA Note: BX denotes block containing X. x1
Immediate Database Modification (Cont.) Recovery procedure has two operations instead of one: undo(T)restores the value of all data items updated by T;to their old values,going backwards from the last log record for 7; redo(T)sets the value of all data items updated by T;to the new values,going forward from the first log record for 7; Both operations must be idempotent That is,even if the operation is executed multiple times the effect is the same as if it is executed once Needed since operations may get re-executed during recovery When recovering after failure: Transaction T needs to be undone if the log contains the record <T;start>,but does not contain the record <T;commit>. Transaction T;needs to be redone if the log contains both the record <7;start>and the record <T commit>. Undo operations are performed first,then redo operations. Database System Concepts,5th Edition,Oct 5,2006 17.19 ©Silberschat乜，Korth and Sudarshan
Database System Concepts, 5 17.19 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Immediate Database Modification (Cont.) Recovery procedure has two operations instead of one: undo(Ti ) restores the value of all data items updated by Ti to their old values, going backwards from the last log record for Ti redo(Ti ) sets the value of all data items updated by Ti to the new values, going forward from the first log record for Ti Both operations must be idempotent That is, even if the operation is executed multiple times the effect is the same as if it is executed once Needed since operations may get re-executed during recovery When recovering after failure: Transaction Ti needs to be undone if the log contains the record <Ti start>, but does not contain the record <Ti commit>. Transaction Ti needs to be redone if the log contains both the record <Ti start> and the record <Ti commit>. Undo operations are performed first, then redo operations
Immediate DB Modification Recovery Example Below we show the log as it appears at three instances of time. <To start> <To start> <To start> <T0,A,1000,950> <T0,A,1000,950> <T0,A,1000,950> <T0,B,2000,2050> <T0,B,2000,2050> <T0,B,2000, 2050> <To commit> <To commit> <T start> <T start> <T1,C,700,600> <T1,C,700,600> <T commit> (a) (b) (c) Recovery actions in each case above are: (a)undo(To):B is restored to 2000 and A to 1000. (b)undo(T1)and redo(To):C is restored to 700,and then A and B are set to 950 and 2050 respectively. (c)redo(To)and redo(T):A and B are set to 950 and 2050 respectively.Then C is set to 600 Database System Concepts,5th Edition,Oct 5,2006 17.20 ©Silberschat乜，Korth and Sudarshan
Database System Concepts, 5 17.20 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Immediate DB Modification Recovery Example Below we show the log as it appears at three instances of time. Recovery actions in each case above are: (a) undo (T0 ): B is restored to 2000 and A to 1000. (b) undo (T1 ) and redo (T0 ): C is restored to 700, and then A and B are set to 950 and 2050 respectively. (c) redo (T0 ) and redo (T1 ): A and B are set to 950 and 2050 respectively. Then C is set to 600
Checkpoints Problems in recovery procedure as discussed earlier: 1.searching the entire log is time-consuming 2 we might unnecessarily redo transactions which have already 3.output their updates to the database. Streamline recovery procedure by periodically performing checkpointing 1.Output all log records currently residing in main memory onto stable storage. 2.Output all modified buffer blocks to the disk. 3.Write a log record checkpoint>onto stable storage. Database System Concepts,5th Edition,Oct 5,2006 17.21 ©Silberschat乜，Korth and Sudarshan
Database System Concepts, 5 17.21 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Checkpoints Problems in recovery procedure as discussed earlier : 1. searching the entire log is time-consuming 2. we might unnecessarily redo transactions which have already 3. output their updates to the database. Streamline recovery procedure by periodically performing checkpointing 1. Output all log records currently residing in main memory onto stable storage. 2. Output all modified buffer blocks to the disk. 3. Write a log record < checkpoint> onto stable storage