Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Calculation of Serial and Non-Serial Schedules in DBMS
Introduction
In a database management system (DBMS), the scheduler is responsible for managing the execution of concurrent transactions. The scheduler uses a schedule, which is a sequence of actions that the DBMS must execute, to ensure that the system remains in a consistent state and that concurrent transactions do not interfere with each other. There are two types of schedules: serial schedules and non-serial schedules. In this article, we will discuss the calculation of both types of schedules in a DBMS.
Serial Schedules
A serial schedule is a schedule in which all transactions are executed one after another, without any concurrent execution. In other words, a serial schedule is a schedule in which transactions are executed in a single-threaded environment. The calculation of a serial schedule is straightforward, as there is no need to consider the execution order of transactions.
One of the advantages of using serial schedules is that they are easy to understand and reason about. Since there is no concurrent execution, it is easy to see how the system state changes as transactions are executed. However, serial schedules can lead to poor performance if there are many transactions that could be executed concurrently.
Example
Here is an example of a serial schedule in SQL −
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE account_number = 'A123'; UPDATE accounts SET balance = balance + 100 WHERE account_number = 'B456'; COMMIT;
Here we are doing 2 updates, one is reducing the balance of account 'A123' by 100 and other is increasing the balance of account 'B456' by 100 in one transaction, this is called as debit-credit transaction where balance of one account is credited and other account is debited.
Non-Serial Schedules
A non-serial schedule is a schedule in which transactions are executed concurrently. In other words, a non-serial schedule is a schedule in which transactions are executed in a multi-threaded environment. The calculation of a non-serial schedule is more complex than that of a serial schedule, as we must consider the execution order of transactions.
One of the advantages of using non-serial schedules is that they can lead to better performance, as transactions can be executed concurrently. However, non-serial schedules can be harder to understand and reason about, as it can be difficult to see how the system state changes as transactions are executed concurrently.
It is worth mentioning that when calculating non-serial schedule, it is important to ensure that the schedule is serializable, which means that it is equivalent to a serial schedule. A schedule is considered serializable if it preserves the isolation of transactions, that is, each transaction is executed as if it were the only transaction in the system.
Example
Here is an example of a non-serial schedule in SQL −
BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_number = 'A123'; COMMIT TRANSACTION; BEGIN TRANSACTION; UPDATE accounts SET balance = balance + 100 WHERE account_number = 'B456'; COMMIT TRANSACTION;
Here we are doing 2 updates in two different transactions, one is reducing the balance of account 'A123' by 100 and other is increasing the balance of account 'B456' by 100.
Calculation of Serial and Non-Serial Schedules in DBMS
In a DBMS, the scheduler is responsible for managing the execution of concurrent transactions. The scheduler uses schedules to ensure that the system remains in a consistent state and that concurrent transactions do not interfere with each other.
The calculation of a serial schedule is straightforward, as there is no need to consider the execution order of transactions. However, serial schedules can lead to poor performance if there are many transactions that could be executed concurrently. On the other hand, non-serial schedules can lead to better performance, as transactions can be executed concurrently. However, it can be difficult to understand and reason about non-serial schedules as it can be difficult to see how the system state changes as transactions are executed concurrently.
It is worth noting that when calculating non-serial schedules, it is important to ensure that the schedule is serializable, which means that it is equivalent to a serial schedule. A schedule is considered serializable if it preserves the isolation of transactions, that is, each transaction is executed as if it were the only transaction in the system. This can be achieved through various concurrency control techniques such as locking, timestamp ordering, and multi-version concurrency control.
Points to be Noted
Deadlocks − Deadlocks are a common problem that can occur when transactions are executed concurrently. A deadlock occurs when two or more transactions are waiting for each other to release a resource they need. In order to avoid deadlocks, a DBMS can use a variety of techniques such as lock ordering and timeout-based protocols.
Isolation levels − Isolation levels define the degree to which the DBMS isolates transactions from each other. Different isolation levels, such as READ COMMITTED, REPEATABLE READ, and SERIALIZABLE, can have a significant impact on the calculation of schedules. A higher isolation level can increase the chances of conflicts between transactions, but it also increases the chances of preserving the consistency of data.
Two-phase locking − Two-phase locking is a concurrency control technique that is used to ensure that a schedule is serializable. Two-phase locking requires transactions to acquire and release locks on data items in two phases: an expanding phase and a shrinking phase. In the expanding phase, a transaction acquires locks on data items it needs to access. In the shrinking phase, the transaction releases locks on data items it no longer needs.
Rollbacks and recovery − Rollbacks and recovery are important mechanisms that are used to handle errors and failures in a DBMS. When a transaction fails, it must be rolled back so that it does not leave the system in an inconsistent state. Recovery mechanisms, such as the write-ahead logging and the deferred update technique, are used to ensure that transactions can be rolled back and the system can be restored to a consistent state.
Conclusion
In conclusion, understanding the calculation of both serial and non-serial schedules in a DBMS is crucial for designing efficient and reliable database systems. It is important to choose the appropriate schedule based on the specific requirements of the system and to ensure that non-serial schedules are serializable to preserve the isolation of transactions.
1K+ Views
