DATABASE DESIGN I - 1DL300 Autumn 2012 An Introductory Course on Database Systems http://www.it.uu.se/edu/course/homepage/dbastekn/ht12/ Uppsala Database Laboratory Department of Information Technology, Uppsala University, Uppsala, Sweden Silvia Stefanova, UDBL - IT - UU 2012-12-04 1
Introduction to Recovery Techniques Elmasri/Navathe ch 22 Padron-McCarthy/Risch ch 23 and 240 Silvia Stefanova Department of Information Technology Uppsala University, Uppsala, Sweden Silvia Stefanova, UDBL - IT - UU 2012-12-04 2
Recovery • Recovery – the database is restored to the most consistent state just before the time of failure. • Two typical strategies for recovery: Recovery from a catastrophic failure , e.g. disk crash – it is restored a past copy of the database that was backed up to archival storage Recovery from a no catastrophic failure : Identify any changes that may cause an inconsistency in the database Undo/Redo some operations to restore a consistent state of the database It is not needed a complete archival copy of the database. Entries kept in the system log on disk are analysed to determine the appropriate actions for recovery. Silvia Stefanova, UDBL - IT - UU 2012-12-04 3
Scenario 1) Tom has no money in his bank account. 2) Alice has 300 SEK. She transfers 200 SEK of her money to the bank account of Tom. 3) John has 1500 SEK. He transfers 100 SEK to the bank account of Tom. 4) Bob has 500 SEK. He transfers 50 SEK to the bank account of Alice. Then the final balance should be: Alice’s bank account: 150 SEK Bob’s bank account : 450 John’s bank account: 1400 Tom’s bank account: 300 SEK 2300 SEK Silvia Stefanova, UDBL - IT - UU 2012-12-04 4
Transaction T1 Transaction T2 Transaction T3 Read Alice’s balance Read A_balance ( 300 ) Write Alice’s balance back to the database Write A_balance A_balance= A_balance – 200 ( 100 ) Read Tom’s balance Read T_balance (0) Write Tom’s balance back to the database Write T_balance T_balance= T_balance + 200 ( 200 ) Commit T1 Checkpoint Checkpoint Checkpoint Read John’s balance R ead J_balance; Write John’s balance back to the database Write J_balance J_balance= J_balance – 100 (1400) Read Bob’s balance Read B_balance ( 500) Write Bob’s balance back to the database Write B_balance B_balance= B_balance - 50 (450) Read Tom’s balance Read T_balance Write Tom’s balance back to the database Write T_balance T_balance= T_balance + 100 ( 300 ) Commit T2 Read Alice’s balance Read A_balance Write Alice’s balance back to the database Write A_balance A_balance= A_balance + 50 (150) Commit T3 Silvia Stefanova, UDBL - IT - UU 2012-12-04 5
Scenario • The system crashes before updating the Alice’s account with 50 SEK transferred by Bob. • Transaction T3 fails before coming to its commit point. Silvia Stefanova, UDBL - IT - UU 2012-12-04 6
Transaction T1 Transaction T2 Transaction T3 Read Alice’s balance Read A_balance ( 300 ) Write Alice’s balance back to the database Write A_balance A_balance= A_balance – 200 ( 100 ) Read Tom’s balance Read T_balance (0) Write Tom’s balance back to the database Write T_balance T_balance= T_balance + 200 ( 200 ) Commit T1 Checkpoint Checkpoint Checkpoint Read John’s balance R ead J_balance; Write John’s balance back to the database Write J_balance J_balance= J_balance – 100 (1400) Read Bob’s balance Read B_balance ( 500) Write Bob’s balance back to the database Write B_balance B_balance= B_balance - 50 (450) Read Tom’s balance Read T_balance Write Tom’s balance back to the database Write T_balance T_balance= T_balance + 100 ( 300 ) Commit T2 System crash System crash Read Alice’s balance Read A_balance Write Alice’s balance back to the database Write A_balance A_balance= A_balance + 50 (150) Commit T3 Silvia Stefanova, UDBL - IT - UU 2012-12-04 7
Scenario • An inconsistency can be introduced in the database. Alice: 100 SEK Bob : 450 John: 1400 Tom: 300 SEK 2250 SEK !!! 50 SEK missing • Transaction T3 is not committed and it has to be rolled back. Silvia Stefanova, UDBL - IT - UU 2012-12-04 8
Buffering of disk blocks • Multiple disk pages (blocks) including data from the database to be updated are cached into main memory buffers. • The disk pages are then first updated in the main memory. • Later on the disk pages are written back (flushed) to disk. • DBMS cache is a collection of in memory buffers controlled by the DBMS. • Two main strategies for flushing a modified buffer to disk: In-place updating : writes the buffer to the original location (overwrite the old value of a data item) Shadowin g: writes un updated buffer to a different disk location (multiple versions of a data item can be maintained) Silvia Stefanova, UDBL - IT - UU 2012-12-04 9
Logging • Write-ahead logging - flush system log to disk before updating the database • Disk page management (rules for when a page form the database can be written from the cache back to disk): No-steal approach – an updated page by a transaction cannot be written to disk before transaction commits. Steal approach – an updated page can be written back to disk before transaction commits Force approach - all updated pages are immediately written to disk before transaction commit No-force approach - not all update pages are written to disk before transaction commits Silvia Stefanova, UDBL - IT - UU 2012-12-04 10
Recovery concepts – System log • System log: information about changes applied to the data items by the various transactions. • REDO-type log entry New value of a data item written by the operation (AFIM – after image) Needed to REDO the effect of the operation from the log • UNDO-type entry Old value of a data item (BFIM – before image) Needed to UNDO the effect of the operation from the log Silvia Stefanova, UDBL - IT - UU 2012-12-04 11
Recovery concepts – System log • Checkpoint entry Written in the log when the system writes out to the database on disk all the data buffers that have been modified. Periodical entry The recovery manager of a DBMS must decide at what intervals to take checkpoint. Silvia Stefanova, UDBL - IT - UU 2012-12-04 12
Recovery concepts – System log Entry Explanation Start, T Marks the start for transaction T Write,T,X,old_val,new_val Marks that T changes the value of X from old_val to new_val . Read T, X Marks that T reads the value of X Commit T Marks that T is finished with all accesses and its effect can be introduced in the database. Check point Writes out on disk modified buffers Silvia Stefanova, UDBL - IT - UU 2012-12-04 13
Rollback • If a transaction fails for whatever reason after updating the database but before the transaction commits, it may be necessary to roll back the transaction . • If any data item value has been changed by the transaction and written to the database, they must be restored to their previous values (BFIM). • The UNDO-type log entries are used to restore the old values of data items that must be rolled back. Silvia Stefanova, UDBL - IT - UU 2012-12-04 14
Rollback • Cascading rollback: when rollback is applied to a transaction T, we must apply rollback to all transactions S that have read item values that has been updated by T. We must then do the same for transactions that have read values that each such S has updated and so forth. • (Read, T, ...) records in the log file are used to decide if cascading rollback is required or not. • Example: Write T1,A .. Read T2,A Commit T2 Write T1,B System Crash o T1 is rolled back because it did not commit o T2 is rolled back because it reads value of A written by T1. Silvia Stefanova, UDBL - IT - UU 2012-12-04 15
Recovery technique based on Immediate Update • Immediate Update – The database may be updated by some operations of a transaction before the transaction reaches its commit point. • System log: Store the UNDO-type log entities (BFIMs) Ability to UNDO the effect of transaction's update by rolling back • Steal strategy Silvia Stefanova, UDBL - IT - UU 2012-12-04 16
Recovery technique based on Immediate Update 1) The recovery technique ensures that all updates of a transaction are recorder in the database on disk before the transaction commits. REDO is never needed UNDO/NO-REDO recovery algorithm Utilizes the force strategy Silvia Stefanova, UDBL - IT - UU 2012-12-04 17
Recovery technique based on Immediate Update 2) The recovery technique ensures that some updates of a transaction are recorder in the database on disk before the transaction commits. Most general case UNDO/REDO recovery algorithm Utilizes the steal/no-force strategy Silvia Stefanova, UDBL - IT - UU 2012-12-04 18
UNDO-REDO with checkpoints 1) Use two lists of transactions: List 1 - committed transactions since last checkpoint CT List 2 – active transactions AT 2) Undo the write-operations of the AT. The operations should be undone in the reverse of the order in which they were written in the log. 3) Redo all write-operations of the CT from the log in the order in which they were written in the log. Silvia Stefanova, UDBL - IT - UU 2012-12-04 19
Recommend
More recommend
