I was getting occasional Error: database is locked messages from a Datasette instance that was running against a bunch of different SQLite files that were updated by cron scripts (my personal Dogsheep).
I had read about SQLite's WAL mode but never fully understood how it works. I asked some clarifying questions on the SQLite forum and learned that WAL is actually a property of the database file itself, not of the connection to that database.
This means that turning on WAL is a thing you can do directly to a database file!
Here's the incantation:
sqlite3 github.db 'PRAGMA journal_mode=WAL;'
I ran this against all of the *.db files in a directory like this:
ls *.db | while read filename;
do sqlite3 $filename 'PRAGMA journal_mode=WAL;';
done;
The first time I ran this it worked on all but one file, which showed the Error: database is locked message - so I kept trying against that file until it worked.
After running this each .db file has an accompanying .db-shm and .db-wal file. So far I've not seen the "database is locked" message, so I think it had the desired effect.
If you want to turn WAL mode off and go back to the SQLite default, the unintuitive way to do that is:
PRAGMA journal_mode=delete;
I added a command to sqlite-utils 2.15 that does this:
sqlite-utils enable-wal *.db
The disable-wal command disables it again.
Ben Johnson wrote about how WAL mode internals work in great detail in How SQLite Scales Read Concurrency .
In a Hacker News comment, Ben points out:
The other odd thing is that the journal_mode is only persistent for WAL, I believe. The DELETE, TRUNCATE, & PERSIST modes are per-connection. It makes sense though since those 3 modes deal with the rollback journal and are compatible with each other while the WAL is totally separate. https://www.sqlite.org/pragma.html#pragma_journal_mode
I made some extensive notes on the performance impact of WAL mode in Benchmarking SQLite in Django.
Created 2020-08-09T20:23:23-07:00, updated 2023-10-27T13:53:08-07:00 · History · Edit