Prerequisite – Types of Schedules in DBMS
Serializable Schedule :
A transaction schedule is serializable if its outcome is equal to the outcome of its transactions executed serially i.e. sequentially without overlapping in time. A serializable schedule always leaves the database in consistent state. A serial schedule is always a serializable schedule because a new transaction only starts when the older one has finished execution.
Let us consider the following schedule and see if it is serializable.
Now, let us figure out if the above schedule is serializable.
- Swapping R(X) of T1 and R(Y) of T2.
- Swapping R(X) of T1 and W(Y) of T2.
Thus, after changing the conflicting operations in the initial schedule we get a serial schedule. Hence, this schedule is serializable.
Types of Serializable Schedules :
- Result Equivanent Schedule
- Conflict Equivalent Schedule or Conflict Serializability
- View Equivanent Schedule or View Serializability
Difference between Conflict and View Serializability :
|S.No.||Conflict Serializability||View Serializability|
|1.||Two schedules are said to be conflict equivalent if all the conflicting operations in both the schedule get executed in the same order. If a schedule is a conflict equivalent to its serial schedule then it is called Conflict Serializable Schedule.||Two schedules are said to be view equivalent if the order of initial read, final write and update operations is the same in both the schedules. If a schedule is view equivalent to its serial schedule then it is called View Serializable Schedule.|
|2.||If a schedule is view serializable then it may or may not be conflict serializable.||If a schedule is conflict serializable then it is also view serializable schedule.|
|3.||Conflict equivalence can be easily achieved by reordering the operations of two transactions therefore, Conflict Serializability is easy to achieve.||Viewequivalence is rather difficult to achieve as both transactions should perform similar actions in a similar manner. Thus, View Serializability is difficult to achieve.|
|4.||For a transaction T1 writing a value A that no one else reads but later some other transactions say T2 write its own value of A, W(A) cannot be placed under positions where it is never read.||If a transaction T1 writes a value A that no other transaction reads (because later some other transactions say T2 writes its own value of A) W(A) can be placed in positions of the schedule where it is never read.|
Example for Conflict Serializability –
Let us consider the following transaction schedule and test it for Conflict Serializabilit
Now, we will list all the conflicting operations. Further, we will determine whether the schedule is conflict serializable using Precedence Graph.
Two operations are said to be conflicting if the belong to different transaction, operate on same data and at least one of them is a write operation.
- R3(X) and W2(X) [ T3 -> T2 ]
- W1(Y) and R3(Y) [ T1 -> T3 ]
- W1(Y) and W2(Y) [ T1 -> T2 ]
- R3(Y) and W2(Y) [ T3 -> T2 ]
Constructing the precedence graph, we see there are no cycles in the graph. Therefore, the schedule is Conflict Serializable.
The serializable schedule is,
T1 -> T3 -> T2
Example for View Serializability –
Let us consider the following transaction schedule and test it for View Serializability.
As we know that if a schedule is Conflict Serializable, then it is View Serializable also. So first let us check for Conflict Serializability.
The conflicting operations for this schedule are –
- R1(A) and W2(A) [ T1 -> T2 ]
- R1(A) and W2(A) [ T1 -> T3 ]
- W2(A) and R3(A) [ T2 -> T3 ]
- W2(A) and W1(A) [ T2 -> T1 ]
- W2(A) and W3(A) [ T2 -> T3 ]
- R3(A) and W1(A) [ T3 -> T1 ]
- W3(A) and W1(A) [ T1 -> T3 ]
Constructing the precedence graph for conflicting operations in the schedule.
As we can see that there is a cycle in the precedence graph, it means that the given schedule is not Conflict Serializable. Now, on checking for blind write we get that there exists a blind write W2(A) in the given schedule. Thus, the schedule may or may not be View Serializable.
In order to check for View Serializability, we will draw aDependency Graph of the schedule. From the given schedule we gather the following points :
- T1 reads A before T2 updates A thus, T1 must execute before T2.
- T3 does the final update on A thus, it must execute in the end.
Constructing the dependency graph.
As there exists no cycle in the graph, we can say that the given schedule is View Serializable.
The serializable schedule is T1 -> T2 -> T3.
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.