Recoverable Schedule In DBMS

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 in DBMS; 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.

Conditions for a Schedule to be Recoverable:

1. Commit Dependency:

A schedule is recoverable if, for every transaction T1 and T2, if T2 reads a data item written by T1, then T2 must commit only after T1 commits. In other words, T2 cannot commit before T1 commits, because T2 is dependent on T1’s result.

  • Example: If T1 writes to a data item X and T2 reads X, T2 should not commit unless T1 has already committed.

2. No cascading rollbacks:

In a recoverable schedule, if a transaction T2 depends on T1, and T1 fails, then T2 should also fail (or be rolled back). This prevents a situation where T2 commits while relying on uncommitted changes from T1, which could result in inconsistent database states if T1 fails later.

Why is Recoverability Important?

  • Consistency: Ensures that the database remains in a valid state even if a transaction fails. This is essential in multi-user environments to maintain data integrity.

  • Avoiding Cascading Rollbacks: Prevents situations where multiple transactions need to be rolled back due to one transaction failing. Cascading rollbacks can severely affect performance and make it difficult to maintain database consistency.

Conclusion

A recoverable schedule is essential for maintaining data consistency and preventing cascading rollbacks. It guarantees that a transaction can safely commit only if it is based on the results of other transactions that have already been committed. Ensuring that transactions in a DBMS follow the rules for recoverability helps in avoiding scenarios where uncommitted changes lead to data corruption and rollback chains that negatively affect database performance and integrity.