DBMS - Scheduling in Transactions

Transactions ensure that data remains accurate and consistent even when multiple processes run simultaneously. There may be more than one transactions in action. But when several transactions are executed together, how do we organize them without creating data conflicts? Here the concept of "scheduling" comes. Read this chapter to learn the basics of scheduling, its types, and examples for a better understanding.

What is Scheduling?

A schedule is an ordered sequence of operations from different transactions arranged based on their execution order or timeline. A schedule defines how the database processes multiple transactions. It also keep the consideration that every instruction is performed correctly while maintaining data consistency.

Example − Consider there are two transactions, T1 and T2. They are working on shared data items A and B −

• T1 − Reads and writes A, then reads and writes B.
• T2 − Works on B first, then A.

The way these instructions are ordered forms a schedule.

Basic Rules of Scheduling

To keep the database consistent, scheduling must follow a set of rules as given in the following −

• All Instructions Must Be Included − Every instruction from every transaction must be part of the schedule. We cannot ignore them. Skipping instructions could cause data loss or corruption.
• Maintain Transaction Order − The order of operations within each transaction must remain unchanged. For example, if T1 reads A before writing it, then the order cannot be swapped.
• Context Switching Allowed − We can switch back and forth between transactions. But this is only done when the instructions of each transaction follow the correct sequence.

Types of Schedules

Schedules fall into two main types −

• Serial Schedules
• Non-Serial Schedules

Let us look at these in detail with examples.

Serial Schedules

A serial schedule means the transactions runs one after another. They re not overlapping their operations. This method is the safest because transactions do not interfere with each other.

Example: Serial Schedule of T1 and T2

Let us arrange transactions T1 and T2 into two possible serial schedules.

In this schedule, T2 runs fully before T1 starts. Since only one transaction runs at a time, there is no risk of data inconsistency.

• Advantage − No risk of conflicts or data errors.
• Disadvantage − No concurrency. Only one transaction runs at a time, slowing down the system.

Non-Serial Schedules

Non-serial schedules allow overlapping transactions, enabling concurrency but increasing the chance of conflicts.

Example: Non-Serial Schedule of T1 and T2

Here is an example where instructions from T1 and T2 are interleaved −

In this schedule, instructions from T1 and T2 are mixed. This is allowing both transactions to run concurrently. However, incorrect interleaving could lead to data inconsistencies if done carelessly.

Key Insight

• Advantage − Allows for concurrency, improving system performance.
• Disadvantage − Increases the risk of data conflicts due to overlapping operations.

Importance of Non-Serial Schedules

Non-serial schedules are used because they −

• Boost Resource Efficiency − More transactions can run simultaneously. This is reducing idle time.
• Lower Waiting Time − Transactions spend less time waiting for execution.

However, these benefits come at a cost. If the scheduling is not done correctly then the database could become inconsistent. That is where conflict serializability helps to get even non-serial schedules maintain data accuracy.

Calculating Possible Schedules

How many different serial schedules can exist for n transactions. To find that we can use formula as n! (n factorial) because each transaction can occupy any position in the schedule.

Example − Suppose there are three transactions T1, T2, and T3. The number of possible serial schedules would be −

$3! = 3 \times 2 \times 1 = 6$

The possible schedules would be −

• T1 → T2 → T3
• T1 → T3 → T2
• T2 → T1 → T3
• T2 → T3 → T1
• T3 → T1 → T2
• T3 → T2 → T1

For non-serial schedules, the number depends on the total instructions. Also depends on how they are interleaved while respecting each transaction’s internal order.

Advanced Calculation for Non-Serial Schedules

If transactions contain different numbers of instructions, the calculation becomes more complex. Suppose T1 has $n_1$ instructions, T2 has $n_2$, and Tn has $n_n$. The total instructions would be −

Total Instructions = $n_1 + n_2 + ... + n_n$

To calculate possible non-serial schedules while keeping individual transaction orders intact, use the formula −

Total Schedules = $\frac{(n_1 + n_2 + \cdots + n_n )!}{(n_1 ! \times n_2 ! \times \cdots \times n_n !)}$

Conclusion

In this chapter, we explained in detail the concept of scheduling in transactions. We discussed what a schedule is and why it is essential for database management. We also explored two primary types of schedules: serial and non-serial schedules. Serial schedules guarantee data consistency but do not support concurrency, which makes them slow in real-world scenarios. Non-serial schedules, while offering better performance, come with the risk of data conflicts.

We also learned how to calculate the number of possible schedules and why conflict serializability is useful in handling non-serial schedules.