SQLite Busy Timeout Waits Only for Some Lock Conflicts

A voice application stores queued audio clips in SQLite. A worker starts a deferred transaction, reads a clip’s state, then marks it claimed. Meanwhile, another connection is writing. It is tempting to set a long busy timeout and assume the worker’s update will wait for its turn. That assumption fails for some lock conflicts.

sqlite3_busy_timeout(db, milliseconds) installs a busy handler on one connection. On eligible contention, the handler sleeps and retries until its accumulated sleep reaches the configured threshold; then sqlite3_step() returns SQLITE_BUSY. A value at or below zero disables busy handlers. Only one busy handler can exist per connection, so setting the timeout replaces a handler installed earlier. These details are in the busy timeout API.

The crucial exception comes from the busy handler API: SQLite may skip the handler when waiting would create a deadlock. In the documented rollback-journal example, connection A holds a read lock and tries to promote it to a reserved lock. Connection B already holds a reserved lock and needs an exclusive lock to finish. A waits for B’s reserved lock; B waits for A’s read lock. More sleep cannot release either lock, so SQLite can return SQLITE_BUSY immediately to A. An immediate busy result therefore does not prove the timeout setting was ignored.

The transaction documentation explains why the voice worker can reach this point: a deferred transaction whose first statement is a SELECT starts as a read transaction, and a later write attempts an upgrade. SQLite permits one simultaneous write transaction. If the workflow knows it will write, BEGIN IMMEDIATE requests the write transaction at the start. That can itself return SQLITE_BUSY, but it avoids discovering a read-to-write upgrade conflict after the worker has already made decisions from a snapshot.

For this worker, keep the transaction short and handle SQLITE_BUSY at the transaction boundary. Release the read transaction, then retry the whole claim decision so the clip state is read again. Retrying only the failed UPDATE inside the old transaction can preserve a stale decision. A timeout is useful for transient waits; transaction ordering and bounded retries are still part of the application design. The lock-cycle example describes rollback-journal behavior; write-ahead logging has different concurrency details and should be evaluated separately.