Build1 publisher3 min readPublished
SQLite's WAL mode requires every process to run on the database's own host
SQLite's WAL mode coordinates readers through a shared-memory index file, so every process using the database must run on the host that stores it. Tuning mounts and timeouts on NFS or SMB can quiet the symptoms without making a multi-host setup safe, a dev.to analysis argues.
The Engineer · Build desk
Drafted by a language model from the sources cited here and checked against its claim ledger before publication. How we use AISend a correction
What happened
- WAL mode coordinates through three files: the main database, the append-only -wal log, and the -shm wal-index that also holds the locks.
- In the post's example, a WAL-mode application on a mounted volume passes its tests and survives weeks of restarts while only one host uses it.
- Once a second machine starts using the file, nothing fails at first, and then stale reads, intermittent SQLITE_BUSY errors and failed checkpoints appear.
- The analysis covers NFS, SMB, distributed filesystems and any volume that appears local to several hosts at once.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint Any shared-volume design that lets two hosts open one SQLite file in WAL mode falls outside what WAL supports, whatever the mount options or filesystem.
- exposure A database that passes routine health checks can still fail to open after an unlucky interruption, so green checks are not evidence that a shared setup is safe.
- cost Falling back to rollback mode gives up WAL's non-blocking reads while keeping the dependence on cross-host file locks and ordered fsync.
- decision Teams already sharing a file across hosts have to choose between a client/server database and SQLite behind a request API on the storage host.
SQLite's default rollback journal copies each original page into a journal file before changing the database. If a transaction is interrupted, it restores from those copies [8]. WAL runs the other way. It leaves the original pages in place and appends changed pages to a separate log [9]. A transaction commits when its commit record is appended, and the main file can stay unchanged for some time [9]. Readers keep reading the stable database file and see a snapshot while the writer appends [11]. The design is sound, and it is the reason WAL improves concurrency [11].
The cost is coordination state. A checkpoint copies committed pages from the log back into the main file. It runs automatically once the log reaches a configured size, historically 1,000 pages by default [10]. Readers also consult the wal-index, which lives beside the database in the -shm file [1]. According to the dev.to analysis, that index is why every process using a WAL database must run on the same host. The -shm file is part of the read path [1].
The post splits the multi-host problem into three contracts [5]. Processes must agree on who owns SQLite's locks. An fsync() must order earlier writes safely before later ones. Every process must see a coherent wal-index and its lock state [5]. A network filesystem can break any of the three, and WAL makes the third one unavoidable [5].
The trigger is a second machine using the file, not specifically a second writer [3]. The wal-index exists for readers [1]. A replica host that only runs SELECT queries against the same mount is therefore inside the problem.
The usual response is to inspect the mount, change cache settings, raise timeouts or turn WAL off, and sometimes the symptoms shrink [4]. Raising a timeout is a good way to see fewer SQLITE_BUSY errors and no way at all to make two hosts agree on a wal-index [5]. Of the symptoms the post lists, only intermittent SQLITE_BUSY errors are a waiting problem. Stale reads and failed checkpoints are not [3].
Turning WAL off moves the failure somewhere else. Rollback mode avoids the shared-memory index but still depends on reliable file locks and durable, ordered writes [6]. The author wrote that rollback mode "changes the failure shape rather than eliminating the risk" [6].
"Put the SQLite engine on the same host as the database file. If clients are remote, make the network boundary an application protocol, not a filesystem mount," the author wrote [12]. I think the rule fits any service where one team owns the data. A small API on the storage host keeps every SQLite process on the machine where the wal-index is coherent [1]. Remote clients then never open the file.
What to watch
- A reproducible multi-host test on NFS or SMB that counts stale reads and failed checkpoints would turn the post's list of symptoms into a measured failure rate.
- SQLite's own documentation on WAL and network filesystems is the primary record; any change there would take precedence over the secondhand account of that guidance used here.