Using page LSN to decide redo

WHAT THIS TESTS The interviewer wants the exact mechanism behind safe, idempotent redo, the page-LSN comparison, which is the heart of write-ahead recovery.

A GOOD ANSWER COVERS Every data page carries a page-LSN field holding the LSN of the most recent log record whose change is reflected in that page's persisted contents. The WAL is an ordered sequence of records each tagged with a monotonically increasing LSN. During the redo phase of recovery, for a page that may have been dirty at crash time, the engine reads the page as it exists on disk, examines its page-LSN, and walks the relevant log records. For each log record describing a change to that page, it compares the record's LSN to the page-LSN. If the record's LSN is greater than the page-LSN, the change is not yet present on the persisted page, so it must be redone and the page-LSN advanced to that record's LSN. If the record's LSN is less than or equal to the page-LSN, the page already reflects it, perhaps because that page was flushed before the crash, so the record is skipped. This comparison makes redo idempotent: rerunning recovery after another crash reaches the same decisions and never applies a change twice.

COMMON WRONG ANSWERS Reapplying every log record regardless of the page's LSN, double-applying flushed changes. Comparing transaction IDs instead of LSNs. Assuming all dirty pages need full replay without per-page checking. Forgetting to advance the page-LSN after redo.

LIKELY FOLLOW-UPS Where is the page-LSN stored; why must redo be idempotent; how does the dirty page table narrow which pages to check; what is recLSN.

ONE CONCRETE EXAMPLE A dirty page on disk shows page-LSN 700. Redo encounters log records for that page at LSN 650 and 760. It skips 650 because 650 is not greater than 700, so the page already has it, and it applies 760, updating the page-LSN to 760. If recovery restarts, it again skips everything up to 760, guaranteeing no operation is applied twice.