Why do we need schedules in DBMS?

When many transactions are running at the same time, the operations must be executed in a specific order because the database can only handle one operation at a time. The schedule is the name for this set of procedures.

What are concurrent schedules in DBMS?

When a database system processes many transactions at the same time, the related schedule is no longer required to be serial. Interleaving transactions is now possible. When the instructions of the transactions are executed ahead of time, the schedule is said to be concurrent.

When is a schedule serializable?

A serializable schedule is one that is guaranteed to have the same effect on any consistent database instance as a complete serial schedule over S.

Introduction to Scheduling in DBMS

Introduction

The term “schedule” refers to a sequence of operations from one transaction to the next. The process of queuing up transactions and executing them one by one is known as scheduling. When there are numerous transactions operating at the same time, and the order of operations needs to be determined so that the operations do not overlap, scheduling is used, and the transactions are timed properly. We’ll go through many types of scheduling in this blog.

Types of Schedules

In DBMS, schedules may be classified as:-

Serial Schedule

The serial schedule is a sort of schedule in which one transaction is completed before proceeding to the next. When the first transaction completes its cycle, the subsequent transaction is processed in a serial manner.

Serial schedules are always Strict, Consistent, Recoverable, and Cascade-less.

Example: Assume two transactions T1 and T2

All the operations of transaction T1 on data item A and then B executes, and then in transaction T2, all the operations on data items A and B execute. The R stands for a read operation, and W stands for a write operation.

Non-Serial Schedule

This is a kind of schedule in which many transactions' operations can be interleaving. This could worsen the concurrency problem. The transactions are carried out in a non-serial fashion, ensuring that the end result is accurate and consistent with the serial schedule. Besides a serial schedule, where one transaction has to wait for another to finish all of its operations, a non-serial schedule allows the next transaction to continue without waiting for the last one to finish. The concurrent transaction does not benefit from this type of schedule.

Serializable

This is used to guarantee the database's consistency. It is mostly used in Non-Serial scheduling to ensure that the schedule will not result in any inconsistencies. Only when the non-serial schedule is equivalent to the serial schedules for an n number of transactions is it considered to be serializable. Multiple transactions can run at the same time because concurrency is allowed in this instance.

There are two kinds of serializable schedules:

Conflict Serializable

If a schedule can be turned into a serial schedule by switching non-conflicting operations, it is called conflict serializable.

If all of the following conditions are met, two operations are said to be incompatible:

They both work with the same data item.
One of them must be a write operation.

View Serializable

If a schedule is viewed equal to a serial schedule, it is called view serializable (no overlapping transactions). A conflict schedule is a view serializable, but it is not conflict serializable if the serializability includes blind writes.

Non-Serializable

In the case of non-serial schedules,

Multiple transactions run at the same time.
All of the transactions' operations are interleaved or jumbled together.

Assume two transactions T1 and T2

The above transaction is said to be non-serial, which results in inconsistency or conflicts in the data.

Non-Serializable schedules are further of the following types:

Recoverable Schedule

If a transaction is reading a value that has been updated by another transaction in a Recoverable schedule, this transaction can commit only after the other transaction that is updating the value has been committed.

Example: Assume two transactions T1 and T2.

Since T1 commits before T2, this is a recoverable schedule that makes the value read by T2 correct.

Example:

T1	T2
R(A)
W(A)
	W(A)
	R(A)
COMMIT
	COMMIT

Non-Recoverable Schedule

A non-recoverable Schedule is one in which a transaction performs a dirty read operation from an uncommitted transaction and commits before the transaction from which it has read the value.

Example: Assume two transactions T1 and T2.

T2 reads the value of A written by T1. Later T1 is aborted; therefore, the value read by T2 is wrong, but as T2 has committed, this schedule is non-recoverable.

T1	T2
R(A)
W(A)
	W(A)
	R(A)
	COMMIT
ABORT

You can also read about the Log based recovery.

Must Recommended Topic, Schema in DBMS

FAQs

Why do we need schedules in DBMS?
When many transactions are running at the same time, the operations must be executed in a specific order because the database can only handle one operation at a time. The schedule is the name for this set of procedures.
What are concurrent schedules in DBMS?
When a database system processes many transactions at the same time, the related schedule is no longer required to be serial. Interleaving transactions is now possible. When the instructions of the transactions are executed ahead of time, the schedule is said to be concurrent.
When is a schedule serializable?
A serializable schedule is one that is guaranteed to have the same effect on any consistent database instance as a complete serial schedule over S.

Key Takeaways

In this blog, we learned that The term "schedule" refers to a sequence of operations from one transaction to the next. Scheduling is the process of queuing up transactions and executing them one by one. There are many types of scheduling in DBMS. The term serializable is used to describe a schedule that can be turned into a serial schedule by switching non-conflicting operations. Non Serializable schedule is in which many transactions' operations can be interleaving.