# Logging, Checkpoints and Crash Recovery — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/c-recovery

> Explain write-ahead logging, checkpoints and undo/redo recovery.

## Surviving crashes

Databases cache pages in memory and write them to disk later, so a crash can leave the disk with some committed changes missing and some uncommitted changes present. **Write-ahead logging (WAL)** solves this: before a changed data page is written to disk, the **log records** describing the change must be written to stable storage, and a transaction is committed only when its commit record is in the log. Each log record holds the transaction ID, the item, and often the **before image** (for undo) and **after image** (for redo). After a crash, recovery **redoes** the changes of committed transactions that may not have reached the data files and **undoes** the changes of transactions that had not committed. **Checkpoints** periodically flush dirty pages and record a checkpoint in the log so recovery need not scan from the beginning. The widely used **ARIES** algorithm performs three passes: **analysis**, **redo** (repeating history) and **undo**. Recovery handles crashes; protection against disk loss, mistakes and disasters still needs **backups**, **point-in-time recovery** from archived logs, and **replication**.

## Recovering after a crash, by hand

A log excerpt and the actions recovery takes.

```text
log (oldest first)
  <T1 start>
  <T1, A, before=500, after=400>
  <T2 start>
  <T2, B, before=200, after=300>
  <CHECKPOINT  active: T1, T2>
  <T1 commit>
  <T3 start>
  <T3, C, before=50, after=80>
  -- crash --

recovery
  redo: T1 committed  -> ensure A = 400 on disk
  undo: T2 and T3 never committed -> restore B = 200, C = 50
  write abort records for T2 and T3
```

## Commit means the log is safe

Durability comes from flushing the log, not the data pages. That is why log disk speed and settings like `fsync` or `synchronous_commit` matter so much for write performance and safety.

**Quiz:** In write-ahead logging, what must reach stable storage before a modified data page is written?

- [x] The log records describing that change
- [ ] The entire database backup
- [ ] The query plan
- [ ] Nothing; pages can be written at any time

*Answer:* The log records describing that change. WAL requires log records to be durable before the corresponding data changes are written.