SQLite WAL Mode Can Lock Short-Lived Readers

Summary

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 lockedThe usual advice goes something like this:Use WAL mode,have a non-zero busy timeout,use BEGIN IMMEDIATE for transactions that will write.And this advice is correct for the typical workload of a web application that has long-lived database connections.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 0 and we started seeing rare-but-constant database is locked errors.Reading implies writing in WAL modeThe 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 c...