A commit response is a promise about a failure boundary. If a power loss follows an acknowledged order but precedes the page write, the order must still be recoverable. If a data page reaches storage before the log record explaining it, recovery loses its evidence. Logging is therefore a protocol for reconstructing a legal state, not an audit trail added after the fact.
A durable log prefix orders safe writes
| LSN | Transaction | Record | Durable before crash? |
|---|---|---|---|
| 900 | T1 | begin | yes |
| 901 | T1 | set stock 20 → 13 | yes |
| 902 | T1 | commit | yes |
| 903 | T2 | begin | yes |
| 904 | T2 | set stock 13 → 6 | yes |
| 905 | T2 | commit | no |
The stable log prefix ending at 904 supports T1’s commit. A crash before 905 becomes durable cannot honestly expose T2 as committed. A data page may contain an update before its transaction commits; recovery’s transaction status decides whether to preserve, undo, or hide it. Page LSNs make redo idempotent: if a page already reflects LSN 901 or later, reapplying 901 is unnecessary.
Prediction — a page beats its log
A page reflecting LSN 911 reaches storage, but the log is durable only through 910. Why is this STEAL-without-WAL failure mode an error even if the update happens to be correct?
HintExpandCollapse
Recovery needs a durable explanation for every durable page effect.
Worked solutionExpandCollapse
The durable page contains an effect whose redo/undo record may be absent. After a crash, recovery cannot determine whether the change belonged to a committed transaction, reconstruct an overwritten older value, or repeat the operation safely on another page. WAL forbids this ordering; correctness is not established by observing one lucky final value.
Redo repeats history; undo removes losers
Crash between log records; derive redo and undo
Move the crash point through a small write-ahead log. T1 changes A and commits; T2 changes B but never commits. Inspect which updates recovery may retain.
- 1. BEGIN T1
- 2. UPDATE A: 10 → 7
- 3. BEGIN T2
- 4. UPDATE B: 20 → 30
- 5. COMMIT T1
Possible page values at crash
A ∈ {10, 7}
B ∈ {20, 30}
A durable update log permits, but does not prove, its page write.
Winner / loser
T1: winner — ensure A = 7
T2: loser — roll back B if present
After recovery
A = 7
B = 20
Model scope: WAL forces each log record before its corresponding page write, but log durability does not reveal whether a page write happened before the crash. Log records are durable in the order shown; updates include before/after images; no torn pages or concurrent transactions appear. ARIES also performs analysis, tracks page LSNs, and repeats history before undo.
Possible page values at crash
A ∈ {10, 7}
B ∈ {20, 30}
A durable update log permits, but does not prove, its page write.
Winner / loser
T1: winner — ensure A = 7
T2: loser — roll back B if present
After recovery
A = 7
B = 20
Static state: T1’s commit record is durable, so recovery ensures its update is reflected if necessary. T2 has no commit record, so recovery rolls its update back if it reached a page.
Advance the durable prefix in the playground and state the committed state before revealing it. The model intentionally exposes one committed transaction and one unfinished transaction; it is a small reasoning model, not a complete implementation of checkpoints or compensation log records.
Worked trace — crash after page flush
T1 writes stock 20 → 13 at LSN 901 and commits at 902. The page flushed at LSN 901 before the crash. T2’s update at 904 may be present in the buffer, but its data-page effect has not reached durable storage; T2 has no durable commit. Recovery sees page LSN 901, skips redundant redo of 901, and starts from a state that includes T1 but not T2. If T2’s update had reached the page, recovery would undo or make it invisible because T2 is a loser.
ARIES makes undo repeatable
The three-phase sketch above becomes a real recovery protocol only when an interrupted undo can itself survive a crash. ARIES adds compensation log records (CLRs) and a per-transaction undo-next LSN so that recovery never repeats an undo it already performed.
Worked trace. T2 begins at LSN 903, updates stock at 904, and crashes without a durable commit. During undo, recovery appends CLR 906 for the reversal with undoNextLSN = 903 and applies the undo. WAL requires that CLR to be durable before its changed page is flushed. Suppose 906 is durable and recovery crashes again before finishing. On restart, redo can repeat 906 if its effect is missing from the page; undo resumes at 903 instead of undoing 904 again. If the CLR was not durable, recovery follows the durable log instead: WAL prevents the corresponding undo page effect from having been safely flushed first.
| Phase | Reads | Produces | Key invariant |
|---|---|---|---|
| Analysis | log from the last checkpoint | transaction table, dirty-page table | every loser is identified before any undo |
| Redo | log forward from the smallest dirty-page recLSN found during analysis | pages brought to their logged history | page LSN skips changes already present |
| Undo | losers’ log records backward | CLRs and restored pre-images | undoNextLSN advances exactly once per action |
Analysis starts from the checkpoint designated by the master record and reconstructs transaction and dirty-page state. A fuzzy checkpoint records active transactions and dirty pages while work continues; the dirty-page table can contain a recLSN older than the checkpoint, so redo can start earlier. The correctness claim depends on WAL and the recovery protocol, not merely checkpoint frequency.
Why undo needs its own log record
Transaction T3 has updates at LSN 20 and LSN 30. Recovery writes a durable CLR for undoing 30 with undoNextLSN = 20, then crashes. Evaluate which recovery principles apply:
Classify each option as correct or incorrect, or leave uncertain options unanswered (“I don't know”) and verify at any time.
Recovery objectives require distinct artifacts
| Artifact | Main question answered | Cannot replace |
|---|---|---|
| WAL / transaction log | how to recover changes after a base state | a base copy once retention is exhausted |
| full or incremental backup | what durable base can be restored | recent changes without logs or later backups |
| replica | how quickly can service continue after a node loss | an independently retained historical restore point |
| checksum / invariant test | did restored bytes and domain facts survive? | the data copy itself |
A stated recovery point objective (RPO) is acceptable data loss, measured in time or transactions. A recovery time objective (RTO) is acceptable restoration time. They are claims until a restore drill measures them. A backup file existing in object storage is not evidence that it restores, replays logs, starts, and passes business invariants.
Application — design a restore drill
The product claims RPO ≤ 5 minutes and RTO ≤ 60 minutes after a bad migration. State a drill that can disprove either number.
Worked solutionExpandCollapse
Capture a known workload and timestamps, take the documented base backup, retain the required WAL/archive interval, then introduce the bad migration. Restore into an isolated environment to a target time no more than five minutes before the declared incident boundary. Measure elapsed time through startup and validation, compare row checksums where appropriate, and test domain invariants such as tenant ownership and nonnegative stock. Failure to reach the time target disproves RPO; exceeding the measured recovery budget disproves RTO.
Corruption and accidental change need different responses
Transfer — select the first safe action
A replica is healthy but has already replayed an accidental tenant-wide deletion. A nightly backup exists and WAL is retained. What is the first safe recovery plan?
HintExpandCollapse
Do not promote the replica merely because it is available; it has the same logical damage.
Worked solutionExpandCollapse
Preserve the primary, replica, backup, and WAL evidence; restore the backup plus WAL to an isolated target before the deletion; verify the tenant scope and invariants; then choose a controlled logical export/import or a promotion plan with client fencing. The replica can reduce outage time in a node-loss case, but here it is not an independent recovery source.
Deep Practice
Solve these problems to test and consolidate your understanding. Complete worked solutions and diagnostic hints are available for each problem.
Exercise 4.4.1: ARIES Compensation Log Record and Idempotent Restart
An active transaction modifies page at LSN 100. During crash recovery, ARIES undo writes CLR 105 with undoNextLSN = null, updating page to LSN 105. Before recovery completes, a second power outage occurs. Verify how the subsequent recovery restart handles CLR 105 and whether page is undone again.
HintExpandCollapse
Check the repeating-history redo phase and CLR redo-only properties.
Worked solutionExpandCollapse
On the second restart, the analysis phase identifies CLR 105 in the log. During the repeating-history redo phase, if page ‘s page LSN on disk is less than 105, CLR 105 is reapplied to restore the page image. Because CLR 105 has undoNextLSN = null, the undo phase recognizes that all actions of are already fully reversed and does not attempt any further undo on page The process is strictly idempotent.
Exercise 4.4.2: Redo vs. Undo Duality in Write-Ahead Recovery
Explain why write-ahead logging recovery requires both a forward redo pass and a backward undo pass, and why neither pass can substitute for the other.
HintExpandCollapse
Contrast unwritten committed pages with written uncommitted pages.
Worked solutionExpandCollapse
The redo pass is necessary because a transaction may commit while its dirty modified data pages remain in the volatile buffer pool without reaching disk (No-Force policy); redo replays logged updates to bring disk pages up to the committed state. The undo pass is necessary because dirty pages from active or uncommitted transactions may have been flushed to disk to free buffer frames (Steal policy); undo rolls back those uncommitted modifications to preserve transaction atomicity.
Exercise 4.4.3: Falsifiable RPO/RTO Disaster Recovery Drill Design
An enterprise SLA specifies and Specify a rigorous disaster recovery test drill protocol that either experimentally proves compliance or produces a falsifying measurement.
HintExpandCollapse
Establish a baseline workload with known timestamps, simulate catastrophic primary failure, and time restoration.
Worked solutionExpandCollapse
- Baseline workload: Generate continuous synthetic transactions writing timestamped sequence rows.
- Simulated catastrophe: At timestamp ungracefully terminate the primary node (
kill -9) and sever network storage. - Restoration timing: At trigger restore automation using the latest base backup and WAL archives. Record elapsed wall-clock time until the recovered instance passes health checks and accepts read/write queries If the SLA fails RTO.
- Data loss audit: Query the maximum transaction timestamp present in the restored database. If the SLA fails RPO.
Exercise 4.4.4: Point-in-Time Recovery vs. Streaming Replication for Logical Corruption
A developer mistakenly executes DROP TABLE accounts; on production. Within 100 milliseconds, the command is replicated to the hot standby replica. Contrast why promoting the streaming replica fails to recover the data, and prescribe the exact point-in-time recovery (PITR) procedure.
HintExpandCollapse
Physical replication propagates logical drop commands immediately. PITR halts replay prior to the drop.
Worked solutionExpandCollapse
A physical streaming replica faithfully executes every WAL record emitted by the primary. The DROP TABLE command is immediately applied to the replica, destroying the table there as well.
Prescribed PITR procedure:
- Preserve the WAL archive and base backup immediately; do not overwrite existing standby state.
- Provision an isolated recovery server and restore the latest full physical base backup taken prior to the incident.
- Configure recovery settings with
recovery_target_timeset to 1 second before the exact execution timestamp of theDROP TABLEstatement (or target the transaction ID preceding the drop). - Replay WAL archives up to the target boundary, verify table integrity, and export the recovered
accountsrelation back to the production environment.
References & Further Reading
- Mohan, C., Haderle, D., Lindsay, B., Pirahesh, H., & Schwarz, P. (1992). ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging. ACM TODS, 17(1), 94–162. (Analysis/redo/undo, page LSNs, and compensation log records.)
- PostgreSQL: Write-Ahead Logging
- PostgreSQL: Continuous Archiving and Point-in-Time Recovery
- PostgreSQL: Backup and Restore
- CMU 15-445/645 Database Systems