We know that transactions are set of instructions and these instructions perform operations on database. When multiple transactions are running concurrently then there needs to be a sequence in which the operations are performed because at a time only one operation can be performed on the database. This sequence of operations is known as Schedule.
Lets take an example to understand what is a schedule in DBMS.
DBMS Schedule example
The following sequence of operations is a schedule. Here we have two transactions T1 & T2 which are running concurrently.
This schedule determines the exact order of operations that are going to be performed on database. In this example, all the instructions of transaction T1 are executed before the instructions of transaction T2, however this is not always necessary and we can have various types of schedules which we will discuss in this article.
T1 T2 ---- ---- R(X) W(X) R(Y) R(Y) R(X) W(Y)
Types of Schedules in DBMS
We have various types of schedules in DBMS. Lets discuss them one by one.
In Serial schedule, a transaction is executed completely before starting the execution of another transaction. In other words, you can say that in serial schedule, a transaction does not start execution until the currently running transaction finished execution. This type of execution of transaction is also known as non-interleaved execution. The example we have seen above is the serial schedule.
Lets take another example.
Serial Schedule example
Here R refers to the read operation and W refers to the write operation. In this example, the transaction T2 does not start execution until the transaction T1 is finished.
T1 T2 ---- ---- R(A) R(B) W(A) commit R(B) R(A) W(B) commit
In Strict schedule, if the write operation of a transaction precedes a conflicting operation (Read or Write operation) of another transaction then the commit or abort operation of such transaction should also precede the conflicting operation of other transaction.
Lets take an example.
Strict Schedule example
Lets say we have two transactions Ta and Tb. The write operation of transaction Ta precedes the read or write operation of transaction Tb, so the commit or abort operation of transaction Ta should also precede the read or write of Tb.
Ta Tb ----- ----- R(X) R(X) W(X) commit W(X) R(X) commit
Here the write operation W(X) of Ta precedes the conflicting operation (Read or Write operation) of Tb so the conflicting operation of Tb had to wait the commit operation of Ta.
In Cascadeless Schedule, if a transaction is going to perform read operation on a value, it has to wait until the transaction who is performing write on that value commits.
Cascadeless Schedule example
For example, lets say we have two transactions Ta and Tb. Tb is going to read the value X after the W(X) of Ta then Tb has to wait for the commit operation of transaction Ta before it reads the X.
Ta Tb ----- ----- R(X) W(X) W(X) commit R(X) W(X) commit
In Recoverable schedule, if a transaction is reading a value which has been updated by some other transaction then this transaction can commit only after the commit of other transaction which is updating value.
Recoverable Schedule example
Here Tb is performing read operation on X after the Ta has made changes in X using W(X) so Tb can only commit after the commit operation of Ta.
Ta Tb ----- ----- R(X) W(X) R(X) W(X) R(X) commit commit