Database Recovery in DBMS: Failures, Storage and Goals

44
1

Introduction

A database may stop working while an update is still in progress. A transaction can fail, a server can lose power, or a disk can become damaged. Without recovery support, these failures could leave incomplete changes or cause saved work to disappear.

Database recovery restores a usable, consistent database after a failure. Understanding what failed, where information was stored, and which transactions committed explains what recovery must achieve.

Example Table

Consider an Account table that stores each account’s identifier, holder name, and current balance.

Account: initial data

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

AccountID is the primary key and uniquely identifies each account.

Suppose a transaction transfers 200 from Asha to Ravi. It must perform two updates:

  1. Subtract 200 from Asha’s balance.

  2. Add 200 to Ravi’s balance.

After a successful transfer, the balances should be:

AccountID

HolderName

Balance

1

Asha

800.00

2

Ravi

700.00

The combined balance remains 1500.00. If only the subtraction survives, the database incorrectly shows a combined balance of 1300.00.

Both updates therefore belong to one transaction, a group of operations treated as one unit of work.

Types of Database Failures

Different failures require different recovery actions.

Failure type

What happens?

Example

Main recovery action

Transaction failure

One transaction cannot complete successfully.

A transaction is selected for rollback after a deadlock.

Remove its incomplete changes.

System crash

The database process or operating system stops. Working memory is lost.

Power failure or server crash.

Recover from surviving data files and recovery logs.

Media failure

Stored database files become damaged or unavailable.

Disk failure or storage corruption.

Restore a valid backup and apply available recovery records.

Types of database failures

Types of database failures

A deadlock occurs when transactions wait for resources held by one another and cannot move forward.

For ordinary crash recovery, the required persistent files must remain available. If the failure also destroys those files, recovery must address storage loss as well.

What About Accidental Changes?

An incorrect update or deletion can commit successfully. The database then treats it as committed work, even if it was a human mistake.

Normal crash recovery does not automatically reverse such a change. Correcting it may require a new transaction or restoring data to an earlier point.

A network disconnection creates another difficulty: a client might lose contact just after the database commits. A missing success response does not always mean the transaction failed. The application should check the outcome before repeating an operation.

Storage Types in DBMS

Recovery depends on which information survives a failure.

Storage type

Meaning

Examples

Main limitation

Volatile storage

Loses its contents when power is lost.

RAM, database memory buffers.

Cannot preserve data through a power failure.

Non-volatile storage

Retains stored information without power.

SSDs, hard disks.

Can still fail or become corrupted.

Stable storage

An ideal model in which stored information survives failures.

Approximated using redundant copies and careful storage techniques.

No physical storage provides an absolute guarantee against every failure.

Non-volatile does not mean indestructible. A disk may survive a power interruption but fail physically later.

Memory Buffers and Data Pages

Databases commonly read and write data in fixed-size units called pages.

Frequently used pages are kept in a memory area called the buffer pool or buffer cache. Updating a row usually changes its in-memory page first.

A dirty page is a page that has changed in memory but has not yet been written back to its data file. Here, “dirty” means modified, not damaged.

This creates two recovery concerns:

  • A committed change may still be missing from a data file.

  • Depending on the database’s storage policy, a data file may contain changes from a transaction that has not committed.

Recovery information allows the database to handle these situations correctly.

Recovery Goals

1. Remove Incomplete Transaction Effects

Atomicity means that a transaction’s changes take effect together or do not take effect at all.

If the transfer stops after subtracting 200 from Asha but before completing and committing, recovery must prevent that partial transfer from becoming the final result.

2. Preserve Committed Work

Durability means that successfully committed work survives the failures covered by the system’s durability guarantees.

If the transfer committed durably before a server crash, recovery must preserve the balances of 800.00 and 700.00, even if some changed data pages had not yet reached their files.

This depends on correct durability settings, working storage, and the availability of the required recovery information.

3. Restore a Consistent Database

Recovery should leave the database in a state that reflects complete transactions.

For the transfer, the combined balance must remain 1500.00. However, recovery cannot repair incorrect business logic in a successfully committed transaction. Correct transaction code and constraints are still necessary.

Assuming no other transactions change these accounts:

Failure point

Required outcome after recovery

Asha

Ravi

Before the transfer commits

Remove the incomplete transfer.

1000.00

500.00

After the transfer commits durably

Preserve the completed transfer.

800.00

700.00

Recovery keeps complete transactions

Recovery keeps complete transactions

4. Limit Data Loss and Downtime

For backup-based recovery, two useful planning terms are:

  • Recovery Point Objective (RPO): The maximum acceptable amount of lost recent data, measured in time.

  • Recovery Time Objective (RTO): The target time for restoring service.

For example, an RPO of five minutes allows at most five minutes of recent work to be lost. An RTO of thirty minutes targets restoring service within thirty minutes.

These are planning targets. Backups, log retention, and restore procedures must support them.

How Logs, Checkpoints, and Backups Help

Recovery Logs and Write-Ahead Logging

A recovery log records information needed to recover database changes.

With write-ahead logging (WAL), the required log records must reach persistent storage before the corresponding changed data page is written to its data file.

With durable commit settings, the required log records, including the commit information, become durable before the database confirms successful commit. Every changed data page does not need to be written at that moment.

This allows recovery to reconstruct changes that were committed but had not yet reached the data files.

Memory , logs and data files

Memory , logs and data files

UNDO and REDO

Two important recovery ideas are:

Idea

Purpose

Transfer example

UNDO

Remove the effects of incomplete or aborted work.

Remove Asha’s deduction if the transfer never committed.

REDO

Reapply logged changes when required to reconstruct database pages.

Restore the committed transfer if its changes were missing from the data files.

These describe recovery goals, not one identical internal process used by every database.

For example, InnoDB uses redo and undo information. PostgreSQL replays WAL and uses transaction status and row visibility rules to prevent aborted changes from appearing as committed data.

Some recovery algorithms also replay changes from incomplete transactions before handling their effects. REDO should therefore not be understood as always replaying only committed transactions.

Checkpoints

A checkpoint records recovery progress and coordinates writing changed pages.

It helps reduce the amount of recovery work needed after a crash. A checkpoint does not commit active transactions, and it is not a replacement for a backup.

Backups and Log Replay

A backup provides a recoverable copy of database data.

After storage loss, recovery may:

  1. Restore a valid backup.

  2. Replay the required saved logs.

  3. Bring the database to a consistent recoverable point.

Point-in-time recovery restores the database to a selected point, such as just before an accidental committed deletion. It requires a suitable backup and the necessary log history.

Replication can improve availability, but it does not replace backups. An accidental deletion may also be copied to a replica.

A Compact SQL Example

The following example demonstrates the visible effects of rollback and commit. It does not simulate a server crash or disk failure.

Assume the two accounts exist, Asha has sufficient funds, and no other transaction changes these rows during the example.

1. Create the Initial Data

Objective: Create the account table with the starting balances.

Consider the following queries to create and populate Account.

CREATE TABLE Account (
AccountID INT PRIMARY KEY,
HolderName VARCHAR(50) NOT NULL,
Balance DECIMAL(10, 2) NOT NULL
);
START TRANSACTION;
INSERT INTO Account (AccountID, HolderName, Balance)
VALUES
(1, 'Asha', 1000.00),
(2, 'Ravi', 500.00);
COMMIT;
SELECT AccountID, HolderName, Balance
FROM Account
ORDER BY AccountID;

DECIMAL(10, 2) and NUMERIC(10, 2) store exact numeric values with two decimal places.

Expected result

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

2. Roll Back an Incomplete Transfer

Objective: Cancel the transaction after the deduction, before the credit is performed.

Consider the following queries to undo the incomplete transfer.

START TRANSACTION;
UPDATE Account
SET Balance = Balance - 200.00
WHERE AccountID = 1;
ROLLBACK;
SELECT AccountID, HolderName, Balance
FROM Account
ORDER BY AccountID;

ROLLBACK cancels this transaction’s changes. Asha’s deduction does not remain in the table.

Expected result

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

The same atomicity goal applies after a crash: an incomplete transfer must not remain as a completed change.

3. Commit the Complete Transfer

Objective: Save both sides of the transfer together.

Consider the following queries to transfer 200 from Asha to Ravi and commit the transaction.

START TRANSACTION;
UPDATE Account
SET Balance = Balance - 200.00
WHERE AccountID = 1;
UPDATE Account
SET Balance = Balance + 200.00
WHERE AccountID = 2;
COMMIT;
SELECT AccountID, HolderName, Balance
FROM Account
ORDER BY AccountID;

Expected result

AccountID

HolderName

Balance

1

Asha

800.00

2

Ravi

700.00

COMMIT completes the transaction. A later ROLLBACK cannot cancel this already committed transfer.

If a server crash occurs after durable commit and the required storage survives, recovery must preserve this result.

Summary

Database recovery handles transaction failures, system crashes, and storage failures. Its main goals are to remove incomplete transaction effects, preserve committed work, and restore a consistent database. The recovery method depends on which data and recovery information remain available.

Memory buffers provide fast access, while persistent storage holds data files and recovery information. Logs support recovery, checkpoints reduce recovery work, and backups protect against losses that surviving database files alone cannot repair.

CS Core

Read Similar Blogs

Comments0