Why do we need a recoverable schedule

Why do we need a recoverable schedule?

Why do we need a recoverable schedule?

Unrecoverable schedules lead to inconsistent database state. Hence, we need only recoverable schedules to safeguard consistency in concurrent execution of transactions.

T1	T2
R(x) W(x) R(y) W(y)	R(x) W(x)

To explain this, let us use the example schedule given above. Let us assume that the transaction T₂ commits before T₁ commits. Also assume that, the transaction T₁ has failed due to some reasons. In that case, T₁ has to be aborted. If T₁ has to be aborted then the transactions that have consumed data that was written by T₁ must also be aborted. In our example, we need T₂ must also be aborted (cascading abort). As T₂ has already committed, aborting T₂ is not possible. Now, this schedule is unrecoverable. This leads to inconsistent database state. Hence we need recoverable schedules.

**********

Go to What is recoverable schedule page

Go to Transaction management in DBMS page

Go to Important keywords in DBMS page

Go to Why do we need recoverable schedules page

Why recoverable schedules?

what is the purpose of recoverable schedules?

dirty reads in concurrent schedules

what is unrecoverable schedule

Recoverable schedules in database transaction

Recoverable schedules in database transaction

Recoverable schedules

A schedule is recoverable if every transaction in that schedule commits only after all the transactions whose changes they read have committed.

A schedule where, for each pair of transactions T_i and T_j, if T_j reads a data item previously written by T_i, then the commit operation of T_i precedes the commit operation of T_j.

For example, assume a schedule S with two transactions T₁, and T₂. If T₂ consumes a data item written by T₁ then T₁ must commit before T₂. If this happens then the schedule S is said to be recoverable schedule.

If a schedule should be recoverable, then there shouldn’t be any dirty reads [reading of uncommitted data].

Example:

Is the following transaction is recoverable or not?

Instruction	T1	T2
1 2 3 4 5 6	R(x) W(y) Commit	R(x) W(y) Commit

This schedule is recoverable because there are no dirty reads. Here, T₁ commits before T₂ but T₂ is not consuming any data that is written by T₁. Hence, this schedule is recoverable. Also, the order of commit is not an issue as neither T₁ nor T₂ has consumed data written by the other transactions. 

***********

Go to Transaction management in DBMS page

Go to Important keywords in DBMS page

Go to Why do we need recoverable schedules page 
Go to Define cascadeless schedules page

Recoverable schedules in concurrent schedules

what is recoverable schedule

define recoverable schedules

recoverable schedules example

how to identify a recoverable schedule

check for recoverability in concurrent schedules

What is the result of interleaved transactions in database transactions

Result of interleaved transactions in database transactions, Lost Update problem

Question:

Consider the given schedule S₁ with transactions T₁ and T₂; if the value of X at the beginning of the transactions is 100, what will be the value of X at the end of the transactions? Also, find the problem with the given schedule.

Transaction T1	Transaction T2
READ X X:=X - 20 WRITE X	READ X X:=X - 30 WRITE X

Solution:

The value of X at the end of the transactions is 70. This value is wrong. From the given schedule, it is very clear that both transactions are reading the initial value of X as 100, both calculates the new value to be written, and both writes the new value of X. here, T₁ finishes transaction first and writes it’s new X value 80. Then, T₂ overwrites the value written by T₁. Hence, we will see only one result which is written later.

This problem is called Lost Update problem.

Lost update problem – it happens when two transactions access the same resource in an interleaved fashion (i.e., few instructions from T₁ and few from T₂ and so on) and one of them overwrites the update made by the first.

************

Go to Solved Exercises in DBMS page

Go to Transaction Management in Database Management System page

Go to Lost Update problem in Database Transaction page

 

result produced by two concurrent transactions
what is the result of two interleaved transactions
transaction exercises in DBMS
database transaction and its execution
what is lost update problem in transactions
how to handle lost update problem
database solved exercises
write-write conflict in database transactions