During crash recovery, the engine may encounter a log record describing a page change that had already been written to the data file before the crash. How does it avoid applying that change a second time, and why would double application be harmful?

## The core mechanism: two matching counters The transaction log is a sequence of records, each stamped with a **log sequence number (LSN)** that increases monotonically. Each data page has a small header field, the **page LSN**, holding the LSN of the most recent log record whose effect has been applied to that page. The rule tying them together is the write-ahead ordering rule: before a dirty page may be written to the data file, the log must be durable up to that page's page LSN. As a consequence, when recovery looks at a page on disk, the page LSN is an exact, self-describing statement of how far that page has caught up with history. There is no separate bookkeeping to consult and no ambiguity, even though pages were written back in essentially arbitrary order. Redo therefore becomes a simple test per record: - If the record's LSN is less than or equal to the page LSN, the change is already there. Skip it. - Otherwise apply the change and set the page LSN to the record's LSN. ## Why skipping matters: log records are not naturally repeatable It is tempting to think replaying twice is harmless, since redo puts the database into a known state anyway. It is not, because of how changes are logged. Engines log **physiological** records: physical with respect to which page, logical with respect to what happens inside that page. Examples: *insert this tuple into page 7, taking any free slot*, *delete slot 3 on page 12 and compact the page*, *add 5 to the value at slot 2, column 4*, *split this index page, moving the upper half to a new page*. These describe operations, not final byte images, because full-page images would multiply log volume enormously. Operations like these are not idempotent. Replaying an insert twice creates two rows. Replaying an increment twice doubles the delta. Replaying a page compaction can shuffle slots that later records address by number, corrupting everything downstream. So exactly-once application per page is a correctness requirement, not an optimization, and the page LSN is what enforces it. A purely physical log (whole before and after images of each page) would indeed be idempotent, which is why some engines log full-page images occasionally, for example the first time a page is touched after a checkpoint, to protect against torn writes. But that is a special case layered on top, not the general rule. ## The cheap filters that come first Fetching a page from disk just to read its LSN is expensive, so redo applies cheaper tests in order: 1. **Is the page in the dirty page table?** If analysis did not list it, the page was clean at the crash-relevant moment, meaning all changes up to that point are already durable. Skip without any I/O. 2. **Is the record's LSN below the page's recLSN?** The recLSN is the first LSN that dirtied the page since it was last written. Anything older is on disk. Skip without I/O. 3. **Otherwise fetch the page and compare page LSN.** This is the authoritative test, needed because a page may have been written to disk after being dirtied, which the reconstructed dirty page table cannot know. Only the third test requires reading the page, so the vast majority of the log is dispatched cheaply. ## Idempotence buys restartability Recovery can itself be interrupted, which is not a rare theoretical case: a machine that just crashed from bad memory or a failing disk may well crash again during restart. Because each redo application is guarded by an LSN comparison, re-running redo from the same start point simply skips whatever the previous attempt completed. The process converges no matter how many times it is interrupted, and no separate progress file is needed. The undo side achieves the analogous property differently, through compensation log records that record reversals as they happen, so an interrupted rollback resumes rather than restarts. ## Related uses of the page LSN The same field does double duty elsewhere. A physical replica applying a shipped log stream uses the identical comparison to decide whether a record applies to its copy of the page. Backup and point-in-time restore use it when rolling a restored file forward. Torn-page detection often pairs it with a checksum or with full-page images written after a checkpoint. ## Summary One small header field per page turns an unordered, partially written set of data files into something recovery can repair deterministically. Compare LSNs, apply if behind, stamp, move on.