SQL Server

Database Recovery Methods Part 5

Database Recovery Methods Part – 5

Recovery Methods Based On Delayed Update

The notion behind delayed update a method are to delayed or postpone any definite updates to the database until and unless the transaction finishes its implementation effectively as well as reaches its commit point. At the time of transaction implementation, the updates are logged only in the log in addition to in the cache buffers. When the transaction reaches its commit point as well as the log is force written to disk, the updates are logged in the database. When a transaction is unsuccessful before attaining the commit point, there is no requirement to undo any actions, as the transaction has not affected the database on disk in any method. Though this may make simpler recovery, it cannot be castoff in exercise if transactions are not small as well as every single transaction alters a small number of items. For other kinds of transactions, there is the possibility that the buffer space may run out as transaction modification should be kept in the cache buffers until and unless it reaches the commit point.

At this point of time it can be stated that a classic delayed update procedure can be as follows:

1. Any transaction cannot perform any kind of modification in the database on disk until and unless it has reached its commit point.

2. Any transaction will not reach its commit point until and unless every single update actions of it are logged in the log as well as then the log is force written to disk.

It must be noted that the phases of this procedure is a repetition of the write ahead logging (WAL) procedure. For the reason that the database is certainly not updated on disk until and unless its transaction are committed, therefore there is under no circumstances a need to UNDO any actions. Henceforth, this is known as the NO UNDO or REDO retrieval algorithm. REDO is desirable at the time when the system failure occurs after a transaction is committed however before all its modifications are logged in the database on disk. In this circumstance, the transaction actions are repeated from the log entries.

Typically, the technique of retrieval from failure is thoroughly connected to the concurrency control process in multi user systems. First of all it can be discussed about the retrieval in single user systems, where no concurrency control is wanted, so that an individual can recognize the retrieval procedure independently of any concurrency control technique. Next, it will be discussed by what means concurrency control may affect the retrieval procedure.

Retrieval Using Delayed Update in a Single User Environment

In such a setting, the retrieval algorithm can be somewhat easy one. The algorithm Retrieval using Delayed Update in a Single User (RDU – S) environment practices a REBUILD process, given next, for rebuilding some of the Write_Item processes; its workings are given as follows:

PROCESS (RDU – S): It makes usage of two (2) lists of transactions: the committed transactions as the last check point, in addition to the active transactions, at the most one (1) transaction will be in this category, as the system is single user. Apply the REBUILD process to every WRITE_ITEM procedures of the committed transactions from the log in the sequence in which they have been written to the log. Resume the active transactions.

The REBUILD procedure is demarcated as follows:

REDO ( WRITE_OPERATION ) : Recreating a Write_Item operation WRITE_OPERATION contains of investigating its log record [ Write_Item, T, X, New_Value ] as well as setting the data of item X in the database to New_Value, which is the After Image (AFIM).

In reality, the full retrieval procedure must be denoted which is for the reason that, if the system were to fail in the course of the retrieval procedure, the subsequent retrieval attempt might REBUILD certain Write_Item processes which had already been rebuilt at the time of first recovery procedure. The outcome of recovery from a system crash at the time of recovery must be the similar as the consequence of refining at the time when there is no database crash in the course of recovery. The sole transaction in the active list will have had no influence on the database for the reason that of the delayed update procedure, and it is overlooked totally by the recovery process as none of its processes were replicated in the database on disk. Though, this transaction should be resumed now, either automatically by means of the recovery process or else manually by the end user.

Figure 1.1 displays an instance of recovery in a single user situation, where the first (1st) failure happens in the course of implementation of transaction T2 as shown in Figure 1.2-B. The recovery procedure will redo the [Write_Item, T1, A, 10] entry in the log by means of rearranging the data item A to 10 (it’s a fresh data). The [Write, T2… N], entries in the log are overlooked by means of the recovery procedure for the reason that T2 is not committed. If a second (2nd) failure happens at the time of recovery from the first (1st) failure, the identical recovery procedure is reiterated from start to finish, with matching results.




Read_Item (A)

Read_Item (C)

Read_Item (B)

Write_Item (C)

Write_Item (B)

Read_Item (B)

Write_Item (B)


[Start transaction, T1]

[Write_Item, T1, B, 10]

[Commit, T1]

[Start transaction, T2]

[Write_Item, T2, C, 20]

[Write_Item, T2,B, 45]

System Crash

The [Write_Item… N] operations of T1 are redone.

** T2 log entries are ignored by the recovery process

Figure 1.1 An example of recovery using deferred update in a single User environment.

(a) The read and write operations of two (2) transactions.

(b) The System log at point of crash.

In the upcoming part we will be discussing Postponed Update with Simultaneous Execution in a Multi User Setting and Transaction Activities which are Ineffective for the Database.