What is Write-Ahead Logging, and why must the log be written before the data page?
Interview preparation resource from Gate Smashers.
Write-Ahead Logging (WAL) requires the relevant log record to be made durable before the corresponding modified data page is written to disk. This guarantees the DBMS has the recovery information available if a crash happens and ensures commit durability: the log describing a change must be safely stored before the dirty page may reach durable storage.
Definition
Write-Ahead Logging (WAL) requires the relevant log record to be made durable before the corresponding modified data page is written to disk. Database pages are usually modified in memory first; WAL mandates that the log describing those changes be stored durably before any changed (dirty) page is allowed to reach durable storage.
Why the log must be written first
If the system crashes after a data page has been written but before the log exists, the DBMS could see a changed page without having enough information to understand, undo, or redo that change. Writing the log first guarantees the DBMS has the recovery information available to correctly perform REDO and/or UNDO after a crash.
How WAL works (steps)
WAL lets the DBMS defer flushing dirty pages to disk but enforces the ordering that the log record describing a change is made durable first.
- Step 1: Transaction changes a page in memory.
- Step 2: Corresponding log record is written and made durable first.
- Step 3: The data page may be flushed later.
- Step 4: After a crash, the durable log can be used for REDO and/or UNDO as required.
Commit durability and recovery
Before the DBMS reports a transaction as committed, the log records required to recover that commit must be durable. WAL is a foundation of reliable crash recovery because it gives the DBMS a durable history of changes before those changes can appear permanently in database pages.