How SQLite Scales Read Concurrency

Summary

SQLite’s write-ahead log (WAL) is presented as the mechanism that overturns the common claim that SQLite is inherently single-user or unable to scale reads. Unlike the rollback journal, which blocks readers and writers from operating at the same time, WAL leaves original database pages in place and appends changed pages to a -wal file, allowing point-in-time snapshots to coexist with ongoing writes. SQLite tracks those entries in a shared-memory (SHM) index: 32KB blocks contain page-number lists and 8,192-slot hash maps, with slot positions calculated as (pgno * 383)%8192 while transaction position determines visibility. The post concludes that WAL mode benefits most applications, while noting that rollback journaling can remain preferable for edge cases such as multi-database transactions.

Takeaways

1. 1

   In the example, a WAL entry consists of a 24-byte header followed by 4,096 bytes of page data. A transaction is not considered committed until its final page is written, with the database-size field identifying that final entry.

2. 2

   The WAL header contains a format marker, version, page size, checkpoint sequence, salt, and checksum. The salt lets SQLite distinguish current frames from stale data when the WAL restarts without deleting older contents.

3. 3

   The SHM index combines page-number lists with hash maps. SQLite uses (pgno * 383)%8192 to select a hash position, follows collision chains, and ignores entries written after a read transaction began.