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.
- 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.
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.
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.
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.
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.
Question: Which of the following scenarios may lead to an irrecoverable error in a database system?
- A transaction writes a data item after it is read by an uncommitted transaction.
- A transaction reads a data item after it is read by an uncommitted transaction.
- A transaction reads a data item after it is written by a committed transaction.
- 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.
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
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Types of Schedules based Recoverability in DBMS
- Lossless Decomposition in DBMS
- Introduction of Relational Algebra in DBMS
- Need for DBMS
- Commonly asked DBMS interview questions | Set 1
- Normal Forms in DBMS
- Relational Model in DBMS
- Commonly asked DBMS interview questions | Set 2
- Last Minute Notes - DBMS
- ACID Properties in DBMS
- DBMS Architecture 2-Level, 3-Level
- Introduction of DBMS (Database Management System) | Set 1
- Introduction of 3-Tier Architecture in DBMS | Set 2
- Introduction of Relational Model and Codd Rules in DBMS
- Precedence Graph For Testing Conflict Serializability in DBMS
- Canonical Cover of Functional Dependencies in DBMS
- Thomas Write Rule in DBMS
- Armstrong's Axioms in Functional Dependency in DBMS
- Database Recovery Techniques in DBMS
- Deadlock in DBMS