WAL — durability without rewriting the file every commit — Build a B-tree storage engine (SQLite-style)

Build a B-tree storage engine (SQLite-style) (11 scenes)

Scene 08 · WAL — durability without rewriting the file every commit

Commits append page-images to the WAL and fsync; the main DB file is left alone until checkpoint — fast, and readers don't block.

Previously

The page cache made reads RAM-fast and let writes happen in cache. But a crash erases RAM — the engine still owes you a story for how a commit becomes durable without rewriting random pages of the main file every time.

Scene 08

WAL — durability without rewriting the file every commit

Watch

Diagram

Three stacked files: users.db (the page strip from earlier scenes), users.db-wal (initially empty; commits append page-image frames here), and users.db-shm (a small shared-memory card that lets readers find the latest frame for a given page). The right-hand panel shows the synchronous mode — when fsync actually runs.

Sources

Watch one COMMIT walk the four steps. Mutate the page in cache, append a frame to the WAL, fsync the WAL, then return. The DB file is not touched.

Implementation

Pager.commitUnderWAL

the four-step commit — what the diagram is animating

1def commit(txn):
2    for page in txn.dirty_pages:        # step 1: in cache
3        wal.append(Frame(page.id, page.bytes))
4    wal.append(Frame(commit=True))      # commit marker
5    if synchronous in (FULL, EXTRA):
6        fsync(wal)                       # step 3: durable
7    shm.update_index(txn.dirty_pages)   # readers see latest
8    return OK                            # step 4
9    # NOTE: db file is NOT touched here

Reader.readPage

why a reader doesn't block on the writer

1def read_page(page_id, snapshot_end_mark):
2    # check shm for latest frame ≤ end_mark
3    frame = shm.lookup(page_id, snapshot_end_mark)
4    if frame is not None:
5        return wal.read(frame)           # served from WAL
6    return db_file.read(page_id)         # fall back to main file

Pager.commitUnderRollbackJournal

the older default — for contrast (one sentence in lessons)

1def commit_rollback(txn):
2    journal.write(originals(txn.dirty_pages))
3    fsync(journal)                       # 1st fsync
4    db_file.write(txn.dirty_pages)       # rewrite the FILE
5    fsync(db_file)                       # 2nd fsync
6    delete(journal)                      # commit point
7    # readers were blocked under EXCLUSIVE this whole time

PreviousThe page cache — RAM is 1000x faster than disk NextCheckpoint — and the 20 GB WAL