A schedule is the representation of execution sequence for all the instructions of the transactions.
Schedules are categorized in two types:
- Serial Schedules
- Concurrent Schedules
A schedule is said to be serial if and only if all the instructions of all the transactions get executed non-preemptively as an unit.
Each serial schedule consists of a sequence of instructions from various transactions, where the instructions belonging to one single transaction appear together in that schedule.
Thus, for a set of n transactions, there exist n! different valid serial schedules.
if there exist three transactions then all possible serial schedules are:
here n = 3(no of transactions)
3! = 3*2*1 = 6 serial schedules
Serial schedules gives guarantee for data consistency.
A schedule is said to be concurrent in case the instructions of the transactions get executed preemptively.
When the database system executes several transactions concurrently, the corresponding schedule no longer needs to be serial.
If two transactions are running concurrently, the operating system may execute one transaction for a little while, then perform a context switch, execute the second transaction for some time, and then switch back to the first transaction for some time, and so on. With multiple transactions, the CPU time is shared among all the transactions.
Several execution sequences are possible, since the various instructions from both
transactions may now be interleaved.
In general, it is not possible to predict exactly how many instructions of a transaction will be executed before the CPU switches to another transaction.
Thus, the number of possible schedules for a set of n transactions is much larger than n!.
Concurrent schedules might get affected with conflicting operations and hence does not give guaranty for data consistency.
A pair of operations is said to be conflicting with each other if and only if:
- They belong to different transactions
- Both the operations are accessing the same data item.
- At least one of these operations is write operation.
write-write, write-read, read-write.
Disadvantages of Serial Schedules:
- High average waiting time.
- Low response time and low throughput could be possible.
Advantage of Serial Schedules:
- It always gives guarantee for data consistency.
Disadvantages of Concurrent Schedules:
- Possible data inconsistency.
- some time too much context switching
Advantage of Concurrent Schedules:
- Reduce waiting time.
- improve response time and throughput.
Hence RDBMS has implemented Concurrency control mechanism to produce serializable schedules.
A schedule is said to be serializable if and only if the result of the given concurrent schedule is equivalent to one of the possible serial schedule.