Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction  
2.
Schedule
3.
Recoverable Schedule
3.1.
Recoverable Schedule with cascading rollback
3.1.1.
Example
3.2.
Cascade less Recoverable Schedule
3.2.1.
Example
4.
Irrecoverable/Non-recoverable Schedule
4.1.
Example
5.
Capabilities of recoverability in a DBMS
6.
Frequently Asked Questions
6.1.
What is recoverable vs irrecoverable?
6.2.
What are the types of recoverable?
6.3.
What does Commit mean in transaction tables?
6.4.
What is the condition for recoverability of a schedule by cascading rollback?
7.
Conclusion
Last Updated: Mar 27, 2024
Easy

Recoverability in DBMS

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

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. 

Recoverability in DBMS

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).

Schedule
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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.

You can also read about the Log based recovery.

Irrecoverable/Non-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😄.

Must Recommended Topic, Schema in DBMS

Capabilities of recoverability in a DBMS

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.

Recommended Readings:

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.

Previous article
Introduction to Scheduling in DBMS
Next article
Condition of Schedules to be View-Equivalent
Live masterclass