What do you mean by schedule in DBMS?

A transaction must satisfy the ACID properties of DBMS, namely

• Atomicity − Execute all or none transaction.

• Consistency − The DB should be consistent before and after transaction execution.

• Isolation − Transaction must be executed in isolated form.

• Durability − Roll backing should always be possible in Case of any failure.

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 two categories, namely −

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 as follows −

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.

Concurrent Schedule

Multiple transactions can be executed in an interleaved manner, but obeying the above ACID properties.

Advantages

The advantages of concurrent schedule are as follows −

• Increases throughput which is nothing but the number of transactions completed per unit time).

• Reduces waiting time.

Example

T1= 90sec
T2= 500sec
T3= 5sec.

If we execute serially by T1->T2->T3 then transaction T3 waits for 590 sec, so we go for non-serial or concurrent transactions to reduce waiting time.

i.e. T3 -> T1 -> T2.

The disadvantage of the concurrent transaction is that the execution of a concurrent transaction may result in inconsistency.