Concurrency Control and Recovery With concurrent transactions,all transactions share a single disk buffer and a single log A buffer block can have data items updated by one or more transactions We assume that if a transaction Ti has modified an item,no other transaction can modify the same item until T;has committed or aborted i.e.,the updates of uncommitted transactions should not be visible to other transactions Otherwise,how to perform undo if T,updates A,then T2 updates A and commits,and finally T,has to abort? Can be ensured by obtaining exclusive locks on updated items and holding the locks till end of transaction(strict two-phase locking) Log records of different transactions may be interspersed in the log. Database System Concepts-7th Edition 19.16 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 19.16 ©Silberschatz, Korth and Sudarshan th Edition Concurrency Control and Recovery ▪ With concurrent transactions, all transactions share a single disk buffer and a single log • A buffer block can have data items updated by one or more transactions ▪ We assume that if a transaction Ti has modified an item, no other transaction can modify the same item until Ti has committed or aborted • i.e., the updates of uncommitted transactions should not be visible to other transactions ▪ Otherwise, how to perform undo if T1 updates A, then T2 updates A and commits, and finally T1 has to abort? • Can be ensured by obtaining exclusive locks on updated items and holding the locks till end of transaction (strict two-phase locking) ▪ Log records of different transactions may be interspersed in the log
Undo and Redo Operations Undo and Redo of Transactions undo(7 )--restores the value of all data items updated by 7;to their old values,going backwards from the last log record for 7; Each time a data item X is restored to its old value V a special log record <Ti,X,V>is written out When undo of a transaction is complete,a log record <7;abort>is written out. redo(7)--sets the value of all data items updated by 7;to the new values,going forward from the first log record for 7; No logging is done in this case Database System Concepts-7th Edition 19.17 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 19.17 ©Silberschatz, Korth and Sudarshan th Edition Undo and Redo Operations ▪ Undo and Redo of Transactions • 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 ▪ Each time a data item X is restored to its old value V a special log record <Ti , X, V> is written out ▪ When undo of a transaction is complete, a log record <Ti abort> is written out. • 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 ▪ No logging is done in this case
Recovering from Failure When recovering after failure: Transaction 7;needs to be undone if the log Contains the record <7;start>, But does not contain either the record <T commit>or <T;abort>. Transaction T;needs to be redone if the log Contains the records <T;start> And contains the record <T;commit>or<T;abort> Database System Concepts-7th Edition 19.18 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 19.18 ©Silberschatz, Korth and Sudarshan th Edition Recovering from Failure ▪ When recovering after failure: • Transaction Ti needs to be undone if the log ▪ Contains the record <Ti start>, ▪ But does not contain either the record <Ti commit> or <Ti abort>. • Transaction Ti needs to be redone if the log ▪ Contains the records <Ti start> ▪ And contains the record <Ti commit> or <Ti abort>
Recovering from Failure(Cont.) Suppose that transaction 7;was undone earlier and the <T;abort>record was written to the log,and then a failure occurs, （ On recovery from failure transaction T;is redone 。 Such a redo redoes all the original actions of transaction T;including the steps that restored old values Known as repeating history Seems wasteful,but simplifies recovery greatly Database System Concepts-7th Edition 19.19 ©Silberscha乜，Korth and Sudarshan
Database System Concepts - 7 19.19 ©Silberschatz, Korth and Sudarshan th Edition Recovering from Failure (Cont.) ▪ Suppose that transaction Ti was undone earlier and the <Ti abort> record was written to the log, and then a failure occurs, ▪ On recovery from failure transaction Ti is redone • Such a redo redoes all the original actions of transaction Ti including the steps that restored old values ▪ Known as repeating history ▪ Seems wasteful, but simplifies recovery greatly
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,and log records <To,B,2000>,<To,A,1000>,<To,abort>are written out (b)redo(To)and undo (T):A and B are set to 950 and 2050 and C is restored to 700.Log records <T1,C,700>,<T1,abort>are written out. (c)redo(To)and redo(T1):A and B are set to 950 and 2050 respectively.Then C is set to 600 Database System Concepts-7th Edition 19.20 @Silberschatz,Korth and Sudarshan
Database System Concepts - 7 19.20 ©Silberschatz, Korth and Sudarshan th Edition 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, and log records <T0 , B, 2000>, <T0 , A, 1000>, <T0 , abort> are written out (b) redo (T0 ) and undo (T1 ): A and B are set to 950 and 2050 and C is restored to 700. Log records <T1 , C, 700>, <T1 , abort> are written out. (c) redo (T0 ) and redo (T1 ): A and B are set to 950 and 2050 respectively. Then C is set to 600