Surviving the power cut

Durability says a committed transaction survives a crash. The disk is slow and memory is not, so the database works in memory. Both cannot be true by accident.

Write ahead logging

The rule is in the name: write the log before the data.

1. transaction changes a row in memory
2. the change is appended to the log on disk
3. the log entry is flushed, and only now does COMMIT return
4. the actual data page is written to disk later, whenever convenient

The log is appended sequentially, which a disk does far faster than scattered writes across a large file. So commit waits for one sequential write rather than several random ones.

What happens after a crash

On restart the database reads the log and reconciles:

• committed in the log, not yet in the data file: redo it
• in the data file, never committed: undo it

This is why a crash mid transaction leaves no half transfer. The money left one account and the log knows whether the other side committed.

Checkpoints

The log cannot be replayed from the beginning of time. Periodically the database flushes dirty pages and records a checkpoint, meaning everything before this point is safely in the data files. Recovery starts there.

Checkpointing too rarely makes recovery slow. Too often makes normal operation slow. It is a tuning knob with a real trade behind it.

Backups are a different thing

The log does nothing for the third row. A committed DELETE with no WHERE is durable, which is exactly the problem. Recovering from it needs a backup plus the log replayed to just before the mistake, which is what point in time recovery means.

And a backup nobody has restored is not a backup. The first restore should not happen during the incident.