CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 2 of 5 · 1 min read

Why the log must be durable before the data page

Without the log recorded first, a crash before the data page is flushed would lose the change completely.

Think of a construction crew that always files the permit paperwork for a change before actually making the change on site. Even if work stops unexpectedly, there is still a record proving exactly what was intended.

In plain words: as covered in Level 4, a write changes an in-memory page first, with the corresponding log entry written durably before, or at the same time as, that in-memory change, while the actual data page on disk might not be flushed until much later. If a crash happens after the in-memory change but before the data page is flushed, the data file on disk still holds the old value. Without the durable log entry describing what should have happened, that change would be completely and permanently lost once the system crashes, with no way to even know it was ever supposed to happen.

A worked example. A price update that changed an in-memory page and was logged, but whose data page was never flushed before a crash, can still be correctly recovered, since the durable log entry records exactly what the new price was supposed to be.

The key lines. No new SQL; this is the same write-ahead logging mechanism introduced in Level 4, now examined specifically for its role in recovery.

Write-ahead logging (recovery role)

Write-ahead logging, in its recovery role, guarantees that a durable record of every change exists before that change is necessarily reflected on the data page, so a crash before the page is flushed never loses the change entirely.

Example: filing a construction permit before making a change on site, so a record survives even if work stops unexpectedly, mirrors write-ahead logging's recovery role.