Introduction to Recovery Techniques Elmasri/Navathe ch 22 - - PowerPoint PPT Presentation

2012-12-04 1 Silvia Stefanova, UDBL - IT - UU

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

2012-12-04 2 Silvia Stefanova, UDBL - IT - UU

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

2012-12-04 3 Silvia Stefanova, UDBL - IT - UU

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.

2012-12-04 4 Silvia Stefanova, UDBL - IT - UU

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

• f 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

2012-12-04 5 Silvia Stefanova, UDBL - IT - UU

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 Read 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

2012-12-04 6 Silvia Stefanova, UDBL - IT - UU

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.

2012-12-04 7 Silvia Stefanova, UDBL - IT - UU

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 Read 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

System crash System crash

2012-12-04 8 Silvia Stefanova, UDBL - IT - UU

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.

2012-12-04 9 Silvia Stefanova, UDBL - IT - UU

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)

• Shadowing: writes un updated buffer to a different disk location

(multiple versions of a data item can be maintained)

2012-12-04 10 Silvia Stefanova, UDBL - IT - UU

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

2012-12-04 11 Silvia Stefanova, UDBL - IT - UU

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

2012-12-04 12 Silvia Stefanova, UDBL - IT - UU

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.

2012-12-04 13 Silvia Stefanova, UDBL - IT - UU

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

• ld_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

2012-12-04 14 Silvia Stefanova, UDBL - IT - UU

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.

2012-12-04 15 Silvia Stefanova, UDBL - IT - UU

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

• T1 is rolled back because it did not commit
• T2 is rolled back because it reads value of A written by T1.

2012-12-04 16 Silvia Stefanova, UDBL - IT - UU

Recovery technique based on Immediate Update

• Immediate Update – The database may be updated by some
• perations 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

2012-12-04 17 Silvia Stefanova, UDBL - IT - UU

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

2012-12-04 18 Silvia Stefanova, UDBL - IT - UU

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

2012-12-04 19 Silvia Stefanova, UDBL - IT - UU

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.

2012-12-04 20 Silvia Stefanova, UDBL - IT - UU

Recovery technique based on Deferred Update

• Deferred Update – The database is not physically updated on

disk until after a transaction is committed.

• During the transaction updates are reordered only in the log and

in the cache buffers.

• If a transaction fails before commit there is no need to UNDO

any operation.

• No UNDO is required during recovery.
• System log:
• store only the REDO-type log entities (AFIMs)

2012-12-04 21 Silvia Stefanova, UDBL - IT - UU

Recovery technique based on Deferred Update

• It can’t be used in practice unless transactions are short.

Otherwise it can happen run out of buffer space.

• NO-UNDO/REDO recovery algorithm

2012-12-04 22 Silvia Stefanova, UDBL - IT - UU

NO-UNDO/REDO with checkpoints

1) Use two lists of transactions:

• List 1 - committed transactions since last checkpoint CT
• List 2 – active transactions AT

2) Redo all write-operations of the CT from the log. The operations should be redone in the order in which they were written in the log. 3) The AT are cancelled.

2012-12-04 23 Silvia Stefanova, UDBL - IT - UU

Shadow paging

• A simple way to implement a recovery manager.
• It does not require a log manager.
• Main idea: stores all of a transaction’s updates in a shadow

copy of the database. There is also a master copy of the database, whose state represents the execution of all committed transactions and no aborted ones. When the transaction commits, the shadow copy is swapped with the master copy of the database, thereby installing the updates.

• It is not widely used in commercial products because it does not

scale up to high transaction rates as well as logging. Executing a single transaction requires copying the entire database.

2012-12-04 24 Silvia Stefanova, UDBL - IT - UU

Shadow paging

Old copy of the database Old copy of the database New copy of the database

db pointer db pointer

Before update After update

2012-12-04 25 Silvia Stefanova, UDBL - IT - UU

Summary

• Recovery
• Logging
• Write-ahead logging
• BFIM, AFIM
• Check point
• Transaction rollback
• Recovery technique based on Immediate Update
• Recovery technique based on Deferred Update