Intro to DBMS

Recoverability of Schedule

Sometimes, a transaction may not execute completely due to some problems, i.e., hardware failure, software issue, system crash, etc. In that case, the failed transaction has to be rolled back to its starting point of time. If rollback is done successfully, then it will be a recoverable schedule; otherwise, it will be an irrecoverable schedule.

Keep in Mind: Committed transaction can never be a rollback

1. Irrecoverable Schedule

When some values of a failed transaction (say T1) are read by some other transaction (say T2), and T2 committed before T1 and T1 fail before committing, as given below. Then, this case will be irrecoverable.

Recoverability of Schedule: Irrecoverable Schedule

 

Explanation

The above irrecoverable schedule shows two transactions (T1 and T2). T1 reads and writes the value of data “A”. T2 also reads the same value of “A,” which is written by T1. T2 commits, but later on, T1 fails.

Due to the failure of T1, we have to roll back T1 and T2.

  • T1 is rollback due to its failure but T2 has to rollback because it read the data written by T1.

But T2 can’t be rolled back because it is already committed. So this type of schedule is called irrecoverable schedule.

Note:  After the rollback of T1, the database should roll back to its original value of 1500, but it is not possible because the database updated with a value of 3000 by committing transaction T2. So it is a big problem in an irrecoverable schedule.

2. Recoverable Schedule

The schedule will be recoverable if Tj (Say T2) reads the updated value of Ti (Say T1) and Ti committed before Tj commit.

Recoverability of Schedule: Recoverable Schedule

Explanation

The above table of recoverable schedules shows two transactions. T1 reads and writes the value of data “A”. T2 also reads the same value of “A,” which is written by T1. But later on, T1 fails. Due to this, we have to roll back T1. Transaction (T2) should also be rollback because T2 has read the same value of data “A” written by T1.

As transaction T2 was not committed before T1, we can roll back transaction T2 as well. So it is recoverable with cascade rollback.

Note: As nothing is committed by T1 and T2, the database value can be rolled back to its original value of 1500 successfully. It is called a recoverable schedule.