How Does the Write-Ahead Log Enable Crash Recovery?
Understand how databases use the write-ahead log to redo committed changes and undo incomplete ones after a crash.
Expected Interview Answer
The write-ahead log enables crash recovery because, on restart, the database replays log records to redo committed changes that never made it to data pages, then undoes changes from transactions that were still in progress when the crash happened, restoring the database to a consistent state.
Recovery typically runs in phases: an analysis pass scans the log to find the last checkpoint and determine which transactions were active at crash time, a redo pass replays every logged change from that point forward so all committed effects are reapplied to the data pages even if they were lost from memory, and an undo pass rolls back any changes belonging to transactions that never committed. Because the log records changes in order with enough detail to reconstruct or reverse them, the engine does not need to trust the state of the data pages at all — it treats the log as the source of truth and rebuilds everything from it.
- Guarantees committed transactions survive any crash
- Automatically discards effects of incomplete transactions
- Avoids relying on the on-disk data pages being consistent at crash time
- Bounds recovery time by starting from the last checkpoint instead of the beginning of history
AI Mentor Explanation
Think of a match where the electronic scoreboard freezes mid-over due to a power cut, but the third umpire has a complete ball-by-ball log. On power restoration, officials replay every logged ball since the last confirmed total to rebuild the correct score, and if an over was still being bowled with an unconfirmed final ball, that partial entry is discarded rather than counted. WAL-based recovery works identically: replay everything logged and confirmed, discard anything left incomplete.
Step-by-Step Explanation
Step 1
Analysis pass
Scan the log from the last checkpoint to identify which transactions were committed, and which were still active at crash time.
Step 2
Redo pass
Replay every logged change since the checkpoint, reapplying effects to data pages regardless of whether those pages were flushed before the crash.
Step 3
Undo pass
Roll back any changes made by transactions that were active but never committed, using the log to reverse each operation.
Step 4
Resume normal operation
Once redo and undo complete, the database is in a consistent state and can accept new transactions.
What Interviewer Expects
- Clear description of the redo and undo phases
- Understanding that recovery starts from the last checkpoint, not from scratch
- Awareness that the log, not the data pages, is treated as the source of truth
- Ability to explain why uncommitted transactions must be rolled back on recovery
Common Mistakes
- Describing only redo and forgetting the undo phase entirely
- Assuming recovery always replays the entire log history from the beginning
- Confusing crash recovery with point-in-time backup restoration
- Not explaining why the log is trusted over the potentially inconsistent data pages
Best Answer (HR Friendly)
“When a database crashes, it uses the write-ahead log to fix itself on restart. It replays every change that was logged and committed but might not have been saved to disk yet, and it undoes any changes from transactions that were still in progress when the crash happened, so the database ends up in a correct, consistent state.”
Code Example
-- Session 1: start a transaction and update a row, but do not commit
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
-- (imagine the server process crashes here, before COMMIT)
-- On restart, PostgreSQL's recovery process (via pg_wal) will:
-- 1) Redo any committed WAL records not yet flushed to disk
-- 2) Roll back this open, uncommitted transaction entirely
-- After recovery, the balance is unchanged because the
-- transaction never reached COMMIT:
SELECT balance FROM accounts WHERE account_id = 1;Follow-up Questions
- What is the difference between the redo log and the undo log during recovery?
- How does a checkpoint reduce the amount of log recovery has to replay?
- What happens if the WAL file itself is corrupted or lost?
- How does recovery differ between a single-node database and a replicated cluster?
MCQ Practice
1. During WAL-based crash recovery, what does the redo pass do?
Redo replays logged changes so that committed effects, which may not have reached the data pages before the crash, are reapplied.
2. What happens to a transaction that was active but not committed when the database crashed?
Uncommitted transactions are undone during recovery so the database never retains partial, inconsistent effects.
3. Why does recovery typically start from the last checkpoint rather than the beginning of the WAL?
A checkpoint marks a point where data pages were known to be flushed, so recovery only needs to replay log records after that point.
Flash Cards
What are the two main phases of WAL-based crash recovery? — Redo (reapply committed changes) and undo (roll back incomplete transactions).
Why is the log trusted over the data pages during recovery? — Data pages may be inconsistent or partially written at crash time; the log holds the full, ordered record of intent.
What limits how much of the log recovery must replay? — The last checkpoint, which marks a known-durable state to start replay from.
What happens to an in-progress, uncommitted transaction after a crash? — It is rolled back entirely during the undo phase of recovery.