Database Recovery: WAL, Checkpoints, Undo, and Redo

A committed transaction must survive a crash; an aborted transaction must leave no visible effect. Recovery turns those promises into storage rules. The central idea is simple: record enough history before changing durable data, then use that history after failure.

Start by naming the failure

Failure	What is lost or damaged	Main response
Transaction abort	One transaction’s partial work	Undo or discard its versions
Process or power failure	Volatile memory	Replay the log during restart
Storage-device failure	Database pages and perhaps local log	Restore backup, then apply archived log
Site loss	Machine or location	Fail over to an independent copy

A backup is not a transaction log. A backup supplies an older complete state; the log explains how to advance that state and which incomplete work to remove.

In plain terms: a backup is a photograph. The log is the sequence of events recorded after the photograph was taken. Recovery often needs both.

The write-ahead rule

Write-ahead logging (WAL) imposes two ordering rules:

1. Before a dirty data page reaches durable storage, the log record describing that change must be durable.
2. Before the database reports COMMIT success, the transaction’s commit record must be durable.

The first rule makes undo or redo possible even if a changed page is written early. The second makes the client-visible meaning of commit honest. WAL does not require every data page to be flushed at commit; usually only the log is forced.

transaction changes page in memory
        │
        ├── append log record
        ├── flush log through COMMIT
        └── reply success

dirty page may reach disk later

Why STEAL and FORCE matter

Two buffer-manager choices determine the recovery work:

Policy	Meaning	Consequence
STEAL	An uncommitted dirty page may be written	Recovery may need UNDO
NO-STEAL	Uncommitted pages stay in memory	No on-disk uncommitted update
FORCE	All changed pages are flushed at commit	Committed work needs no REDO
NO-FORCE	Commit may leave dirty pages in memory	Recovery may need REDO

Most high-performance systems favor STEAL + NO-FORCE: memory can be reused and commit does not wait for many random page writes. The price is support for both undo and redo.

What restart recovery actually asks

After a crash, recovery must classify log records around the failure boundary:

• Which transactions committed? Their effects may require redo.
• Which transactions were still active? Their visible on-disk effects may require undo.
• Which page updates are already reflected on disk? Page metadata such as a log sequence number helps avoid repeating unnecessary work.

An idempotent redo operation is safe to consider more than once. Recovery checks whether a page already includes a change before applying it.

Checkpoints bound the search

A checkpoint records enough information about active transactions and dirty pages to avoid scanning from the beginning of the log. It does not replace WAL and it does not necessarily flush the entire database.

More frequent checkpoints usually shorten restart work but create more foreground I/O. Less frequent checkpoints reduce that overhead but increase recovery time. The useful target is a tested recovery-time objective, not “as often as possible.”

Recovery beyond one database

Recovery becomes harder when an operation crosses services. A local database can undo bytes using its log; an external email, payment, or shipment cannot be physically undone the same way. Those workflows need idempotency keys, retry records, and compensating business actions.

This is also why “we have replicas” is not a backup strategy. Replication can faithfully copy accidental deletion or corruption. Recovery plans should cover point-in-time restore, credential loss, regional failure, and regular restore drills.

Review card

• WAL reaches durable storage before the data page it describes.
• A commit response requires a durable commit record.
• STEAL creates a possible undo requirement; NO-FORCE creates a possible redo requirement.
• A checkpoint limits recovery work; it does not replace the log.
• Backup, replication, and transaction logging solve different failure modes.
• A recovery design is incomplete until restore time and data loss are tested.

transaction 在 memory 修改 page
        │
        ├── append log record
        ├── flush log 到 COMMIT
        └── 回覆成功

dirty page 可以之後才寫入 disk