Log Based Recovery in DBMS

Log Based Recovery in DBMS

Log-based recovery is an important technique used in Database Management Systems (DBMS) to maintain database consistency and recover data after a system failure. A log is a collection of records stored in a stable storage location. It keeps track of the operations performed by transactions so that the database can be restored to a consistent state after an unexpected failure.

Whenever a transaction modifies data, the corresponding operation is recorded in the log. According to the Write-Ahead Logging (WAL) principle, the log record must be written to stable storage before the actual database modification is performed.

For example, suppose a transaction changes a student’s city from “Noida” to “Bangalore”. The log may contain the following records:

• <Tn, Start>
• <Tn, City, 'Noida', 'Bangalore'>
• <Tn, Commit>

These records allow the DBMS to determine which operations were completed and which transactions need to be undone or redone after a crash.

How Log-Based Recovery Works

The basic idea behind log-based recovery is to maintain a history of transaction operations. When a failure occurs, the recovery manager examines the log and determines the appropriate action for each transaction.

Depending on when database changes are applied, log-based recovery can be broadly classified into two approaches:

1. Deferred Database Modification
2. Immediate Database Modification

1. Deferred Database Modification

In deferred database modification, changes made by a transaction are not immediately applied to the database. Instead, the changes are first recorded in the log and are applied to the database only after the transaction successfully commits.

This approach makes recovery relatively simple because an uncommitted transaction has not modified the actual database.

Recovery Steps in Deferred Update

1. Locate the most recent checkpoint in the log.
2. Identify transactions that started after the checkpoint.
3. Transactions containing both Start and Commit records are redone.
4. Transactions containing a Start record but no Commit record are ignored because their changes were never applied.

Example of Deferred Database Modification

Consider two transactions:

• T1: Transfers Rs. 200 from account A to account B.
• T2: Withdraws Rs. 100 from account C.

Initial balances are:

• A = 1000
• B = 1500
• C = 2000

Log Entry	Database Action
[Start_transaction, T1]	Transaction starts
[Write, T1, A, 800]	Not applied yet
[Write, T1, B, 1700]	Not applied yet
[Commit, T1]	Apply changes: A = 800, B = 1700
[Start_transaction, T2]	Transaction starts
[Write, T2, C, 1900]	Not applied yet
[Commit, T2]	Apply change: C = 1900

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

If the system crashes during execution, the recovery process checks the log:

• Transactions with a Commit record are redone.
• Transactions without a Commit record are ignored.

This prevents incomplete transactions from producing inconsistent database states.

2. Immediate Database Modification

In immediate database modification, changes are applied to the database as the transaction executes. The transaction does not need to wait until commit before modifying database pages.

Because changes can be written before a transaction commits, the DBMS must maintain sufficient log information to undo incomplete transactions and, when required, redo committed transactions.

This technique relies heavily on Write-Ahead Logging (WAL). The log record describing a change must reach stable storage before the corresponding database change is written.

Recovery Techniques

Undo

Undo recovery reverses changes made by transactions that failed or were active when the system crashed. The old values stored in the log are used to restore the database.

Redo

Redo recovery reapplies changes made by committed transactions when those changes may not yet have been permanently written to the database.

Undo/Redo

Many immediate-update recovery systems use both operations. Uncommitted transactions are undone, while committed transactions are redone when necessary.

Recovery Steps in Immediate Update

1. Locate the latest checkpoint in the log.
2. Identify transactions that were committed before the checkpoint.
3. Transactions with both Start and Commit records are considered committed and may need to be redone.
4. Transactions with a Start record but no Commit or Abort record are treated as incomplete and are undone.
5. For transactions containing multiple writes, undo operations are performed in reverse order where required.

Example of Immediate Database Modification

Using the same transactions T1 and T2:

Log Entry	Database Action
[Start_transaction, T1]	Begin T1
[Write, T1, A, 1000, 800]	A is updated to 800 immediately
[Write, T1, B, 1500, 1700]	B is updated to 1700 immediately
[Commit, T1]	T1 commits
[Start_transaction, T2]	Begin T2
[Write, T2, C, 2000, 1900]	C is updated to 1900 immediately
[Commit, T2]	T2 commits

If a crash occurs, the recovery manager examines the transaction status in the log.

• Transactions that have committed are redone if necessary.
• Transactions that were active when the crash occurred are undone.

Log-Based Recovery in a Multiuser Environment

In a multiuser database system, several transactions may execute concurrently. Therefore, recovery becomes more important because transactions can read and modify shared data at the same time.

If a transaction fails before committing, its changes must be rolled back. If a committed transaction’s changes are not safely stored, those changes may need to be redone during recovery.

Concurrency-related problems such as deadlocks can also cause transactions to be aborted. The recovery mechanism must ensure that aborted transactions do not leave partial or inconsistent changes in the database.

Important Rules of Log-Based Recovery

1. If the log contains both <Ti, Start> and <Ti, Commit>, transaction Ti is considered committed and may need to be redone.
2. If the log contains <Ti, Start> but does not contain <Ti, Commit> or <Ti, Abort>, transaction Ti is considered incomplete and must be undone.
3. Log records must be stored safely before the corresponding database modifications when using Write-Ahead Logging.
4. Checkpoints reduce the amount of log that needs to be processed during recovery.

Deferred vs Immediate Database Modification

Feature	Deferred Modification	Immediate Modification
Database update	After commit	During transaction execution
Uncommitted changes	Not applied to the database	May already be applied
Recovery	Mainly redo committed transactions	May require undo and redo
Implementation	Relatively simple	More complex
Use of old values	Generally not required for undo	Required for undo operations

YT:- DecodeIT

Advantages of Log-Based Recovery

• Helps maintain database consistency after failures.
• Allows committed transactions to be recovered.
• Prevents incomplete transactions from leaving invalid changes.
• Works with both deferred and immediate update techniques.
• Supports reliable recovery from system crashes.
• Checkpoints can reduce recovery time.

Conclusion

Log-based recovery is a fundamental recovery mechanism in DBMS that protects database consistency in the event of system failures. By maintaining a reliable record of transaction operations, the recovery system can determine which transactions should be undone and which should be redone.

Deferred modification simplifies recovery by applying changes only after a transaction commits, while immediate modification provides greater flexibility but may require both undo and redo operations. In both cases, the transaction log acts as a critical source of information for restoring the database to a consistent state.

Keywords

Log Based Recovery in DBMS, Log Based Recovery, DBMS Recovery, Database Recovery, Recovery Techniques in DBMS, Deferred Database Modification, Immediate Database Modification, Write Ahead Logging, WAL in DBMS, Undo Redo Recovery, Transaction Recovery, Database Failure Recovery, Checkpoint in DBMS, Transaction Log, DBMS Transactions