SQLite Busy Handling Needs a Transaction Strategy

A busy timeout is a waiting policy, not a correctness strategy. An application must decide when to acquire its write transaction, how to handle lock contention and whether a failed transaction can be retried safely. Simply setting a very large timeout can turn a visible contention problem into a slow request queue.

Use BEGIN IMMEDIATE when a unit of work is definitely going to write and early writer acquisition matches the application contract. This makes contention appear before the application does work that it would otherwise need to repeat.

The concrete problem

The example uses SQLite with two local connections. The account balance update is guarded in SQL so a stale application-side balance read cannot authorize an overdraft. This is a small isolated demonstration, not a production banking workflow.

Figure: Acquire writer early → Guarded atomic update → Commit or retry whole unit.

A worked example

PRAGMA busy_timeout = 1500;
CREATE TABLE wallets (
  id INTEGER PRIMARY KEY,
  credits INTEGER NOT NULL CHECK (credits >= 0)
);
INSERT INTO wallets VALUES (1, 10);
BEGIN IMMEDIATE;
UPDATE wallets SET credits = credits - 3
WHERE id = 1 AND credits >= 3;
SELECT changes();
COMMIT;

Why the behavior matters

BEGIN IMMEDIATE attempts to establish a write transaction at the beginning. If another writer prevents it, the application encounters contention before reading values and computing a plan. A deferred transaction can begin with reads and later fail when trying to upgrade to a write transaction.

Check the affected-row count to distinguish a successful debit from a missing account or insufficient credits. A retry should repeat the whole logical transaction from a fresh state, not blindly resubmit only the last failed statement. Extended result codes can distinguish ordinary busy conditions from snapshot-related failures.

Failure modes and design tradeoffs

Not every busy situation waits for the full timeout; SQLite can return promptly when waiting cannot resolve the lock progression safely. Do not infer that the timeout is broken merely because an operation fails early. Inspect the transaction pattern and error code.

Retries also need a bounded total deadline and backoff. If a transaction interacts with an external service, repeating it may duplicate that interaction. Keep external side effects outside the retrying database section or attach an appropriate idempotency protocol. Repeated contention is a signal to shorten transactions or reconsider the write architecture, not endlessly increase wait times.

What to verify before adoption

Hold BEGIN IMMEDIATE open in one connection and verify another connection's contention behavior. Then release the writer and confirm a complete retry succeeds without double-decrementing credits. Test the insufficient-credit case and the missing-row case separately. Capture error codes and total request duration, not only success counts.

Primary reference

• SQLite documentation. Verify the exact deployed version and driver behavior before using the example in a production runbook.