Chapter 19 Notes:
Database Recovery Techniques
Some definitions first from Chapter 17 -
Recoverable schedule: A schedule for which once a transaction has committed, it never needs to be rolled back. A schedule S is recoverable if no transaction T in S commits until all transactions T' that have written an item that T reads have committed.
Cascading rollback: a rollback wherein an uncommitted transaction must be rolled back because it read an item from another transaction that failed (and thus was rolled back as well).
System Log -
This was discussed back in Chapter 17. The types of entries in the log are :
[start_transaction, T1]
[write_item, T1, X, old_value, new_value]
[read_item, T1, X]
[commit, T1]
[abort, T1]
(we'll learn about adding checkpoint records to the log later)
At the point in time where a transaction has written a commit record in the system log, we can be assured that all steps of the transaction have been recorded in the system log, and thus that the net effect of the transaction can be obtained by "rolling forward" through the system log, making all the data item changes, for all the steps for that transaction.
Similarly, if a transaction should fail in the middle (e.g. we have a system crash) of a transaction and we do not have all the steps of the transaction, then we can roll backwards through the steps in the log "undoing" the changes to the data items.
Caching (Buffering of Disk Blocks) - The database items that have been most recently changed or read are held in an area of OS cache. Oracle calls this the database buffer cache. When an item from the database is requested, the DBMS first checks to see if this item is already resident in the database buffer cache. If so, it will not need to go out and do a disk retrieval, saving time.
There are 2 main strategies for flushing a modified buffer back to disk -
1) In-place updating - this strategy writes the buffer back to the same, original disk location, overwriting the old value of any changed data items. A log must be used for recovery in this situation
2) Shadowing - this strategy writes an updated buffer to a new location, so multiple versions can be maintained. It keeps an old image (before image or BFIM) and a separate new image (after image or AFIM) for any data being modified. It is not strictly necessary to maintain a log with shadowing.
Write-Ahead Logging : When in-place updating is used, for recovery purposes the log must first record the BFIM in the log before it is overwritten by the AFIM on disk. That is, entries must be written in the log "ahead" of writing changes to disk.
No-Steal Approach: If a buffer (cache page) updated by a transaction cannot be written to disk (modified in the database on disk) before a transaction commits.
Steal Approach: Allows a buffer to be flushed to the db on disk before a transaction commits. The advantage of steal is that it avoids needing a large buffer space to store all updated pages in memory
Force Approach: All pages updated by a transaction are immediately written to disk when the transaction commits
No-Force : Not all updated pages must be immediately written to disk when a transaction commits. The advantage is that an updated page of a committed transaction may still be in memory when another transaction needs to update it, saving the cost of a disk I/O
Most databases use a steal/no-force strategy because of these advantages.
Checkpoints in the System Log:
Taking a checkpoint consists of -
1) Suspend execution of ongoing transactions temporarily
2) Force-write all main memory buffers that have been modified to disk
3) Write a [checkpoint] record to the log and force-write the log to disk
4) Resume execution of transactions
A checkpoint record consists of: a list of active transactions ids, and the locations (addresses) in the log of the first and most recent (last) record in the log for each active transaction.
Checkpoints ensure that recovery will only take just "so much" effort. Committed transactions (i.e. transactions that have there commit records in the log prior to a checkpoint record in the log) never need to be redone! Why? Because we know they have had all there db changes flushed out to the db on disk already!!
Fuzzy Checkpointing - To reduce the delay caused by step 2, we allow the system to resume transaction processing before step 2 is complete. However, the previous checkpoint is valid. The system will maintain a pointer to the valid checkpoint record in the log (from whence to initiate recovery).
Transaction Rollback : Most recovery mechanisms are designed so that cascading rollback is not required - since it would be so time consuming. Rollback involves moving "backwards" through the log, changing back to the "old value" for data items.
Recovery Techniques Based on Deferred Update - The idea is to defer or postpone any actual changes to the db until the transaction reaches its commit point. Drawbacks - requires a large buffer. Advantages - No rollback/undo is required for transactions.
Also called the NO-UNDO/REDO recovery algorithm. A redo of a write operation involves examining the log entry and setting the value of a data item to the new value. In deferred update, the log entry for a write operation need only consist of -
[write_item, T, X, new_value]
Algorithm for Deferred Update with Concurrent Execution in a Multiuser Environment: RDU_M with checkpoints -
Use 2 lists of transactions maintained by the system:
1) Commit list - transactions T that have committed since the last checkpoint
2) Active list - active Transactions T'
REDO all the write operations of the committed transactions in the log by rolling forward through the log (doing the steps in the order in which they were to be done)
Transactions that were active but not committed must be resubmitted as jobs since nothing, in effect, was done for these transactions!
Recovery Techniques Based on Immediate Update - Here the database buffer cache can be flushed to disk without having to wait for a transaction to commit. Advantage - do not need to have a huge buffer. Disadvantage - must also have a rollback/undo for transactions. If a transaction is allowed to write a commit record to the log before all its changes are flushed to the db (most general form) then we have a UNDO/REDO recovery algorithm. This is the most complex. We must rollback uncommitted transactions and roll forward (redo) transactions for which there commits are after the last checkpoint.
Algorithm for Immediate Update with Concurrent Execution in a Multiuser Environment: RIU_M with checkpoints -
Use 2 lists of transactions maintained by the system:
1) Commit list - transactions T that have committed since the last checkpoint
2) Active list - active Transactions T'
UNDO all the write_item operations of the active but uncommitted transactions using the undo (rollback) procedure. This rolls backwards through the log from the most recent operation of the transaction, backing up to the start of the transaction.
REDO all the write operations of the committed transactions in the log by rolling forward through the log (doing the steps in the order in which they were to be done)
Do problems 19.21 through 19.24 in your text!
Shadow Paging - the old 'shadow pages' are kept and in case of transaction failure, restored.
Aries Algorithm - a steal/no-force approach based on
1) write-ahead logging
2) repeating history during redo (ARIES retraces all actions of the database prior to the crash to reconstruct the database when the crash occurred)
3) logging changes during undo (this prevents ARIES from repeating the completed undo operations if a failure occurs during recovery, causing a restart of the recovery process)
Details in your text, not responsible for these on final exam.