Concurrency: one connection is one serial resource

sigil-sqlite runs on a single-threaded cooperative scheduler. That makes two things true at once, and the interaction between them is the whole of this page.

1. A contended sqlite3_step must never block, or it freezes every fiber — including the one holding the lock, which can then never run to commit. So the native layer forces SQLite's own busy_timeout to 0 and the Scheme layer retries cooperatively, yielding between attempts.
2. A SQLite connection is a serial resource. SQLite ends a connection's implicit read transaction only when no statement on it is active.

Put those together and a shared connection has a sharp edge. If fiber A has a statement open — parked in the busy-retry, or simply part-way through a result set — and fiber B runs a query on the same connection, B's read transaction cannot be released when B finalizes: A is still using the btree. The connection is pinned to B's snapshot until A completes, and every later read on it silently misses anything committed in between.

What the library does about it

Every high-level primitive — sqlite-query, sqlite-query-row, sqlite-run, sqlite-exec — holds the connection exclusively for its whole duration, yields included. Concurrent calls on one connection serialise.

The lock spans the whole primitive, not just the busy window. A fiber yields inside an uncontended query too: allocation-driven, roughly once per 450 rows. A 20000-row query yields about 44 times with no contention anywhere. Guarding only the retry would leave every one of those windows open.

Statements are finalized on every exit path, including a raise. A statement leaked by a raise holds the connection's read transaction open for the life of the process, which turns a transient stale read into a permanent one.

What this costs you

Concurrent database work on one connection now queues. A fiber waiting for a connection whose holder is parked on a contended write waits for that whole busy budget — by default 5 seconds. If you want database calls to proceed concurrently, give each fiber its own connection. That is what connections are for, and WAL readers are cheap.

A fiber that waits more than *sqlite-connection-wait-seconds* (15s, three times the busy budget) for a connection raises rather than waiting forever. Two fibers each holding a connection the other wants is a deadlock; a bounded wait turns it into a loud, attributable error instead of a process that stops with no output.

What is NOT covered

The low-level API. sqlite-prepare / sqlite-step / sqlite-finalize are not serialised and cannot be: the library does not know when your statement is finished. If you hold a statement across steps on a connection another fiber also uses, you own the hazard described at the top of this page. Use the high-level API, or give that work its own connection.

Multi-statement transactions. BEGIN … COMMIT spans several primitives, and the lock is released between them, so another fiber's query can interleave inside your transaction. There is deliberately no exported "hold this connection across several calls" primitive, because holding the lock across caller code deadlocks the moment one of your database helpers calls another. Give a transaction its own connection.

busy_timeout

PRAGMA busy_timeout=N cannot be passed to SQLite — a blocking step would freeze the scheduler, which is the thing point 1 above exists to prevent. It used to be accepted and silently ignored: dead configuration that read as live. It now sets the cooperative retry budget instead, which is the same intent expressed where this library can act on it.

The budget is process-wide, not per-connection: sqlite-step receives a statement and has no connection to look a per-connection value up on. Prefer sqlite-set-busy-budget!, which says so in its name; read it back with sqlite-busy-budget.

Diagnostics

sqlite-connection-locked? answers whether a fiber currently holds a connection. It is a diagnostic, not a synchronisation primitive: by the time you act on the answer it may have changed.

THE BUSY BUDGET NOW MEANS SOMETHING DIFFERENT

Read this before tuning *sqlite-busy-budget-seconds* (5.0s), because the parameter did not change but its job did.

Before: exhausting the budget was catastrophic. It raised, the raise leaked the statement, and the leaked statement pinned the connection for the life of the process. So the budget was a number chosen to make exhaustion rare — long enough that ordinary contention never reached it.

Now: exhaustion is merely a raise the caller sees and can handle, and every statement is finalized on the way out. What the budget sets instead is the worst-case stall every other database user on that connection pays, because they queue behind the holder.

Those are different jobs, and 5 seconds was chosen for the first one. A shorter budget now means faster failure and a shorter worst-case stall; a longer one means more patience under contention and a longer stall. Neither reading is available from the old number.

It has deliberately not been changed here. Tuning it is a separate decision that should be made against the new meaning rather than inherited from the old.