Table of Contents

1 Database Recovery Management in DBMS

Database Recovery Management in DBMS

Database Recovery Management in DBMS mainly concerns transaction Recovery System is DBMS or how to recover the Database in case of system failure. This tutorial will learn and understand the different transaction recovery techniques used in database recovery management in DBMS.

Database recovery management in DBMS is an important topic from the GATE exam point of view. Some time conceptual questions are asked in GATE(CS/IT) or UGC NET Exam. We will see some questions at the end of this tutorial.

Table of Content

In this database recovery management in the DBMS tutorial, we will explain the following topic with an example.

Failure in DBMS and Types of Failure.
Transaction Failure and Reasons for Transaction Failure.
Failure anticipation and recovery.
DBMS Recovery Techniques

(A) Log Based Recovery

(i) Immediate Mode (ii) Deferred Mode

(B) Shadow Paging Recovery Method

GATE question Based on Log Recovery.
Conclusion and Summary

Also Read – Mode of Data Transfer in Computer Architecture

Frequently Asked Questions

Some important Questions asked from Recovery System in DBMS are as follows. These questions are generally asked in Technical Interview of Software Developer or Database Administrator Post.

Write the name of different types of failure in DBMS?
What is System Log?
Write the difference between Immediate ModeMode and differed ModeMode?
What is Transaction Failure?
What t are the reasons for transaction Failure?
What is Transaction Recovery?
What is Log Based Recovery?
What is Shadow Paging?

Let’s start with failure in DBMS

What is Failure in DBMS?

Whenever the system fails to function according to its specifications and doesn’t deliver the expected service, that situation is called the failure of the system.

Database recovery management or database recovery in DBMS help to recover the failure.

Types of Failures in DBMS

Different types of Failure in DBMS are as follow –

Hardware Failures

Include memory errors, disk problems, crashes, bad sectors, etc.

Software Failures

Include software failures such as DBMS software, O.S., application programming, etc.

System Crash

A system failure can occur due to hardware/software/power failure.

Transaction Failures

If a transaction fails before Commit, then Database can be corrupted. But we have some recovery methods if there is any transaction failure.

What is Transaction Failure?

When a transaction is submitted to a database system, this is the responsibility of the database management system to execute all the operations in the Transaction.

According to the atomicity property of Transaction, all the operations in a transaction have to be executed, or none will be completed. There won’t be a case where only half of the operations will be executed, or this case will lead to a transaction failure.

The reasons for transaction failure are mentioned below –

System Failure or System Crash

During the Transaction, at a certain point, the system may fail due to various reasons like power failure, network failure, some internal errors in the system, etc., which will lead to transaction failure.

Also Read – Merge Sort and It’s Time Complexity Tutorial

Transaction or System Error

This problem may arise due to programming errors, data storing errors, etc. If the program is not well tested and not executed properly, this may cause a transaction failure too.

Concurrency Control Enforcement

Multiple transactions taking place in the database system in parallel may also cause transaction failure. Suppose Concurrent Transactions should be executed inefficient manner.

Recovery from Transaction Failure

Database Recovery management system or database recovery in DBMS from transaction failure provides the following.

Note – The purpose of database recovery in DBMS is to bring the Database into the last consistent state, which existed before the failure.

To recover from transaction failure, the atomicity of transactions as a whole must be maintained. That is, either all the operations are executed or none.

There are three states of database recovery in DBMS. If the Transaction is in any of these three states, then you can recover your Transaction. The three states are as follows-

Pre-condition

Here, in an instant of time, the Database is an inconsistent state.

Condition

It occurs due to some system failure.

Post-condition

It restores the data into a consistent state that existed before the failure.

Also Read – Application of Image Processing

Failure Anticipation and Recovery

Various Anticipation and Recovery are as follow –

Failures without loss of data: The state of the system is reset to that state which existed before the state of Transaction.

Failure with loss of volatile storage: Active Transaction is terminated in an unpredictable manner before it reaches its Commit or rollback state, and contents of the volatile memory are lost.

Failure with loss of non-volatile storage: Due to head crash on a disk drive or errors in writing to a non-volatile device.

Failure with loss of stable storage: Due to natural and human-made disasters, recovery requires manual re-generation of the Database. The remedy is storing multiple copies in physically secured environments in geographically dispersed locations.

Recovery System in DBMS from Transaction Failure

In a database recovery management system, there are mainly two recovery techniques that can help a DBMS in recovering and maintaining the atomicity of a transaction. Those are as follows

1.Log Based Recovery.

2.Shadow Paging

Let us understand each technique in detail.

Also Read – Smart Home Technology in India

Log Based Recovery in DBMS

A log is a sequence of records that contains the history of all updates made to the Database. Log the most commonly used structure for recording database modification. Some time log record is also known as system log.

Update log has the following fields-

Transaction Identifier: To get the Transaction that is executing.
Data item Identifier: To get the data item of the Transaction that is running.
The old value of the data item (Before the write operation).
The new value of the data item (After the write operation).

We denote various kinds of log records, as shown in the following points. This is the basic structure of the format of a log record.

<T, Start >. The Transaction has started.
<T, X, V1,V2>. The Transaction has performed write on data. V is a value that X will have value before writing, and V2 is a Value that X will have after the writing operation.
<T, Commit>. The Transaction has been committed.
<T, Abort>. The Transaction has aborted.

Consider the data Item A and B with initial value 1000. (A=B=1000)

In the above table, in the left column, a transaction is written, and in the right column of the table, a log record is written for this Transaction.

Key Points – Following points should be remembered while studying the Log Based Recovery.

Whenever a transaction performs a write, it is essential that the log record for that write is to be created before the D.B. is modified.
Once a log record exists, we can output the modification into D.B. if required. Also, we have the ability to undo the modification that has already been updated in D.B.

Log Based Recovery work in two modes These modes are as follow-

Immediate Mode
Deferred Mode

Log Based Recovery in Immediate Mode

In immediate Mode of log-based recovery, database modification is performed while Transaction is in Active State.

It means as soon as Transaction is performed or executes its WRITE Operation, then immediately these changes are saved in Database also. In immediate Mode, there is no need to wait for the execution of the COMMIT Statement to update the Database.

Explanation

Consider the transition T1 as shown in the above table. The log of this Transaction is written in the second column. So when the value of data items A and B are changed from 1000 to 950 and 1050 respectively at that time, the value of A and B will also be Update in the Database.

In the case of Immediate Mode, we Need both Old value and New value of the Data Item in the Log File.

Now, if the system is crashed or failed in the following cases may be possible.

Case 1: If the system crashes after Transaction executing the Commit statement.

In this case, when Transaction executed commit statement, then corresponding commit entry will also be made to the Log file immediately.

To recover the database recovery manager will check the log file to recover the Database, then the recovery manager will find both <T, Start > and < T, Commit> in the Log file then it represents that Transaction T has been completed successfully before the system failed so REDO(T) operation will be performed and Updated values of Data Item A and B will be set in Database.

Case 2: If Transaction failed before executing the Commit, it means there is no commit statement in Transaction as shown in the table given below, then there will be no entry for Commit in the log file.

So, in this case, when the system will fail or crash, then the recovery manager will check the Log file, and he will find the < T, Start> entry in the Log file but not find the < T, Commit> entry.

It means before system failure; Transaction was not completed successfully, so to ensure the atomicity property UNDO(T) operation will be performed because Update Values are written in the Database immediately after the write operation. So Recovery manager will set the old value of data items A and B.

Log Based Recovery in Deferred Mode

In the Deferred Mode of Log-based recovery method, all modifications to Database are recorded but WRITE Operation is deferred until the Transaction is partially committed. It means In the case of Deferred mode, Database is modified after Commit operation of Transaction is performed.

For database Recovery in DBMS in Deferred Mode, there may be two possible cases.

Case 1: If the system fails or crashes after Transaction performed the commit operation. In this situation, since the Transaction has performed the commit operation successfully so there will be an entry for the commit statement in the Log file of the Transaction.

So after System Failure, when the recovery manager will recover the Database, then he will check the log file, and the recovery manager will find both <T, Start> and <T, Commit> It means Transaction has been completed successfully before the system crash so in this situation REDO(T) operation will be performed and Updated value of Data item A and B will be set in Database.

So, in this case, when the system will fail or crash, then the recovery manager will check the Log file, and he will find the < T, Start> entry in the Log file but not find the < T, Commit> entry. It means before system failure, Transaction was not completed successfully, so to ensure the atomicity property, the Recovery Manager will set the old value of data items A and B.

Note – In this case of Deferred Mode, there is no need to Perform UNDO (T). Update values of data item not written to Database immediately after the WRITE operation.

In deferred modes, updated values will be written only after the Transaction commit. So, in this case, there is an old value of the data item in the Database.

Shadow Paging Recovery Method

It is a commonly used method for database recovery systems in DBMS. It requires less disk access than do-log methods.

Here the D.B. is partitioned into some number of fixed-length blocks known as pages, and it maintains two-page tables during the life cycle of Transaction.

At the starting of the Transaction, the page tables are identical at that time.

Here each entry contains a pointer to a certain block on the disk. The key idea is to maintain two-page tables during the transaction-1) Current page table 2) Shadow page table.

When the Transaction starts, both the pages are identical. But during the Transaction, the current page table makes all the changes while the shadow page table remains as it was before. On the shadow page, the instructions of the Transaction are stored.

Also Read – How to Become a Certified Scrum Master in 2022 ?

Check Points Recovery Method in DBMS

A checkpoint is another recovery technique used in database recovery management in DBMS. In this technique, checkpoint operation is performed periodically that copies log information onto stable storage (volatile to stable storage). The information and operations performed at each checkpoint consists of the following-

The Start of the checkpoint and the time and date of the checkpoint is written to the log, and it’s done on a stable storage device.
All log data from the buffers within the computer memory is copied to the log on the stable storage.
The databases are updated from the buffers that are in the volatile storage that are then moved to the physical Database.
An end of checkpoint record is written, and the address of the checkpoint record is saved on a file accessible to the recovery routine on start-up after a system crash.
The frequency of check pointing is a design consideration of the recovery system. Following are the options-

The fixed interval of time.
Transaction consistent checkpoint.
Action-consistent checkpoint.
Transaction oriented checkpoint

GATE Exam Questions on Database Recovery

Solution – Option B is the right answer.

Explanation – Since the system failed before record 7 in transaction T2. It means T2 does not perform commit operations. When the Recovery manager checks the log file to recover the Database, he will find the entry <T1, Start > and <T1, Commit> in the Log file.

It means T1 has committed successfully, so record two and record 3 of Transaction T1 will be REDO, and new Value or Updated value of B and M will be set in the Database.

For transaction T2, there is <t2, Start> but < T2, Commit> is not present in the Log record, so in this case, to bring the Database inconsistent state record six will be UNDO, it means the value of B will not be changed to 10500. Value of B set by transaction T1 means 10000 will be written in the Database.