Showing posts with label Transaction Management. Show all posts
Showing posts with label Transaction Management. Show all posts

Tuesday, 17 April 2018

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

*********




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 T1 and T2 are in a schedule S, and T2 consumes the data written by T1 (we say T2 is dependent on T1), then we must ensure that T1 commits before T2 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 Ti fails and other transactions have consumed the data written by Ti.

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 T1 fails and aborted then T2 which is dependent on T1 has to be rolled back and T3 which is dependent on T2 has also to be rolled back. This type of rollback is called as cascading rollback. Hence, this schedule is not a cascadeless schedule.

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


 
 









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

Monday, 16 April 2018

Check for conflict serializability solved example in dbms

Check for conflict serializability solved example in dbms

Question:
Consider a schedule S with two transactions T1 and T2 as follows;
S: R1(x);W2(x);R1(x);W1(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 T2 executes w(x) after T1 executed r(x). [These two instructions are conflict]. We insert an edge from T1 to T2 as follows;

Ins. 2 and 3: Transaction T1 executes a r(x) after T2 executed w(x). [These two are conflicting instructions.] We insert an edge from T2 to T1 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.

***********









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

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...