Log-Based Recovery and Write-Ahead Logging (WAL) in DBMS

37
0

Introduction

A database can fail while a transaction is changing data. Some changes may have reached storage, while others exist only in memory. After restarting, the database needs enough information to determine which effects must remain and which must be removed.

Log-based recovery records information about changes so that the database can recover correctly. Write-ahead logging (WAL) sets an important ordering rule: the required log records must reach durable storage before the corresponding changed data pages.

Example Table

Consider an Account table that stores account identifiers, holder names, and balances.

Account: initial data

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

AccountID is the primary key and uniquely identifies each account.

A transaction named T1 transfers 200 from Asha to Ravi:

  1. Asha’s balance changes from 1000.00 to 800.00.

  2. Ravi’s balance changes from 500.00 to 700.00.

  3. The transaction commits.

The required final result is:

AccountID

HolderName

Balance

1

Asha

800.00

2

Ravi

700.00

Both updates must succeed together. A result containing only Asha’s deduction would represent an incomplete transfer.

What Is Log-Based Recovery?

Log-based recovery uses saved records of database changes to restore a consistent state after a failure.

It supports two main goals:

  • Atomicity: Incomplete transactions must not leave partial effects.

  • Durability: Durably committed transactions must survive a crash when the required storage and recovery information remain available.

The database creates recovery information during normal processing. After a crash, it uses that information to reconstruct the required state.

A recovery log is different from an application error log. It contains information used to recover data, not simply messages describing errors.

What Does a Recovery Log Record?

A conceptual update record can be written as:

<T1, Account[1].Balance, 1000.00, 800.00>

Here:

  • T1 identifies the transaction.

  • Account[1].Balance means the balance of the account whose AccountID is 1.

  • 1000.00 is the old value.

  • 800.00 is the new value.

  • The angle brackets group the fields into one record.

The old value can support UNDO, which reverses a change. The new value can support REDO, which reapplies a change.

The transfer can be represented by this conceptual log:

Order

Log record

Meaning

1

<START T1>

The transaction begins.

2

<T1, Account[1].Balance, 1000.00, 800.00>

Record Asha’s balance change.

3

<T1, Account[2].Balance, 500.00, 700.00>

Record Ravi’s balance change.

4

<COMMIT T1>

Record successful completion.

Inside a recovery log

Inside a recovery log

These records explain the idea. They are not SQL statements or the actual log format of MySQL or PostgreSQL. Real systems may record page changes, row changes, or other recovery information rather than complete old and new values.

A Log Sequence Number (LSN) identifies a position in the log. It helps the database track the order of records and how far logging or recovery has progressed.

Why Is Write-Ahead Logging Needed?

Databases keep working copies of data pages in memory. A page is a unit of data transferred between memory and storage.

After an update, its page may remain in memory before being written to a data file. Such a modified page is called a dirty page.

Two situations can arise:

  • A changed page reaches its data file before the transaction commits.

  • A transaction commits before all its changed pages reach their data files.

The first situation may require removing incomplete effects. The second may require reconstructing committed changes.

WAL makes sure the necessary recovery information is saved before the database depends on it.

The Two Write-Ahead Logging Rules

1. Log Before Writing the Changed Data Page

Before a changed page is written to its persistent data file, the log records needed to recover those changes must already be durable.

For Asha’s deduction:

Required log information becomes durable
↓
The changed data page may be written

The downward arrow shows the required order.

This rule concerns writing the page to persistent storage. It does not mean the database must flush the log before changing a working copy in memory.

2. Flush the Required Log Before Confirming Durable Commit

Before confirming a durable commit, the database must make the transaction’s required log records, including its commit record, durable.

Flushing means pushing buffered information to persistent storage with the required durability guarantees.

The changed data pages can be written later. If a crash occurs first, the durable log provides the information needed for recovery.

These guarantees assume durable commit settings and storage that correctly honours write requests. Settings that delay log flushing can allow recently acknowledged transactions to be lost after a crash.

Two Write-Ahead Logging rules

Two Write-Ahead Logging rules

UNDO and REDO After a Crash

UNDO: Remove Incomplete Effects

Suppose Asha’s deduction reaches a data file, but the database crashes before T1 commits.

In a recovery design that uses old values for undo, recovery restores Asha’s balance from 800.00 to 1000.00.

If multiple changes from the incomplete transaction need undoing, they are generally reversed in the opposite order from which they occurred.

REDO: Reconstruct Required Changes

Suppose the complete transfer commits durably, but some changed data pages have not reached their files when the database crashes.

Recovery uses the log to restore the required changes:

  • Asha’s balance becomes 800.00.

  • Ravi’s balance becomes 700.00.

REDO does not mean blindly running the original SQL again. For example, recovery must not subtract another 200 from an already updated balance. Log positions and page information help recovery determine which changes need applying.

Assuming no other transactions change these accounts:

Situation at the crash

Required recovery outcome

Asha

Ravi

Transfer did not commit; incomplete changes reached data files

Remove incomplete effects.

1000.00

500.00

Transfer committed durably; some changes are missing from data files

Reconstruct the committed result.

800.00

700.00

UNDO and REDO after a crash

UNDO and REDO after a crash

These are the required outcomes, not one universal recovery algorithm. Some algorithms replay changes from both committed and incomplete transactions before handling incomplete effects.

MySQL uses redo and undo information. PostgreSQL replays WAL and uses transaction status and row visibility rules to keep aborted changes from appearing as committed data.

Deferred Update and Immediate Update

Log-based recovery can also be understood by considering when transaction changes are allowed to reach the database.

Approach

When can changes reach database data pages?

Recovery requirement

Deferred update

Transaction changes are kept from persistent database pages until commit.

Incomplete transactions need no data-page UNDO. Committed changes may need REDO.

Immediate update

Changes may reach database pages before commit.

Incomplete effects may need UNDO. Committed changes may need REDO if pages were not all written.

“Immediate” means changes are allowed before commit. It does not mean every SQL statement immediately writes its changed page to disk.

Two related storage-policy terms explain why recovery work is needed:

  • STEAL: A page containing uncommitted changes may be written to storage. The system must be able to remove or invalidate those incomplete effects.

  • NO-FORCE: Commit does not require every changed data page to be written. Recovery must be able to reconstruct missing committed changes.

WAL supports these flexible page-writing policies while keeping recovery information safe.

Checkpoints and Log Management

A checkpoint records recovery progress and coordinates writing changed pages. It helps the database determine where recovery work needs to begin instead of processing the entire log history.

A checkpoint:

  • Does not commit active transactions.

  • Does not replace a backup.

  • Does not automatically make every older log record safe to delete.

Log records may still be needed for active transactions, recovery, replication, or backups. The database manages log reuse according to those requirements.

WAL can also improve performance because log records are commonly appended in order, while data-page writes can be spread out. With group commit, one log flush can make the records of several transactions durable.

A Compact SQL Example

SQL statements cause the database to generate recovery information internally. Applications do not manually insert the conceptual log records shown earlier.

The following example demonstrates commit and rollback outcomes. It does not simulate a crash or directly inspect recovery logs.

Assume no other transaction changes these accounts during the example.

1. Create the Initial Data

Objective: Create the two accounts with their 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;

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

2. Cancel an Incomplete Transfer

Objective: Remove Asha’s deduction when the transfer is not completed.

Consider the following queries to perform the deduction and then roll back the transaction.

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

Expected result

AccountID

HolderName

Balance

1

Asha

1000.00

2

Ravi

500.00

ROLLBACK removes this transaction’s effects. The balances return to their starting values.

3. Commit the Complete Transfer

Objective: Save both account changes as one completed transaction.

Consider the following queries to transfer 200 from Asha to Ravi.

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

With durable commit settings, successful commit protects this completed transfer against a crash when the required storage survives. Recovery can reconstruct missing data-page changes from the durable recovery information.

A later ROLLBACK cannot undo this already committed transaction.

Important Limits of WAL

WAL protects recovery ordering, but it does not solve every data problem:

  • It does not replace backups. Losing both data files and required logs can make recovery impossible without another copy.

  • It does not detect incorrect business decisions. A wrongly calculated transfer can still commit.

  • It does not provide concurrency control by itself. Locks or other transaction-control methods are still needed.

  • It depends on durability settings and storage behaviour. A log record held only in ordinary memory cannot survive power loss.

Summary

Log-based recovery records enough information to remove incomplete effects and preserve committed work. WAL requires the necessary log records to become durable before changed data pages are written and before a durable commit is confirmed.

UNDO and REDO address different recovery needs, while checkpoints reduce recovery work. Together, these mechanisms allow databases to delay data-page writes without treating an incomplete transaction as successful or losing durably committed changes after a recoverable crash.

CS Core

Read Similar Blogs

Comments0