docs/concurrency.md — sigil-sqlite

1# Concurrency: one connection is one serial resource

3`sigil-sqlite` runs on a single-threaded cooperative scheduler. That makes two

4things true at once, and the interaction between them is the whole of this page.

61. A contended `sqlite3_step` must never block, or it freezes every fiber —

7 including the one holding the lock, which can then never run to commit. So

8 the native layer forces SQLite's own `busy_timeout` to 0 and the Scheme layer

9 retries cooperatively, yielding between attempts.

102. **A SQLite connection is a serial resource.** SQLite ends a connection's

11 implicit read transaction only when *no statement on it is active*.

13Put those together and a shared connection has a sharp edge. If fiber A has a

14statement open — parked in the busy-retry, or simply part-way through a result

15set — and fiber B runs a query on the *same* connection, B's read transaction

16cannot be released when B finalizes: A is still using the btree. The connection

17is pinned to B's snapshot until A completes, and every later read on it silently

18misses anything committed in between.

20## What the library does about it

22Every high-level primitive — `sqlite-query`, `sqlite-query-row`, `sqlite-run`,

23`sqlite-exec` — holds the connection exclusively for its whole duration, yields

24included. Concurrent calls on one connection serialise.

26**The lock spans the whole primitive, not just the busy window.** A fiber yields

27inside an *uncontended* query too: allocation-driven, roughly once per 450 rows.

28A 20000-row query yields about 44 times with no contention anywhere. Guarding

29only the retry would leave every one of those windows open.

31Statements are finalized on **every** exit path, including a raise. A statement

32leaked by a raise holds the connection's read transaction open for the life of

33the process, which turns a transient stale read into a permanent one.

35## What this costs you

37Concurrent database work on one connection now **queues**. A fiber waiting for a

38connection whose holder is parked on a contended write waits for that whole busy

39budget — by default 5 seconds. If you want database calls to proceed

40concurrently, **give each fiber its own connection**. That is what connections

41are for, and WAL readers are cheap.

43A fiber that waits more than `*sqlite-connection-wait-seconds*` (15s, three times

44the busy budget) for a connection **raises** rather than waiting forever. Two

45fibers each holding a connection the other wants is a deadlock; a bounded wait

46turns it into a loud, attributable error instead of a process that stops with no

47output.

49## What is NOT covered

51**The low-level API.** `sqlite-prepare` / `sqlite-step` / `sqlite-finalize` are

52not serialised and cannot be: the library does not know when your statement is

53finished. If you hold a statement across steps on a connection another fiber

54also uses, you own the hazard described at the top of this page. Use the

55high-level API, or give that work its own connection.

57**Multi-statement transactions.** `BEGIN` … `COMMIT` spans several primitives,

58and the lock is released between them, so another fiber's query can interleave

59inside your transaction. There is deliberately no exported "hold this connection

60across several calls" primitive, because holding the lock across caller code

61deadlocks the moment one of your database helpers calls another. **Give a

62transaction its own connection.**

64## busy_timeout

66`PRAGMA busy_timeout=N` cannot be passed to SQLite — a blocking step would

67freeze the scheduler, which is the thing point 1 above exists to prevent. It

68used to be accepted and silently ignored: dead configuration that read as live.

69It now sets the cooperative retry budget instead, which is the same intent

70expressed where this library can act on it.

72The budget is **process-wide**, not per-connection: `sqlite-step` receives a

73statement and has no connection to look a per-connection value up on. Prefer

74`sqlite-set-busy-budget!`, which says so in its name; read it back with

75`sqlite-busy-budget`.

77## Diagnostics

79`sqlite-connection-locked?` answers whether a fiber currently holds a

80connection. It is a diagnostic, not a synchronisation primitive: by the time you

81act on the answer it may have changed.

83## THE BUSY BUDGET NOW MEANS SOMETHING DIFFERENT

85Read this before tuning `*sqlite-busy-budget-seconds*` (5.0s), because the

86parameter did not change but **its job did**.

88**Before:** exhausting the budget was catastrophic. It raised, the raise leaked

89the statement, and the leaked statement pinned the connection for the life of

90the process. So the budget was a number chosen to make exhaustion *rare* — long

91enough that ordinary contention never reached it.

93**Now:** exhaustion is merely a raise the caller sees and can handle, and every

94statement is finalized on the way out. What the budget sets instead is the

95**worst-case stall every other database user on that connection pays**, because

96they queue behind the holder.

98Those are different jobs, and 5 seconds was chosen for the first one. A shorter

99budget now means faster failure and a shorter worst-case stall; a longer one

100means more patience under contention and a longer stall. Neither reading is

101available from the old number.

102

103It has deliberately not been changed here. Tuning it is a separate decision that

104should be made against the new meaning rather than inherited from the old.