Hacker News Reader

Comment by platz | Hacker News Reader

busy_timeout is often sidestepped (ignored) when a transaction attempts to upgrade from a read to a write producing SQLITE_BUSY.

By default, SQLite transactions start in DEFERRED mode, acting as read transactions until an actual write operation occurs.

If another connection begins writing to the database while your transaction is in this read state, an immediate SQLITE_BUSY error is triggered regardless of what you set busy_timeout to

View on HN

Exactly. In cases where I expect long-running parallel connections from separate processes to the same sqlite file, I make sure that all read transactions do `BEGIN DEFERRED` so `COMMIT` releases the read locks, and all write transactions do `BEGIN IMMEDIATE` so that `SQLITE_BUSY` timeout is not side-stepped.

There was one case where all transactions were implemented using nested `SAVEPOINT bla` so `BEGIN IMMEDIATE` could not be used without more hassle, so this ended all “I know I'm going to write” transactions to instantly update a single-row table so that their lock would not begin as DEFERRED and eventually switch to `IMMEDIATE`; this way almost all `SQLITE_BUSY` side-steppings disappeared. (timeout was set to 30 seconds but all read/write transactions were instrumented to have less than 5 seconds duration).

lol, I briefly thought of such a technique, but in the end, found it simpler to just use a synchronization primitive at the application level to serialize db access on transactions where I knew I was going to write. it amounts to about the same honor code.

Reply on news.ycombinator.com