Locked SQLite database errors aren’t just for writers.
A lot of online ink has been spilled over the infamous SQLite error:
Cannot prepare SQLite statement: database is locked
The usual advice goes something like this:
- Use WAL mode,
- have a non-zero busy timeout,
- use
BEGIN IMMEDIATEfor transactions that will write.
And this advice is correct for the typical workload of a web application that has long-lived database connections1.
However, Cthulhu has cursed me with an atypical workload once again. We write extremely rarely to the database (in some cases less than once per month) but read tens or hundreds of times per second without connection pooling, because they’re independent processes. In other words: we open the database in read-only mode (SQLITE_OPEN_READONLY), run one SELECT, close the database.
But since we didn’t want writers to block readers – as rare as they may be – we took the ostensibly safe route and put our databases into WAL mode anyway. Turns out, with our MO that is a problem and we learned about it the hard way, because the SQLite C library has a default connection timeout of 02 and we started seeing rare-but-constant database is locked errors.
Reading implies writing in WAL mode
The problem is WAL’s connection lifecycle: connections coordinate through the -shm file, and opening or closing an empty WAL database can briefly require exclusive locks. A new reader that arrives during one of those windows can receive SQLITE_BUSY, even when no application data is being written – nor has been written in days:
$ ls -la /vmws/config/config.db*
-rw-r----- 1 root root 294912 Jul 21 09:49 /vmws/config/config.db
-rw-r----- 1 root root 32768 Jul 24 18:26 /vmws/config/config.db-shm
-rw-r----- 1 root root 0 Jul 24 18:26 /vmws/config/config.db-wal
Compare the modification times: the ls was run on July 24th, at 6:26 p.m. The database itself hasn’t been touched since July 21st and yet the -wal and -shm have been just written by our read-only readers.
A surprising amount of write churn for an effectively read-only system!
If the default busy timeout weren’t 0, we would’ve never learned about this – yay things breaking loudly!
Since writes are so rare, we decided to switch our databases to DELETE mode and haven’t seen the error ever since. As always, it’s trade-offs all the way down.
Reproducer
If you want to reproduce the error, here’s a standard-library-only Python script.
It demonstrates three scenarios by running many processes in parallel with a read-only open-query-close loop each:
- A has no busy timeout, database is in WAL mode.
- B adds a busy timeout of 1 second, still in WAL mode.
- C uses no busy timeout, and no WAL mode.
On my 2023 MacBook Pro with Python 3.14, I get reliably between 1 and 10 failures in the A scenario and 0 in B and C.
#!/usr/bin/env -S uv run --script
"""
Concurrently read from a SQLite database.
"""
import argparse
import multiprocessing as mp
import pathlib
import sqlite3
import tempfile
def worker(db, rounds, barrier, timeout, q):
locked = 0
for _ in range(rounds):
barrier.wait() # everyone opens at the same instant
try:
con = sqlite3.connect(
f"{db.as_uri()}?mode=ro", uri=True, timeout=timeout
)
con.execute("SELECT v FROM config").fetchone()
con.close()
except sqlite3.OperationalError as e:
if "locked" not in str(e):
raise
locked += 1
q.put(locked)
def scenario(name, db, journal_mode, timeout, workers, rounds):
con = sqlite3.connect(db)
con.execute(f"PRAGMA journal_mode={journal_mode}")
con.execute("CREATE TABLE IF NOT EXISTS config(v)")
con.execute("INSERT INTO config VALUES (42)")
con.commit()
con.close()
barrier, q = mp.Barrier(workers), mp.Queue()
procs = [
mp.Process(target=worker, args=(db, rounds, barrier, timeout, q))
for _ in range(workers)
]
[p.start() for p in procs]
[p.join() for p in procs]
num_errs = sum(q.get() for _ in procs)
print(f"{name:<40} {num_errs:>4} / {workers * rounds} locked")
if __name__ == "__main__":
ap = argparse.ArgumentParser(description=__doc__.splitlines()[0])
ap.add_argument(
"-w",
"--workers",
type=int,
default=64,
help="concurrent short-lived processes (default: 64)",
)
ap.add_argument(
"-r",
"--rounds",
type=int,
default=100,
help="open/select/close cycles per worker (default: 100)",
)
args = ap.parse_args()
with tempfile.TemporaryDirectory() as tmpdir:
db = pathlib.Path(tmpdir, "config.db")
for name, mode, timeout in [
("A) WAL, no busy timeout (the bug)", "WAL", 0.0),
("B) WAL, 1s busy timeout (fix #1)", "WAL", 1.0),
("C) DELETE, no busy timeout (fix #2)", "DELETE", 0.0),
]:
scenario(name, db, mode, timeout, args.workers, args.rounds)
Typical output:
A) WAL, no busy timeout (the bug) 7 / 6400 locked
B) WAL, 1s busy timeout (fix #1) 0 / 6400 locked
C) DELETE, no busy timeout (fix #2) 0 / 6400 locked
Except that it seems to be nontrivial to use
BEGIN IMMEDIATEin SQLAlchemy. ↩︎Unlike pretty much any high-level wrapper. For example, Python’s
sqlite3standard library module comes with a default busy timeout of 5 seconds. ↩︎
