Schedule in DBMS

Schedule

A sequence of statements that specify the sequential order in which the statements of concurrent transactions are executed.

The transaction will get committed when it executes the instructions successfully without any interruption. It fails when it aborts any instruction while it is executing.

When the multiple transactions are getting executed concurrently and the order of the operations must be set so that the operations will not get overlap each other, the schedule came into effect and the transactions are timed and executed accordingly.

Schedules in DBMS

• Serial schedule
• Non-serial schedule
• Serializability schedule

Serial schedule:

In this schedule, one transaction is executed completely without any interruption before starting another schedule. In this schedule, the first transaction is completed its whole life before the second transaction begins.

Example for the serial schedule:

T1	T2
R	R(o2)
R	R(o1)
W	W(o2)
Commit	Commit

Here the R stands for reading the operation and W stands for writing the operations. In the above example, the T2 starts executing the instructions after completing the execution of the T1 execution of instructions.

Non-serial schedule:

The non-serial schedule is further classified into three types.

• Strict schedule
• Cascadeless schedule
• Recoverable schedule

Strict schedule:

In a Strict schedule, if the write operation of a transaction precedes a conflicting operation of another transaction then the commit or abort operation should also precede the conflicting operation of another transaction.

Example of the strict schedule:

T1	T2
R(o1)
	R(o1)
W(o1)
commit	W(o1) R(o1)
	commit

here in the above example, there are two operations T1 and T2. The T1 performs its write operation when the read or write operation of the T2 completes. So that we can commit or abort the transaction of the T1 and preceding T2.

Cascade less schedule:

In this Cascade less Schedule, if a transaction is going to perform the read operation on one instruction, then it has to wait until the transaction that is performing on the write operation on that instruction gets committed.

Example:

T1	T2
R(o1)
W(o1)
commit	W(o1) R(o1) commit

The T2 executes the write operation when T1 completes the read and write operation. The T2 will get committed after the T1 commit.

Recoverable schedule:

In this recoverable schedule, if a transaction is reading an instruction that has been updated by another transaction, then this transaction will get committed then after the commit of another transaction that is updating the instruction.

Example of the recoverable schedule:

T1	T2
R(o1) W(o1) commit	R(o1) W(o1) R(o1) commit

In this example, we can see that two transactions are getting committed after executing all the instructions in both transactions.

Serializability schedule:

A schedule is said to be a type of serializable if it is equal to a serial schedule. The instructions in the serializability will execute simultaneously.

The serializability schedule is further divided into the following types.

• View serializable
• Conflict serializable

View serializability:

A schedule Is viewed serializable if it is viewed equivalent to the serial schedule.

The two schedules are said to be view equal if they satisfy the following conditions.

1. Starting value
   |T1 reads the starting value of A in S1 then it must also read the starting value of A in S2.
2. Modified read
   If T1 is reading A which is updated by T2 in S1 then in S2 also T1 should read A which is modified by T2.
3. Final write
   A final write operation should be the same in both the schedules in which the schedule S1 if a transaction T1 updates A then at last in S2 final write operations should be updated by T1.

Example:

Conflict serializability:

T1	T2
R(o1) W(o1) R(o2) W(o2)	R(o1)           W(o1)           R(o2)           W(o2)

In conflict serializability, it can be changed into a serial schedule by swapping the non-conflicting operations.

Operations of the conflict serializability:

The operations of both the transactions should belong to different transactions.
2. The operations of both the transactions should work on the same instruction.
3. There should be at least one of the operations should be a written operation from the both transactions.

Example of conflict serializable:

T1	T2
R(o1)   R(o2) W(o1)	R(o1)  R(o2)   W(o1)

After swapping the two transactions

T1	T2
R(o1)         R(o2) W(o1) commit	R(o1)  R(o2) W(o2)       commit