Recoverability in DBMS

Prerequisite:

As discussed, a transaction may not execute completely due to hardware failure, system crash or software issues. In that case, we have to roll back the failed transaction. But some other transaction may also have used values produced by the failed transaction. So we have to roll back those transactions as well.

Recoverable Schedules:



  • Schedules in which transactions commit only after all transactions whose changes they read commit are called recoverable schedules. In other words, if some transaction Tj is reading value updated or written by some other transaction Ti, then the commit of Tj must occur after the commit of Ti.
    Example 1:

    S1: R1(x), W1(x), R2(x), R1(y), R2(y), 
             W2(x), W1(y), C1, C2; 

    Given schedule follows order of Ti->Tj => C1->C2. Transaction T1 is executed before T2 hence there is no chances of conflict occur. R1(x) appears before W1(x) and transaction T1 is committed before T2 i.e. completion of first transaction performed first update on data item x, hence given schedule is recoverable.

    Example 2: Consider the following schedule involving two transactions T1 and T2.

    T1 T2
    R(A)
    W(A)
    W(A)
    R(A)
    commit
    commit


    This is a recoverable schedule since T1 commits before T2, that makes the value read by T2 correct.

Irrecoverable Schedule:

  • The table below shows a schedule with two transactions, T1 reads and writes A and that value is read and written by T2. T2 commits. But later on, T1 fails. So we have to rollback T1. Since T2 has read the value written by T1, it should also be rollbacked. But we have already committed that. So this schedule is irrecoverable schedule. When Tj is reading the value updated by Ti and Tj is committed before committing of Ti, the schedule will be irrecoverable.
    Recoverabilityofschedules

Recoverable with Cascading Rollback:

  • The table below shows a schedule with two transactions, T1 reads and writes A and that value is read and written by T2. But later on, T1 fails. So we have to rollback T1. Since T2 has read the value written by T1, it should also be rollbacked. As it has not committed, we can rollback T2 as well. So it is recoverable with cascading rollback. Therefore, if Tj is reading value updated by Ti and commit of Tj is delayed till commit of Ti, the schedule is called recoverable with cascading rollback.
    Recoverabilityofschedules2

Cascadeless Recoverable Rollback:

  • The table below shows a schedule with two transactions, T1 reads and writes A and commits and that value is read by T2. But if T1 fails before commit, no other transaction has read its value, so there is no need to rollback other transaction. So this is a Cascadeless recoverable schedule. So, if Tj reads value updated by Ti only after Ti is committed, the schedule will be cascadeless recoverable.
    Recoverability3

Question: Which of the following scenarios may lead to an irrecoverable error in a database system?

  1. A transaction writes a data item after it is read by an uncommitted transaction.
  2. A transaction reads a data item after it is read by an uncommitted transaction.
  3. A transaction reads a data item after it is written by a committed transaction.
  4. A transaction reads a data item after it is written by an uncommitted transaction.

Answer: See the example discussed in Table 1, a transaction is reading a data item after it is written by an uncommitted transaction, the schedule will be irrecoverable.
Related Post:
Conflict Serializability

This article is contributed by Sonal Tuteja. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above



My Personal Notes arrow_drop_up