Database recovery preserves atomicity and durability when transactions, processes, machines, or storage fail. This lesson assumes basic ACID knowledge and explains the ordering rules that make crash recovery possible, the restart phases used by ARIES-style designs, and the separate role of backups.
After the lesson, you should be able to classify a failure, trace which log records require redo or undo, explain why a checkpoint is not a backup, and design a restore test around explicit recovery-time and recovery-point objectives.
A DBMS must be able to recover from various types of failures while maintaining the ACID properties of transactions:
| Failure Type | Description | Recovery Method |
|---|---|---|
| Transaction Failure | Logical error (divide by zero, constraint violation) or system error (deadlock) | Transaction rollback (UNDO) |
| System Crash | Power failure, OS crash - volatile memory (buffer) is lost, disk is intact | Log-based recovery (REDO/UNDO) |
| Disk Failure | Head crash, bad sectors - disk data is lost or corrupted | Backup + archive log restore |
| Network Failure | Communication failure in distributed systems | Two-phase commit, retry protocols |
The most common recovery technique. Every database modification is recorded in a log (also called a journal or write-ahead log) before it is applied to the database.
Each log record contains:
The Write-Ahead Logging (WAL) protocol is the foundation of log-based recovery. It has two rules:
This ensures that if a crash occurs, the log always has enough information to either redo committed transactions or undo uncommitted ones.
| Operation | Purpose | When Applied |
|---|---|---|
| REDO | Re-apply changes of committed transactions that may not have been written to disk | Transaction has COMMIT in log but changes may be in buffer only |
| UNDO | Reverse changes of uncommitted transactions | Transaction has no COMMIT in log (was in progress when crash occurred) |
Without checkpoints, restart may need to search much farther back through the log. A checkpoint records recovery metadata that helps locate the active transactions and dirty pages relevant to restart; its exact contents and flush behavior depend on the recovery design.
A quiescent teaching model can pause work and flush pages, while a fuzzy checkpoint allows transactions and page writes to continue. Recovery therefore follows the checkpoint information and LSNs rather than blindly assuming every earlier page is durable.
Transactions that committed can require REDO, and transactions without a durable commit can require UNDO. Whether a particular page operation is repeated depends on the log and the page state, not only on whether its transaction appears before or after the checkpoint record.
Shadow paging is an alternative to log-based recovery. The database maintains two page tables:
On commit, the current page table becomes the new shadow. On abort, simply discard the current page table and restore the shadow. Disadvantage: Causes data fragmentation and is less efficient than WAL for most workloads.
ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) is an influential recovery design built around write-ahead logging, repeating history during redo, and logging undo work. Commercial and open-source databases use related ideas, but their implementations should not all be described as identical ARIES implementations. Its restart model has three phases:
| Backup Type | Description | Recovery Time | Storage |
|---|---|---|---|
| Full Backup | Complete copy of the entire database | Fastest restore | Largest |
| Incremental Backup | Only changes since the last backup (full or incremental) | Slowest restore (chain of backups) | Smallest |
| Differential Backup | All changes since the last full backup | Medium restore (full + one differential) | Medium |
| Archive Log Backup | Backup of transaction logs for point-in-time recovery | Enables recovery to any point in time | Varies |
A buffer manager decides when modified pages leave memory. Under a steal policy, it may write a page containing an uncommitted change to free a frame; recovery then needs UNDO if that transaction aborts. Under no-force, commit does not require every changed data page to reach disk; recovery needs REDO if committed changes were still only in memory at the crash.
Steal plus no-force supports high throughput because transactions do not monopolize buffer frames or force scattered data pages at every commit. WAL makes the combination recoverable: the log describing a change becomes durable before the changed page, and a transaction's commit record becomes durable before success is acknowledged. The sequential log flush is generally cheaper than forcing every data page.
| Buffer Policy | Possible Disk State | Recovery Need |
|---|---|---|
| Steal | Uncommitted change may be on disk | UNDO |
| No-steal | Uncommitted change remains in memory | Less undo pressure, more buffer pressure |
| Force | Committed pages are forced at commit | Less redo pressure, slower commits |
| No-force | Committed page may still be in memory | REDO |
A log sequence number (LSN) orders log records. A page can store the LSN of the latest change reflected in that page, often called pageLSN. Before flushing the page, the log must be durable through that LSN. During redo, comparing a record's LSN with pageLSN helps recovery avoid reapplying work already present on disk.
ARIES-style undo writes compensation log records (CLRs). A CLR records the action taken to reverse an earlier update and indicates where undo should continue. If recovery crashes again, the CLR can be redone, but the already compensated update is not undone a second time. This makes restart itself restartable and prevents an endless cycle of repeating the same undo.
A simple teaching checkpoint stops transactions and flushes every dirty page, but production systems commonly use fuzzy checkpoints so normal work can continue. The checkpoint records enough information about active transactions and dirty pages to choose a safe restart position. Pages may still be dirty when the checkpoint completes; the log remains necessary.
The dirty-page table records pages that might contain updates not yet reflected on disk and a recovery starting point for each page. The transaction table records active work and its latest log position. At restart, analysis reconstructs these tables, redo repeats relevant history from the earliest required point, and undo rolls back loser transactions. A checkpoint limits search work; it does not establish that every page is current.
Suppose transaction T1 updates account A, its update and commit records become durable, but the data page remains in memory. T2 updates account B, WAL reaches disk, and its uncommitted dirty page is flushed under steal. A power failure then erases memory. Restart must redo T1 if A's page did not contain the committed update and undo T2 because B may contain work that never committed.
Recovery actions should be idempotent or protected by LSN checks: repeating restart after another crash must converge on the same consistent state. The DBMS restores structural correctness before opening normal traffic, but applications may still need to retry transactions whose outcome was not observed. A lost client response does not prove that the transaction failed; use transaction identifiers or idempotency rules when duplicate business effects matter.
The recovery point objective (RPO) is the maximum tolerable data-loss window. The recovery time objective (RTO) is the target time to restore service. A nightly full backup may imply nearly a day of data loss unless archived logs or another mechanism closes the gap. A low RPO also fails operationally if log copies share the failed storage or cannot be replayed.
A restore test should begin with a clean target, verify backup checksums or integrity controls, restore the correct base backup, replay logs to a chosen point, and run application-level consistency checks. Measure elapsed time and record dependencies such as encryption keys, extensions, users, and external object storage. Successful backup creation is not evidence that the full recovery chain is usable.
LSN 10: T1 START
LSN 20: T1 UPDATE A old=100 new=80
LSN 30: T2 START
LSN 40: T2 UPDATE B old=50 new=70
LSN 50: T1 COMMIT
--- CRASH ---
T1 is a winner: redo its update if page A is behind LSN 20.
T2 is a loser: undo its update if page B contains LSN 40.Target point: 2026-07-13 09:55:00 UTC
RPO target: 5 minutes
RTO target: 45 minutes
Verify:
- orders balance against payment ledger
- no committed order has missing line items
- application account can connect with least privilege
- replay stops before the known destructive transactionThe DBMS records change information in a durable log before modified data pages must reach their final storage locations. After a crash, recovery can redo changes from committed transactions and undo incomplete work according to the recovery design.
A checkpoint records a recovery reference point and helps limit how far the DBMS must scan during restart. Changes can still occur after the checkpoint, and some dirty pages from before it may not yet be fully written depending on the system.
A successful backup job proves only that the tool produced an artifact.
Explore 500+ free tutorials across 20+ languages and frameworks.