Recoverability of Schedule in DBMS

In this guide, you will learn a very important concept in DBMS: Recoverability of Schedule. There are times when few transactions in a schedule fail, due to a software or hardware issue. In that case, it becomes important to rollback these failed transactions along with those successful transactions that have used the value updated by failed transactions.

What is an Irrecoverable Schedule?

A schedule that cannot be rolled back because some transactions already used COMMIT to make the changes permanent in database and these transactions have used values produced by the failed transactions. These types of schedules are called irrecoverable schedules.

For example: In this example, we have a schedule that contains two transactions T1 and T2. Transaction T1 reads X and make changes in the value of X and then writes the updated value of X.

This updated value of X is read by transaction T2, which then did some change in X and finally write the value of X and used COMMIT statement to make the changes permanent.

After the changes are made permanent by transaction T2, the transaction T1 failed and it had to be rolled back but the problem here is that T2 has already used commit statement so it cannot be rolled back. This is why this schedule is irrecoverable because it cannot be successfully rolled back even after the failure of one of the transaction.

 T1             T2
----           ----
Read(X)      
X = + 20
Write (X)
               Read(X)
               X = X + 100
               Write(X)
               Commit

Failed!
Rollback

What is a Recoverable Schedule?

A schedule that can be successfully rolled back in case of any failure is known as recoverable Schedule.

For example: Let’s take the same example that we have seen above with some modifications. Here we have moved the commit statement in transaction T2 after the commit statement in transaction T1.

T1             T2
----           ----
Read(X)      
X = + 20
Write (X)
               Read(X)
               X = X + 100
               Write(X)
Commit
               Commit

Now let’s consider some cases of failure to understand whether this schedule can be successfully rolled back.

Case 1: When T1 fails just before the commit statement. In this case both the transactions can be rolled back as none of the transactions used COMMIT statement before the failure point in schedule.

T1             T2
----           ----
Read(X)      
X = + 20
Write (X)
               Read(X)
               X = X + 100
               Write(X)
Failed!
Commit
               Commit

Case 2: Let’s say T2 failed after the commit statement in T1. This is also recoverable as the T2 can be rolled back and T1 didn’t read value of X after write(X) in T1 so no bad read operation here, so no need to rollback the T1 in this case.

T1             T2
----           ----
Read(X)      
X = + 20
Write (X)
               Read(X)
               X = X + 100
               Write(X)
Commit
               Failed!
               Commit

You can also try to put failure points in some places in this schedule other than the above two cases, you will find that the schedule is recoverable.