Yeah I read the OP and my first instinct was that this is SQLITE_BUSY. I've been collecting posts about that here: https://simonwillison.net/tags/sqlite-busy/

I've always tried to avoid situations that could lead to SQLITE_BUSY. SQLITE_BUSY is an architecture smell. For standard SQLite in WAL, I usually structure an app with a read "connection" pool, and a single-entry write connection pool. Making the application aware of who _actually_ holds the write lock gives you the ability to proactively design access patterns, not try to react in the moment, and to get observability into lock contention, etc.

Also worth mentioning - it happens more often when you set journal_mode=WAL, which is not a default.

The default is DELETE mode, where the rollback journal is deleted at the conclusion of each transaction. What's more - in this mode (not-WAL), readers can coexist, but they do block the writer (which is always one) and the writer block readers - concurrency is highly limited.

In WAL mode - which pretty much always you should set - there's also at most one writer, but writer can coexist with readers.

Note that busy_timeout is not applicable to SQLite in this case (the SQLITE_BUSY issued immediately, no wait in this case).

Also this is because WAL mode (and I believe only for WAL mode, since there is really no concurrent reads in the other mode).

The reason is because pages in WAL mode appended to a single log file. Hence, if you read something inside a BEGIN transaction, later wants to mutate something else, there could be another page already appended and potentially interfere with the strict serializable guarantee for WAL mode. Hence, SQLite has to fail at the point of lock upgrade.

Immediate mode solves this problem because at BEGIN time (or more correctly, at the time of first read in that transaction), a write lock is acquired hence no page can be appended between read -> write, unlike in the deferred mode.

Indeed. Everyone who uses sqlite will get burnt by this one day and spend a lot of time chasing down errant write-upgraded transactions that cling on for a little bit longer than intended.

Yes, these are both important points. I didn't see any mention of SQLITE_BUSY in the blog post and wonder if that was never configured. Something that people miss quite often.

In an Oracle database, there is only one process that is allowed to write to tablespace datafiles, the DBWR (or its slaves). Running transactions can write to ram buffers and the redo logs only.

A similar design for SQLite would design for only one writer, with all other processes passing their SQL to it.

Wouldn't that "fix" make the problem worse on the whole, by making transactions hold onto write locks longer than necessary? (Not trying to disagree, just curious about potential downsides.)

Thats the best explanation I’ve seen of this issue.

However, it screams of a broken implementation.

Imagine if Linux PAM logins randomly failed if someone else was concurrently changing their password or vice versa.

In no other application would random failures due to concurrency be tolerated.

SQLite is broken by design; the world shouldn’t give them a free pass.