Important properties a schedule of transactions must have to maintain a database in a consistent state

What are the important properties a schedule of transactions must have to maintain a database in a consistent state?

Properties of a schedule of transactions

We need a schedule which

• is serializable 
• a concurrent schedule which produces same result as a serial schedule is called serializable schedule

• is recoverable 
• a schedule with the ability to recover from failures

• is cascadeless 
• a schedule that avoids/eliminates cascading rollback in case of aborting of one or more transactions.

• preserves the order of the operations in each of the individual 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 recoverable schedules page

important properties of schedules in dbms

why a schedule need to be serializable

cascadeless schedules

preserving the consistency of a database

Cascadeless schedule in database transaction management

Cascadeless schedule in database transaction management

Cascadeless schedule:

A schedule that eliminates cascading rollback of transactions when a failure occurs is called cascadeless schedule. If there are no dirty reads in a schedule, then that schedule will be a cascadeless schedule.

• Every cascadeless schedule is recoverable schedule.

How do we avoid cascading rollback?

If two transactions T₁ and T₂ are in a schedule S, and T₂ consumes the data written by T₁ (we say T₂ is dependent on T₁), then we must ensure that T₁ commits before T₂ commits. If this situation is ensured then we have avoided cascading rollback.

Discussion:

It is an important property that would save time and other resources in database transaction management.

At first place, we need the schedules to be recoverable schedules. Recoverability does not ensure cascadeless schedule. Even if a schedule is recoverable, we may need to rollback several transactions in case if a transaction T_i fails and other transactions have consumed the data written by T_i.

Example 1:

Is the following schedule is cascadeless or not?

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

This schedule is cascadeless because there is no dirty read. 

Example 2:

Is the following schedule is cascadeless or not?

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

This schedule is said to be a partial schedule as it does not consist of either commit or abort statements. In this schedule, if T₁ fails and aborted then T₂ which is dependent on T₁ has to be rolled back and T₃ which is dependent on T₂ has also to be rolled back. This type of rollback is called as cascading rollback. Hence, this schedule is not a cascadeless schedule.

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

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 recoverable schedules page

 

 

what is cascadeless schedule

define cascading rollback

explain with an example the cascadeless schedule

purpose of cascadeless schedule

Example of cascadeless schedule

why all recoverable schedules are not cascadeless schedules?

all cascadeless schedules are recoverable schedules

Check for conflict serializability solved example in dbms

Check for conflict serializability solved example in dbms

Question:

Consider a schedule S with two transactions T₁ and T₂ as follows;

S: R₁(x);W₂(x);R₁(x);W₁(y);commit1;commit2;

Is the schedule S conflict serializable?

Solution:

The given schedule S is as follows;

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

Conflict serializable schedule: A schedule is conflict serializable if it can be transformed into an equivalent serial schedule by swapping pairs of non-conflicting instructions. Two instructions conflict if they involve the same data item and at least one of them is a WRITE.

Let us construct a precedence graph;

Ins. 1 and 2: Transaction T₂ executes w(x) after T₁ executed r(x). [These two instructions are conflict]. We insert an edge from T₁ to T₂ as follows;

Ins. 2 and 3: Transaction T₁ executes a r(x) after T₂ executed w(x). [These two are conflicting instructions.] We insert an edge from T₂ to T₁ as follows;

Insertion of new edge forms a cycle. If there is a cycle in the precedence graph, then the schedule cannot be a conflict serializable schedule.

In other words, if you observe the schedule S carefully you can find that neither of the following can occur;

• Instruction 1 cannot be swapped with instruction 2 due to conflict (Read-Write conflict).

• Instruction 2 cannot be swapped with instruction 3 due to conflict (Write-Read conflict)

Hence, the schedule S cannot be serialized.

Schedule S is not conflict serializable schedule.

***********

Go to Define Serializability page

Go to Conflict Serializability explained with example page

Go to Solved exercises in DBMS page

Conflict serializability solved exercise

conflict serializable schedule example

how to find whether a schedule is conflict serializable or not?

how to check a schedule is serializable or not?

use precedence graph for checking serializability

serializability solved exercise

concurrency and serializability