Do you think IIT Guwahati certified course can help you in your career?
Introduction
Recoverability refers to the ability of a system, particularly a database management system (DBMS), to restore data to its original consistent state after a failure or interruption.
In the context of DBMS, recoverability involves mechanisms such as transaction logging, checkpointing, and transaction rollback to ensure that in the event of a system crash or failure, the database can be restored to a consistent state without losing any committed transactions. It ensures that data remains durable and consistent despite unforeseen events, maintaining the integrity and reliability of the database system.
Schedule
A schedule is a series of operations from one or more Transactions. The following charts explain the basic overview of types of schedules in DBMS(DataBase Management System).
Recoverable Schedule
A schedule is called a recoverable schedule when
A transaction does a dirty read operation from a transaction that is not committed.
And also, its committed operations become delayed till the uncommitted transaction is either committed or rollback.
Recoverable Schedule with cascading rollback
The schedule that is recoverable by cascading rollback is known as a Recoverable schedule.In general, if a transaction T1 fails, then all the other transactions depending upon T1 must be rolled back, this type of rollback in which if we rollback a transaction we also rollback all dependent transactions on it is called cascading rollback.
Example
The following table shows a schedule having two transactions T1 and T2.
Transaction T1
T1’s buffer space
Transaction T2
T2’s buffer space
Database
A=2000
Read(A)
A=2000
A=2000
A = A - 500;
A=1500
A=2000
Write(A)
A=1500
A=1500
Read(A)
A=1500
A=1500
A = A + 800;
A=2300
A=1500
Write(A)
A=2300
A=2300
Failure Point
Commit
Commit
Transaction T1 reads and writes the value of A, which is later used by transaction T2 for reading and writing. Due to the failure of execution of the transaction T1, it needs to be rolled back. Now, T2 should also be rolled back because it reads the value written by transaction T1 since T2 is not committed before Transaction T1, so we can rollback translation T2. Thus, it is recoverable with cascade rollback.
In short, we can say that a schedule will be recoverable with cascading rollback if transaction Tj reads the updated value of Transaction Ti and commit of transaction Tj is delayed till commit of Ti.
Hope this definition will help you understand and remember it clearly😄.
Cascade less Recoverable Schedule
As we have learned about the recoverable schedules. A schedule in which a transaction is not allowed to read the data until the last transaction in the queue is committed(or aborted) is called Cascade less Recoverable schedule.
Example
The following table shows a schedule having two transactions T1 and T2.
Transaction T1
T1’s buffer space
Transaction T2
T2’s buffer space
Database
A=2000
Read(A)
A=2000
A=2000
A = A - 500;
A=1500
A=2000
Write(A)
A=1500
A=1500
Commit
Read(A)
A=1500
A=1500
A = A + 800;
A=2300
A=1500
Write(A)
A=2300
A=2300
Commit
In the above table, transaction T1 reads and writes A and commits, then that value is read and written by transaction T2. Thus, it is a cascade less recoverable schedule.
A schedule is called an irrecoverable schedule when
A transaction does a dirty read operation from a transaction that is not committed.
And commits before the transaction from where it has read the value.
To know further about dirty read operation, you may refer to this blog.
Example
The following table shows a schedule having two transactions T1 and T2.
Transaction T1
T1’s buffer space
Transaction T2
T2’s buffer space
Database
A=2000
Read(A)
A=2000
A=2000
A = A - 500;
A=1500
A=2000
Write(A)
A=1500
A=1500
Read(A)
A=1500
A=1500
A = A + 800;
A=2300
A=1500
Write(A)
A=2300
A=2300
Commit
Failure Point
Commit
Transaction T1 reads and writes the value of A, which is later used by transaction T2 for reading and writing. Due to the failure of execution of the transaction T1, it needs to be rolled back. Now, T2 should also be rolled back because it reads the value written by transaction T1, but Transaction T2 cannot be rolled back because it already committed the transaction(as shown in the above table). This type of schedule is known as the Irrecoverable schedule.
In short, we can say that a schedule is non-recoverable if transaction Tj reads the updated value of Transaction Ti and transaction Tj committed before Ti.
I hope this definition will help you understand and remember it clearly😄.
The capabilities of recoverability in a DBMS are designed to ensure the integrity and consistency of data in the face of system failures or interruptions. These capabilities include:
Transaction Logging: DBMS maintains logs of all transactions performed on the database. These logs record the actions taken by transactions, including updates, inserts, and deletes. In the event of a system failure, the DBMS can use these logs to restore the database to a consistent state by replaying the transactions that were committed before the failure occurred.
Checkpointing: Checkpointing involves periodically writing the current state of the database to stable storage. This checkpoint serves as a reference point that the DBMS can use to recover from failures more efficiently. By recording the checkpoint, the system reduces the amount of time needed to roll back or roll forward transactions during recovery.
Transaction Rollback and Rollforward: In case of a system failure, the DBMS can roll back transactions that were in progress but not yet committed at the time of the failure. This ensures that the database remains consistent and does not contain partially completed transactions. Additionally, the DBMS can roll forward committed transactions from the log to bring the database up to its most recent consistent state.
Write-Ahead Logging (WAL): WAL is a protocol that ensures that changes to the database are recorded in the transaction log before they are applied to the database itself. This guarantees that the database can be recovered to a consistent state even if a failure occurs during the process of applying changes.
Frequently Asked Questions
What is recoverable vs irrecoverable?
Recoverable means data can be restored after failure, while irrecoverable implies permanent data loss.
What are the types of recoverable?
Two types include recoverable schedules, where all transactions can be undone, and cascading rollback schedules, where partial undoing is allowed.
What does Commit mean in transaction tables?
Commit is a transaction state/operation. It safely stores the changes done during transaction execution and makes them permanent. It marks the successful completion of the Transaction.
What is the condition for recoverability of a schedule by cascading rollback?
A schedule will be recoverable with cascading rollback if transaction Tj reads the updated value of Transaction Ti and commit of transaction Tj is delayed till commit of Ti.
Conclusion
In this blog of Recoverability of schedules, we start by introducing the schedules. We go through the chart of types of schedules. Later we learn about Non-recoverable and recoverable schedules with examples.
We learn about the types of recoverability of schedules with cascading rollback and without cascade with their examples each.
You can visit here to learn more about different topics related to database management systems.
Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.
85+ registered