SQLite's WAL Mode Can Lock Out Short-Lived Readers

The advice to “turn on WAL, set a non-zero busy timeout, and wrap your write transactions in BEGIN IMMEDIATE” gets repeated so often that it sounds like gospel. Developer Hynek Schlawack offers a reminder: this recipe is fine for a typical web application with long-lived connections — but not for every workload.

His case turned out to be atypical. The database is written to exceptionally rarely — sometimes less than once a month — yet it is read dozens and hundreds of times per second. What's more, the readers are independent processes with no connection pool: open the database in SQLITE_OPEN_READONLY mode, run a single SELECT, close. To make sure the rare writers wouldn't block the readers, the author put the databases into WAL mode just in case — and got rare but persistent database is locked errors.

Reading that writes

The root of the problem lies in how WAL is built. Connections coordinate through a -shm file, and opening or closing an empty WAL database may, for a brief moment, require an exclusive lock. A new reader that lands in that window gets SQLITE_BUSY — even if nothing is writing to the database right now and nothing has written to it for days.

A vivid proof is the files' modification times. The database itself hadn't changed since July 21, yet its companions -wal and -shm were rewritten on the 24^th — by those very “read-only” processes. For a system that essentially writes nothing, that's a surprising amount of writing.

The error only got noticed at all because the default timeout in SQLite's C library is zero. High-level wrappers usually set their own value: for example, the sqlite3 module in Python's standard library waits five seconds by default. The author is sarcastically pleased: it broke loudly — that's already a win.

Trade-offs all the way down

Since there's almost no writing in his scenario, Schlawack moved the databases back to the classic DELETE mode — and the error vanished. A non-zero busy timeout also solves the problem, but with a caveat: using BEGIN IMMEDIATE correctly in SQLAlchemy turned out to be non-trivial.

The note comes with a reproducible script written in pure Python standard library. It spawns a swarm of parallel processes, each looping “open — SELECT — close,” and compares three scenarios: WAL with no timeout, WAL with a one-second timeout, and DELETE mode with no timeout. On a 2023 MacBook Pro running Python 3.14, the first variant reliably produces between one and ten failures, while the other two produce zero.

The moral is simple and familiar: there are no universal settings, and even a “safe by default” WAL should be chosen for a specific workload profile.

Source: Lobsters