Resources » SQL Server

Techniques for Recovery from transaction failures in database


Posted Date: 28-Jan-2013  Last Updated:   Category: SQL Server    
Author: Member Level: Gold    Points: 40


Recovery from transaction failures usually means that the database is restored to the most recent consistent state just before the time of failures. To do this the system must keep the information about the changes that were applied to data items by the various transaction. This information is typically kept in the System log.



The system must be prepared to recover ,not only from local failures but also from global failures. A local failures such as the occurrence of an overflow condition within an individual transaction, affects only the transaction in which the failure has occurred. A global failure such as a power failure on the CPU affects all the transaction in progress at the time of the failure , and hence has significant system wide implications
Global failures fall into Two categories
1. Media Failures
2. System Failures
Media Failures Head crash on the disk is an example of media failures. Media failures cause damage to the database, or to some portion of it and affect at least those transaction currently using that portion. A media failure is also known as hard crash.
Recovery from such a failures basically involves restoring the database from a backup copy was taken.

System failures: power failures is an example of system failures. system failures affect all transactions currently in progress but do not physically damage the database. A system failure is also known as Soft crash . in this type of failure, critical point is that the contents of main memory are lost. the precise state of any transaction that was in progress at the time of the failure is therefore no longer known and so must by undone(rollback) when the system restarts. It might also be necessary toredo certain transactions at restart time that did successfully completed prior to the crash but did not manage to get their updates transferred from the data base buffers to the physical database.

There are two Recovery Techniques:


1. Deferred Update
2. Immediate Update

Recovery Techniques Based On Deferred Update

The idea behind deferred update techniques is to defer or postpone any actual updates to the database until the transaction completes its execution successfully and reaches its commit point. During transaction execution, the updates are recorded only in the log and in the cache buffers. After the transaction reaches its commit point and the log is force-written to disk, the updates are recorded in the database. If a transaction fails before reaching its commit point, there is no need to undo any operations, because the transaction has not affected the database on disk in any way. Although this may simplify recovery, it cannot be used in practice unless transactions are short and each transaction changes few items. For other types of transactions, there is the potential for running out of buffer space because transaction changes must be held in the cache buffers until the commit point.
We can state a typical deferred update protocol as follows:
1. A transaction cannot change the database on disk until it reaches its commit point.
2. A transaction does not reach its commit point until all its update operations are recorded in the log and the log is force-written to disk.

Notice that step of this protocol is a restatement of the write-ahead logging (WAL) protocol. Because the database is never updated on disk until after the transaction commits, there is never a need to UNDO any operations. Hence, this is known as the NO UNDO/ REDO recovery algorithm. REDO is needed in case the system fails after a transaction commits but before all its changes are recorded in the database on disk. In this case, the transaction operations are redone from the log entries.

Recovery Techniques Based On Immediate Update
In these techniques, when a transaction issues an update command, the database can be updated "immediately," without any need to wait for the transaction to reach its commit point. In these techniques, however, an update operation must still be recorded in the log (on disk) before it is applied to the database-using the write-ahead logging protocol-so that we can recover in case of failure.
If a transaction fails after recording some change in the database but before reaching its commit oint, the effect of its operations on the database must be undone; that is, the transaction must be rolled back. In the general case of immediate update both undo and redo may be required during recovery. This technique known as the UNDO/REDO algorithm, requires both operations and is used most often in practices. A variation of the algorithm where all updates are recorded in the database before a transaction commits requires undo only, so it is known as the UNDO/REDO algorithm.


Did you like this resource? Share it with your friends and show your love!

Responses to "Techniques for Recovery from transaction failures in database"
Author: dineshkumar    03 Oct 2013Member Level: Silver   Points : 0
what is transaction


Author: Rakesh Chaubey    03 Oct 2013Member Level: Gold   Points : 3
Transaction is like ..Suppose u have 1000 rows in excel and what you are goign to do is insert the data in database ...You never when error can happen which in turn lead to 500 inserted 500 remaining ...So as to avoid that you can use transaction . In transaction if there is a failure then not even a single record will go in db ...All data will be reversed


Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    C.Srinivasan
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India