SQL Server

Database Recovery Methods Part 6

Database Recovery Methods Part – 6

Postponed Update with Simultaneous Execution in a Multi User Setting

For multi user systems with concurrency control, the recovery procedure might be a bit more difficult, depending on the protocols castoff for concurrency control. In a lot of circumstances, the concurrency control as well as recovery processes are interconnected. In overall, the greater the degree of concurrency, the more the time taking task it becomes at the time of recovery for an individual.

Consider a system in which concurrency control practices strict two (2) phase locking, thus the locks on items continue the consequence until the transaction reaches its commit point. After which the locks can be freed. This confirms strict as well as serializable schedules. Supposing that [ CHECKPOINT ] entries are involved in the log, a probable recovery algorithm for this situation, is what known as RDU_M (Recovery Using Deferred Update for a Multi user setting), is given next. This process makes use of the REDO process demarcated previously. METHOD RDU_M (WITH CHECKPOINTS): make use of two (2) lists of transactions preserved through the system: the committed transactions TC as the last checkpoint (commit list), in addition to the active transactions TA (active list). REDO every WRITE operations of the committed transactions from the log, in the sequence in which they were written into the log. The transactions which are active plus did not have committed are effectually are cancelled in addition to should be resubmitted.

Figure 1.3 displays a likely schedule of implementing transactions. At the time when the checkpoint was consider at time t, transaction T had committed, however transactions T3 and T4 had not. Before the system crash down at time t1, T3 as well as T2 were committed however T4 and T5 are not committed. As per the RDU_M method, there is no necessity to redo the Write_Item operations of transaction T1 or any other transactions committed before the last checkpoint time t. Write_Item operations of T2 as well as T3 should be repeated.

T1

T2

T3

T4

T5

t1 t2

Time

CHECKPOINT

SYSTEM CRASH

FIGURE 1.3 An example of recovery in a multiuser environment their commit points after the last checkpoint.

If an individual can remember that the log is force written before committing a transaction. Transactions T4 and T5 are overlooked: They are efficiently cancelled or rolled back for the reason that none of their Write_Item operations were logged in the database under the delayed update protocol. An individual can refer to the figure 1.3 later to demonstrate other recovery procedures. An individual can make use of NO-UNDO/REDO recovery algorithm for more efficiency by noting that, when an information item X has been updated as indicated in the log entries more than once by means of committed transactions as the last checkpoint, it is only necessary to REDO the last update of X from the log at the time of recovery. The additional updates will be overwritten by means of this last REDO in any circumstance. In this situation, an individual can start from the end of the log; then, on every occasion when an item is recreated, it is added to a list of recreated items. Before REDO is implemented on an item, the list is examined; when the item appears on the list, it is not recreated all over again, as its last value has already been recovered. When a transaction is terminated for any reason say, for an example by means of the deadlock detection technique, it is basically resubmitted, as it has not altered the database on disk. A disadvantage of the technique defined here is that it restricts the synchronized execution of transactions as every item remains locked in anticipation of the transaction reaching the commit point. Moreover, it might need extreme buffer space to hold every updated items until the transactions are committed. The method’s main advantage is that transaction operations never need to be undone, for two (2) reasons:

1. A transaction does not make record of any modifications in the database on disk until after it reaches its commit point that is, until it completes its execution successfully. Hence, a transaction is never rolled back because of failure during transaction execution.

2. A transaction will certainly not read the data of an item which is written by means of an uncommitted transaction, because items continues to be locked until a transaction reaches its commit point. Therefore, no cascading roll back will happen. Figure 1.4 demonstrates an instance of recovery for a multi user system which makes use of the recovery as well as concurrency control technique just defined.

Transaction Activities which are Ineffective for the Database

In overall, a transaction will have activities which are not affecting the database, like – creating and printing messages or reports from the facts retrieved from the database. When a transaction fails before getting executed successfully, an individual might not want the end user to have these reports, as the transaction has failed to execute successfully. When these inaccurate reports are generated, portion of the recovery procedure will have to notify the end user that these reports are incorrect, as the end user might take an action depending on these reports which will put some effect on the database. Therefore, these types of reports must be created only after the transaction gets executed successfully and reaches its commit point. A well – used technique of handling with such actions is to schedule the commands which will create the reports but will execute the commands as batch jobs, which are implemented only after the transaction gets executed successfully and reaches its commit point. When the transaction does not get executed successfully and it fails, then the batch jobs are also cancelled.

T1

T2

T3

T4

a

Read_Item (A)

Read_Item (B)

Read_Item (A)

Read_Item (B)

Read_Item (D)

Write_Item (B)

Write_Item (A)

Write_Item (B)

Write_Item (D)

Read_Item (D)

Read_Item (C)

Read_Item (A)

Write_Item (D)

Write_Item (C)

Write_Item (A)

b

*

**

**

[Start transaction, T1]

[Write_Item, T1, D, 23]

[Commit, T1]

[Checkpoint]

[Start transaction, T4]

[Write_Item, T4, B, 20]

[Write_Item, T4, A, 15]

[Commit, T4]

[Start transaction, T2]

[Write_Item, T2, B, 28]

[Start transaction, T3]

[Write_Item, T3, A, 45]

[Write_Item, T2, D, 25]

System Crash

T2 and T3 are ignored because they did not reach their commit points.

T4 is redone because the commit point is after the last system checkpoint

Figure 1.4 An example of recovery using deferred update with concurrent transactions.

(a) The READ and WRITE operations of four transactions.

(b) System log at the point of crash. .

In the upcoming part we will be focusing on Recovery Methods Based On Instant Update, UNDO/REDO Recovery Based on Instant Update in a Single User Situation and UNDO/REDO Recovery Based on Instant Update with Simultaneous Execution.

Facebooktwittergoogle_plusredditpinterestlinkedinmail