Scheduling is vital in database management systems because it ensures that numerous transactions can access the database without interfering with one another. It also contributes to the database’s consistency and integrity.
Let’s break down what DBMS is and what the different types of schedules in DBMS are.
What is DBMS?
A DBMS or database management system is a type of software that enables users to create, alter, and manage data or information in a database.
Types of Schedules
We will discuss the following five types of schedules in DBMS:
- Serial schedules
- Conflict serializable schedules
- View serializable schedules
- Recoverable schedules
- Cascadeless schedule
Serial Schedule
A serial schedule is a kind of DBMS schedule in which we execute transactions sequentially with no overlap. In other words, one transaction must complete before another may begin. This is the simplest and most fundamental sort of schedule, and we refer to it as a non-concurrent schedule.
Advantages and Disadvantages
The simplicity of serial schedules is one of their key advantages. Transactions are completed one at a time, resulting in no need for complex concurrency control procedures. This makes serial scheduling simple to implement and comprehend.
The main disadvantage of serial schedules is their inefficiency. Transactions execute sequentially, meaning we waste time waiting for one transaction to finish before the next one can start. This leads to long execution times and reduced system throughput.
Real-World Examples
We use serial schedules mostly in situations where there is only one user accessing the database at a time. We do this where the workload is light and there is no need for concurrent access.
A good example is a simple database in a shop where the cashier is the only user accessing the database. In this case, a serial schedule is enough to ensure data consistency without causing any performance issues.
Serial schedules are inefficient and may not be suitable for situations with high transaction volumes or multiple users accessing the database concurrently.
Concurrent Schedule
A concurrent schedule is a type of DBMS schedule in which many transactions can run concurrently. This can boost system performance, but it also necessitates careful management to assure data consistency and avoid conflicts.
Multiple transactions can access the database at the same time in a concurrent schedule, resulting in greater system resource use and faster processing times. However, the chances of a conflict occurring are increased. This would occur if two transactions attempt to make changes to the same data at the same time.
Types of Concurrent Schedules
There are different types of concurrent schedules that we can use in a DBMS. Two such schedules are timestamp ordering and conflict serializability.
In timestamp ordering, transactions are ordered based on their timestamps. This helps to guarantee that each transaction has a consistent view of the data. In conflict serializability, transactions are ordered based on the conflicts that could occur between them helping to prevent conflicts from occurring.
Advantages and Disadvantages
Concurrent scheduling provides the primary advantage of improving system performance by allowing many transactions to run concurrently. They can also be more difficult to manage since they necessitate careful coordination and monitoring to guarantee data consistency and conflict avoidance.
Real-World Examples
Concurrent schedules are ideal for systems where multiple users need to access the same data at the same time. Good examples would be in a banking system or an online store. The ability to process multiple transactions at the same time gives these systems faster response times and provides better user experiences. However, it’s still important to carefully manage concurrency to avoid conflicts and maintain data consistency.
Recoverable Schedule
A recoverable schedule is one that ensures that the database remains in a consistent state even if the system crashes. A recoverable schedule is a type of schedule that ensures that transactions can be undone or rolled back in case of a system failure or crash.
This schedule saves any updates permanently to the database when a transaction commits. A recoverable schedule ensures that only the effects of committed transactions are retained in the event of a system failure while discarding the effects of uncommitted transactions.
Recoverable schedules use a logging technique to handle transactions and crashes. The system will log every transaction update before it gets executed. The system saves every update permanently to the database when a transaction commits. When there is a system crash, we use a log to determine which transactions need to be rolled back.
Advantages and Disadvantages
The advantages of recoverable schedules are that it ensures the database remains in a consistent state in case of a crash and it supports the undoing of a transaction’s effects in case of a system failure. The disadvantages of recoverable schedules, on the other hand, include the overhead involved in logging every transaction’s updates and the reduced concurrency due to the use of locks.
Real-World Examples
We use them in banking and financial systems where the integrity of the data is critical. They are also in use in e-commerce systems, where transactional data must be reliable and consistent. We use recoverable schedules in systems where data integrity is essential and the cost of data loss is high.
Cascadeless Schedule
A cascadeless schedule is a type of database schedule that solves the cascading rollback problem. It ensures that a transaction does not trigger the rollback of another transaction. The cascadeless schedule guarantees that only the transaction that initiates the rollback is rolled back and not any other transactions that may have already been committed.
Cascadeless schedules avoid cascading rollbacks by using a technique called “cascaded abort prevention.” This technique prevents a transaction from committing until it has completed transactions that require the data it has modified and has already committed. If a transaction cannot commit due to this constraint, it is rolled back.
Advantages and Disadvantages
One advantage of cascadeless schedules is that they prevent the cascading rollback problem. This saves time and prevents data inconsistencies. Cascadeless schedules can also lead to increased concurrency control overhead which impacts performance.
Real-World Examples
Cascadeless schedules are best for when we need a high level of concurrency is required. This can be in online transaction processing systems or in large-scale distributed systems. They are also useful in situations where data consistency is critical. Examples are in financial systems and healthcare applications.
Strict Schedule
A strict schedule is a form of DBMS schedule that guarantees that the output of one transaction does not conflict with the input of another. It provides consistency by requiring that each transaction adheres to a set of preset rules designed to prevent data from being updated by several transactions at the same time.
Transactions in a strict schedule must follow rigid ordering rules to ensure that they do not interfere with one another. Every single transaction must complete all its operations before the next transaction starts. This ensures that each transaction sees a consistent view of the data. If a transaction tries to read data that has been modified by another transaction, the strict schedule rolls back the transaction, thereby keeping the data consistent.
Advantages and Disadvantages
The advantages of strict schedules are that it ensures consistency in the database by preventing transactions from interfering with each other and it provides a clear and defined order of operations, making it easy to maintain the database. The disadvantages of strict schedules are that they can lead to a decrease in concurrency. The transactions have to wait for each other to complete. Also, it can lead to performance issues because the database may need to perform extra checks to ensure we use strict scheduling rules.
Real-World Examples
Strict schedules are ideal for situations when data consistency is critical. A good example is in a banking system where rigorous schedules are in use to guarantee that transactions do not interfere with one another and that account balances stay accurate. Strict schedules are best for situations where data like log files must be read in a consistent order.
©dizain/Shutterstock.com
DBMS That Use Different Schedules
Different Database Management Systems (DBMS) can use different scheduling techniques to manage their transactions. In this section, we will explore some popular DBMSs and the types of schedules they use.
Oracle
Oracle is a popular enterprise-level DBMS that offers multiple transaction isolation levels, including Serializable, Repeatable Read, Read Committed, and Read Uncommitted. Oracle uses Multi-Version Concurrency Control (MVCC) to implement these isolation levels.
MVCC allows transactions to read and write to the database without interfering with other transactions. Oracle also supports various scheduling techniques like Strict 2PL, Optimistic 2PL, and Timestamp Ordering.
MySQL
MySQL is an open-source DBMS that uses a locking-based mechanism to implement concurrency control. It supports transaction isolation levels like Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
MySQL uses two-phase locking to ensure that transactions do not interfere with each other. In addition, MySQL offers different scheduling techniques such as Timestamp-based, First-Commit, and MVCC.
PostgreSQL
PostgreSQL is an open-source relational DBMS that offers multiple transaction isolation levels, including Serializable, Repeatable Read, Read Committed, and Read Uncommitted.
PostgreSQL uses MVCC to implement these isolation levels. It also offers different scheduling techniques like Strict 2PL, Optimistic 2PL, and Serializable Snapshot Isolation.
SQL Server
SQL Server is an in-demand DBMS from Microsoft. It supports multiple transaction isolation levels, including Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
SQL Server uses locking-based mechanisms like Shared Locks and Exclusive Locks to ensure that transactions do not interfere with each other. It also offers different scheduling techniques such as Timestamp-based, Snapshot-based, and Serializable.
The image featured at the top of this post is ©Artem Samokhvalov/Shutterstock.com.
