Micrologics Logo
Back to Blog
App DevelopmentPublished on August 12, 2026

When Recovery Corrupts: Deconstructing the 16-Year-Old SQLite WAL-Reset Bug

A deep dive into how subtle race conditions in SQLite's Write-Ahead Logging protocol can lead to silent database corruption during fault recovery. Learn the low-level mechanics of shared-memory locks, WAL index salting, and edge-case crash semantics.

Introduction: The Illusion of Atomic Storage Invariance

SQLite is widely considered one of the most thoroughly tested software artifacts in existence. With millions of automated test cases, 100% branch coverage, and a pervasive deployment footprint spanning every modern browser, operating system, and embedded runtime, developers default to assuming its ACID guarantees are absolute. However, recent real-world investigative engineering—highlighted by Tailscale’s post-mortem on a long-standing database corruption bug—reminds us that distributed systems and concurrent low-level OS primitives can still violate assumptions made deep inside storage engines.

At the heart of the issue lies SQLite’s Write-Ahead Logging (WAL) reset mechanism: an optimization designed to reclaim disk space and recycle log frames without forcing full file truncations. Under precise race conditions involving process terminations, shared memory re-initialization, and 16-year-old state machine logic, recovery routines intended to restore sanity can instead silently corrupt data pages. In this deep dive, we will deconstruct the low-level architecture of SQLite's WAL mode, trace how lock states and generation counters interact, and analyze the systemic edge cases that cause fault recovery to turn destructive.

Anatomy of SQLite WAL Architecture: SHM, Locks, and Frames

To understand how a database log reset can corrupt storage, one must first master the memory-mapped coordination primitives that enable high-concurrency reads and writes in SQLite.

Traditional SQLite operates using a rollback journal. Before modifying a page in the primary .db file, the engine copies the original un-modified page into a .db-journal file. While simple, this creates a major concurrency bottleneck: readers block writers, and writers block readers.

Introduced in version 3.7.0, Write-Ahead Logging fundamentally alters this layout:

  1. The Database File (.db): Contains immutable state up to the last successful checkpoint.
  2. The Write-Ahead Log (.db-wal): An append-only log file containing updated database pages written sequentially.
  3. The Shared Memory Index (.db-shm): A memory-mapped shared file used as a volatile index to quickly map page numbers in the main database to their latest offset frames inside the .db-wal file.
+--------------------+     +------------------------+     +------------------------+
|   Main DB File     |     |    WAL Index (SHM)     |     |     WAL Log File       |
|   (app.db)         |     |    (app.db-shm)        |     |     (app.db-wal)       |
| +----------------+ |     | +--------------------+ |     | +--------------------+ |
| | Page 1 (v1.0)  | |     | | Pg 1 -> Frame 2    | |     | | Header (Salt1/2)   | |
| | Page 2 (v1.0)  | |     | | Pg 3 -> Frame 1    | |     | | Frame 1 (Pg 3, v1.1)| |
| +----------------+ |     | +--------------------+ |     | | Frame 2 (Pg 1, v1.2)| |
+--------------------+     +------------------------+     +------------------------+

The Role of the Shared Memory (.db-shm) File

When a reader requests Page 1, scanning the entire .db-wal file sequentially would yield $O(N)$ lookup times. To maintain $O(1)$ read performance, SQLite uses the volatile .db-shm index. The shared memory region contains a hash table that maps Page Number -> WAL Frame Offset.

Because multiple processes on the same system map this .db-shm file directly into their virtual memory spaces, locking must be coordinated via inter-process primitives—specifically POSIX advisory locks (fcntl) or system V semaphores depending on the host OS. SQLite splits the shared-memory byte array into distinct byte ranges corresponding to explicit operational locks: WAL_WRITE_LOCK, WAL_CKPT_LOCK, WAL_RECOVER_LOCK, and multiple WAL_READ_LOCK(i) slots.

The Log Truncation & Reset Optimization

As transactions commit, the .db-wal file grows indefinitely unless dirty frames are periodically copied back to the main .db file. This process is called checkpointing.

When a checkpoint completes and no active transactions remain on older reader snapshots, SQLite executes a WAL Reset. Re-allocating or truncating large files on file systems introduces expensive I/O operations and metadata lock contention. To avoid physically deleting and recreating .db-wal on disk, SQLite optimizes this process by resetting the internal frame counter to zero and writing a fresh 32-byte header to the start of the WAL file.

Salting the WAL Header

To ensure that stale index caches in other processes do not misidentify old frames written beyond the current reset point as new data, SQLite implements a 64-bit salt value split into two 32-bit integers (salt-1 and salt-2) stored directly in the WAL header.

struct WalHashHdr {
  u32 iVersion;           /* Format version number */
  u32 iChange;            /* Counter incremented on every change */
  u8 isInit;              /* True if initialized */
  u8 bigEndCksum;         /* True if checksum is big-endian */
  u16 pgsz;               /* Database page size */
  u32 mxFrame;            /* Total valid frames in WAL */
  u32 nPage;              /* Database size in pages */
  u32 aSalt[2];           /* Salt values for current log iteration */
  u32 aCksum[2];          /* Checksum over header fields */
};

Every time a transaction appends a page frame to the WAL, the checksum calculation for that frame factors in the active 64-bit salt pair. During a read operation:

  1. The reader inspects .db-shm to find the WAL frame offset.
  2. The reader checks whether the frame's salt matches the active header salt.
  3. If salts match, the frame is valid. If salts differ, the frame is ignored as stale leftover data from a pre-reset generation.

The Bug Mechanics: How Fault Recovery Corrupts Memory

The zero-day behavior uncovered in SQLite's WAL reset logic centers on how crash recovery constructs the .db-shm index when encountering a improperly reset WAL state combined with interrupted state transitions.

The Vulnerable Execution Sequence

  1. State 1: Full WAL Checkpoint: Process A completes a full checkpoint of the WAL file. All valid frames are written back to app.db. Process A now attempts to reset the WAL log.
  2. State 2: Partial WAL Header Write & Crash: Process A generates a new salt pair $(S_{new1}, S_{new2})$, updates the 32-byte header at offset 0 of app.db-wal, but crashes or gets terminated mid-operation before it can clear the contents of the .db-shm index or finalize file synchronization (fsync).
  3. State 3: Uncoordinated Reader Recovery: Process B connects to the database. Finding the .db-shm header dirty or missing, Process B acquires WAL_RECOVER_LOCK and attempts to reconstruct the shared-memory index by scanning the physical app.db-wal file from offset 0 to the end of the file.
  4. State 4: The Salt Verification Bypass: Process B reads the WAL header containing the new salt pair $(S_{new1}, S_{new2})$. It iterates over frames sequentially:
    • Frame 1 was written after the crash using the new salt pair? No, Frame 1 on disk is actually an old frame from the previous generation, holding salt $(S_{old1}, S_{old2})$.
    • The Flaw: Under specific conditions, if the salt generation counter wrapped or collided, or if the WAL recovery code failed to properly reset the frame validation salt state mid-scan, Process B miscalculates frame validity.
    • Consequently, Process B populates .db-shm mapping Page $X$ to Frame $Y$, even though Frame $Y$ contains stale un-checkpointed data or invalid page offsets.
  Timeline of Corruption State Machine:
  
  Process A                              Process B
  ------------                           ------------
  [Writes New WAL Header (Salt = S2)]
  [Crashes before resetting SHM]
  ---------------------------> X (DEAD)
                                         [Connects to DB]
                                         [Acquires WAL_RECOVER_LOCK]
                                         [Scans WAL with Header Salt = S2]
                                         [Fails to reject Frame with Salt = S1]
                                         [Maps Stale Frame into SHM]
                                         [Reads Stale/Corrupted Pages]

Because Process B incorrectly registers stale WAL frames as modern valid data inside .db-shm, any subsequent read request returns invalid, out-of-date, or half-overwritten memory pages directly into the application process space. Even worse, if Process B then performs a write, it appends data on top of a corrupted index graph, causing permanent corruption inside the actual primary .db underlying storage on the next checkpoint!

Systems Engineering Lessons: Edge-Case Semantics in Shared Memory

This 16-year-old bug offers profound insights for engineers architecting local storage engines, sidecars, and embedded databases.

1. File Locks Are Not Process-Safe Guardrails

In POSIX environments, fcntl locks are associated with an (inode, process) tuple. If a process opens a file twice, closing either file descriptor releases all POSIX locks on that file across the entire process. SQLite manages this via sophisticated lock abstraction layers, but inter-thread signal handling, abrupt process kills (SIGKILL), or system container teardowns can leave filesystem advisory locks cleared while shared memory buffers linger in host OS page caches.

2. Recovery Logic Must Treat Metadata as Untrusted

A recovery subsystem's sole purpose is to rebuild consistent state from inconsistent crash artifacts. The core vulnerability stemmed from recovery routines implicitly trusting metadata parameters (like the salt pair read from the log header) before validating the complete sequential continuity of the downstream frames.

When writing high-reliability logs:

  • Always perform two-phase checksum verification.
  • Maintain monotonic epoch sequence values rather than relying strictly on randomized or salt-based validation primitives.
  • Reject logs where frame salt transitions don't strictly align with atomic commit boundaries.

3. Defensive Configuration Flags for High-Reliability Deployments

For enterprise systems relying heavily on SQLite (e.g., embedded control planes, local edge caches, network proxies), engineers can apply explicit pragmas to reduce exposure to non-deterministic recovery paths:

-- Force full synchronous disk flushes on WAL frames
PRAGMA synchronous = EXTRA;

-- Prevent log truncation/resets from re-using active file mappings
PRAGMA journal_mode = WAL;

-- Execute an explicit integrity check upon opening recovery handles
PRAGMA quick_check;

Using PRAGMA synchronous = EXTRA forces SQLite to execute a physical directory fsync after resetting or truncating the WAL log, guaranteeing that the file system metadata reflects the log boundaries before any subsequent process attempts recovery.

Conclusion: The Perpetual Challenge of Storage Determinism

The resolution of SQLite’s decades-old WAL recovery bug proves that software verification is never truly complete. As modern systems push low-level storage engines into cloud-native microservices, containerized runtimes, and high-density ARM edge nodes, non-deterministic system calls and crash recovery timing will continuously stress system invariants.

Robust systems engineering demands a mindset of systematic failure analysis: assuming every header can be partially written, every process can be killed between instructions, and recovery code must be even more rigorously verified than the steady-state code path it repairs.

#SQLite#Systems Programming#Database Architecture#Concurrency#Low Level Engineering