What are the different types of schedules in DBMS?

A schedule is defined as an execution sequence of transactions. A schedule maintains the order of the operation in each individual transaction. A schedule is the arrangement of transaction operations. A schedule may contain a set of transactions. We already know that a transaction is a set of operations. To run transactions concurrently, we arrange or schedule their operations in an interleaved fashion.

Schedules are divided into 2 categories, which are as follows −

Serial Schedule
Non-serial Schedule

The categories of schedules are given below in a form of chart −

Serial schedule

Transactions present in this schedule are executed serially, after the instruction of Ti completes, the instructions of Tj will be executed, where j=i+1.

Serial schedule guarantee consistency −

For 2 transactions, total number of serial schedules possible = 2.
For 3 transactions, total number of serial schedules possible = 6.

 2 Transaction      3 Transaction
   T1->T2           T1->T2->T3
   T2->T1           T1->T3->T2
                    T2->T1->T3
                    T2->T3->T1
                    T3->T1->T2
                    T3->T2->T1

If n = number of transactions, then number of serial schedules possible = n!.

A serial schedule always gives the correct result. But, we follow a concurrent schedule for time efficiency. So we must ensure serializability of a concurrent schedule.

Non-serial Schedules

When a transaction is overlapped between the transactions T1 and T2.

Example

T1	T2
READ1(A)
WRITE1(A)
	READ2(B)
	WRITE2(B)
READ1(B)
WRITE1(B)
READ1(B)

Types of Non serial schedules

Non serial schedules are divided into serializability and the Non serial schedules. Let us first discuss serializability.

There are two types of serializability which are as follows −

View serializability

A schedule is view-serializability if it is view equivalent to a serial schedule.

The rules it follows are given below −

T1 is reading the initial value of A, and then T2 also reads the initial value of A.
T1 is the reading value written by T2, and then T2 also reads the value written by T1.
T1 is writing the final value, and then T2 also has the write operation as the final value.

Conflict serializability

It orders any conflicting operations in the same way as some serial execution. A pair of operations is said to conflict if they operate on the same data item and one of them is a write operation.

That means,

Readi(x) readj(x) - non conflict read-read operation
Readi(x) writej(x) - conflict read-write operation.
Writei(x) readj(x) - conflict write-read operation.
Writei(x) writej(x) - conflict write-write operation.

The Non serial schedules are divided into recoverable and non-recoverable schedules. Let us first understand the recoverable schedule.

Recoverable schedule

Consider the following example −

T1	T2
R(X)
W(X)
	W(X)
	R(X)
READ1(B)
commit
	commit

Here, transaction T2 is reading the value written by transaction T1 and the commit of T2 occurs after the commit of T1. Hence, it is a recoverable schedule.

Again the recoverable schedule is divided into cascade less and strict schedule −

Cascadeless schedule

Given below is an example for the cascadeless schedule −

T1	T2
R(X)
W(X)
	W(X)
commit
	R(X)
	commit

Here, the updated value of X is read by transaction T2 only after the commit of transaction T1. Hence, the schedule is Cascadeless schedule.

Strict schedule

Given below is an example of strict schedule −

T1	T2
R(X)
	R(X)
W(X)
commit
	W(X)
	R(X)
	commit

Here, transaction T2 reads and writes the updated or written value of transaction T1 only after the transaction T1 commits. Hence, the schedule is strict schedule.

Now let us see about the Non-recoverable schedule.

Non-Recoverable Schedule

A schedule that is not recoverable is non-recoverable. If the commit operation of Ti doesn't occur before the commit operation of Tj, it is non-recoverable.

Consider an example for the non-recoverable schedule as given below −

Schedule 1