Logging, Checkpoints and Crash 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.

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.

त्वरित जाँच: In write-ahead logging, what must reach stable storage before a modified data page is written?

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