I went and had a look in the source, and it seems like opening a database doesn't count as 'reading from a database' and triggering the recovery, instead being triggered by the first transaction. Hence, if there is a hot journal, what I thought was going to be a read-only transaction could acquire an exclusive lock and other concurrent transactions would then see SQLITE_BUSY. Oops.

Yes that is correct, in rollback-journal mode a read-only transaction will acquire an exclusive lock to write to the database if a hot-journal is detected.

I would think you could anticipate this pretty easily by (a) forcing a trivial read transaction as an exclusive operation upon opening a database for the first time or (b) treating the initial operation as a write even if it looks like a read-only transaction.

Of course you will still encounter SQLITE_BUSY if someone is poking around the DB with eg. the sqlite3 command line tool or some other process which isn't aware of your communication protocol :)

(I won't comment on WAL journal mode as I have no experience there)

Why not simply use SQLite's busy handling and set a timeout that is appropriate? I've seen suggestions in the range of several minutes. See sqlite.org/c3ref/busy_timeout.html for the C API or sqlite.org/pragma.html#pragma_busy_timeout for the PRAGMA to set said timeout and for more info.

That way you never have to "poll", never have to deal with any of it in fact, SQLite will do all the concurrency handling and waiting and polling for you, and as a bonus make your DB more robust (with concurrency code that has been proven a million times over).

You simply do a query, and it will do it when appropriate and return once it is ready. Zero effort.

Lastly, that way if you do get the BUSY signal back, you know something has gone terribly wrong and needs investigation or maintenance.

Special note: This all assumes you are accessing the DBs on a local machine (to the SQLite process) and not across a network, for then there are more to consider.

The busy timeout mechanism is implemented in terms of busy handlers; the docs go out of their way to tell you that a busy handler only might be called:

The presence of a busy handler does not guarantee that it will be invoked when there is lock contention.

Hence, I cannot assume that seeing SQLITE_BUSY is my error if I don't manage locking myself - in fact, it's the opposite, as it's only if I manage locking myself that seeing SQLITE_BUSY is a sure sign that I've gone wrong somehow! So, absent my own locking, it's not 'zero effort': I need to plan for SQLITE_BUSY as a normal return value, and the only sensible course of action seems to be recurrently attempting to open a new transaction. Ick. On the other hand, my application is already arbiting access to database connections between green threads - at the use sites, going from single-connection-per-database to multi-connection is literally only a matter of marking them as read-only or read-write if I manage locking myself.

Even if I could rely on it, the built-in busy timeout is inherently built on polling, and the poll intervals are up to 100ms. I definitely prefer notifications for these sorts of (relatively) long-duration waits, primarily because I expect delivery to be more prompt and to either be fair or offer an interesting fairness-performance tradeoff; writing an event-driven busy handler might be possible, but wouldn't look very different from managing locks myself (and I'm not sure it can be done entirely correctly s.t. SQLITE_BUSY never gets shown to the application).

(The above point might not be strong enough on its own to justify doing locking myself, as opposed to using the built-in busy timeout; it could well be fine, and probably would be, depending on performance parameters. I want to do it so that I can blissfully ignore SQLITE_BUSY, and any performance win is a nice bonus.)