Day 7: Crash the Database Mid-Transaction & Witness Write-Ahead Log Recovery — Then Restore from a Full Backup.

Day 7: Crash the Database Mid-Transaction & Witness Write-Ahead Log Recovery

In Day 6, we designed concurrent isolation strategies to prevent lost updates when customers raced to buy the last laptop in our retail catalog. We learned that database engines use locks and transaction isolation levels to keep concurrent memory operations from corrupting our state.

But what happens when the physical world intrudes? What happens if the power cord is pulled, or the operating system kernel panics, or we run kill -9 on our database process right in the middle of a transaction?

In this lesson, we will explore the core mechanism that prevents data corruption and guarantees durability: the Write-Ahead Log (WAL). We will build a mini-transactional storage engine, deliberately crash it mid-transaction, witness its crash-recovery phase (REDO and UNDO), and perform Point-in-Time Recovery (PITR) using a full backup and WAL replay.

The Physical Reality of Disk Writes: Why write() is a Lie

To understand why databases need a WAL, we must first understand how operating systems write data to physical disks.

When an application calls write() on a file descriptor, the operating system does not immediately spin up a disk platter or flash a block of SSD gates. Doing so for every single byte would destroy performance. Instead, the OS copies your data into a kernel buffer cache. The OS then decides when to flush this cache to physical media—often seconds later.

If your database updates a product's stock count in its main database file and the power cuts before the OS flushes that memory page to disk, your change is lost. Worse, if the OS was halfway through writing a 4KB database page when the power died, you end up with a torn page—a corrupted block of bytes that is unreadable by the database engine.

To solve this, database engines enforce the Write-Ahead Logging Invariant:

No database page (containing table rows or index nodes) may be written to non-volatile storage until the log records describing the modification have been safely flushed to disk.

The log is an append-only file. Appending to a file is incredibly fast because it is a sequential write, which avoids the expensive disk head seeks (on HDDs) or block-erasure cycles (on SSDs) required by random updates to a database file.

The Recovery Engine: ARIES-style REDO and UNDO

When our database boots back up after a crash, it inspects the WAL. The recovery process follows a simplified version of the classic ARIES (Algorithms for Recovery and Isolation Exploiting Semantics) protocol. It runs in two distinct phases:

1. The REDO Phase (Repeating History)

The recovery engine scans the WAL forward from the last known checkpoint. It reapplies every committed transaction it finds. If a transaction wrote a COMMIT record to the WAL, we must guarantee its changes are in the database, even if those changes never made it to the main database file before the crash.

2. The UNDO Phase (Cleaning Up the Mess)