RELEASE-NOTES-v0.16.3.md

1# sigil-sqlite 0.16.3

3**A connection is now serialised across each high-level call. This changes

4concurrency semantics: database calls on ONE connection queue behind each

5other. If you want them to proceed concurrently, give each fiber its own

6connection.**

8Patch release, no API break. Three defects, all reachable in normal use, all in

9this library. Consumers need no code change; they may need to reconsider how

10many connections they open.

12## What was wrong

14A SQLite connection is a serial resource. SQLite ends a connection's implicit

15read transaction only when *no statement on it is active*. So if fiber A has a

16statement open on a connection -- parked in the cooperative busy-retry, or

17part-way through a result set -- and fiber B runs a query on the *same*

18connection, B's read transaction cannot be released when B finalizes: A is still

19using the btree. The connection is pinned to B's snapshot until A completes, and

20every later read on it silently misses anything committed in between.

22Then it got worse on its own. Once pinned, the connection's own parked write can

23never complete, so it exhausts the busy budget and RAISES -- and `sqlite-query`

24finalized only on its `else` branch, so the raise leaked the statement. A leaked

25statement holds the read transaction open **for the life of the process**.

27Observed in kiln, whose controller shares one connection between its HTTP app

28and its run loop: `GET /api/groups` answered **HTTP 200 with a pre-commit view

29of its own database** -- 55 rows where an independent connection to the same

30file saw 755 -- and stayed that way until the process was restarted. No error,

31no log line, no way for a caller to tell.

33## What changed

35**1. The connection is held across the whole primitive.** `sqlite-query`,

36`sqlite-query-row`, `sqlite-run` and `sqlite-exec` take a per-connection lock

37for their entire duration, yields included.

39Guarding only the busy window would not have been enough, and that is measured

40rather than argued: a fiber yields inside an *uncontended* query too,

41allocation-driven, roughly **once per 450 rows**. A witness fiber counts ~44

42yields across a 20000-row query with no contention anywhere.

44Scope is deliberately the primitive. Held any coarser it would deadlock the

45moment one of a consumer's database helpers called another. At primitive scope

46reentrancy is impossible by construction: no user code runs between prepare and

47finalize.

49**2. Statements are finalized on every exit path**, raises included.

51**3. `PRAGMA busy_timeout=N` is honoured instead of silently swallowed.** It

52cannot be passed to SQLite -- a blocking step freezes the whole cooperative

53scheduler, measured at 0 witness ticks in two seconds against 4701 for the

54retrying path -- so it now sets the cooperative retry budget, which is the same

55intent expressed where this library can act on it. `sqlite-busy-budget` and

56`sqlite-set-busy-budget!` are the explicit form. The budget is process-wide.

58## What it costs you, measured

60An external writer held the write lock for 500ms while two fibers used one

61shared connection:

63| | |

64|---|---|

65| read on the **shared** connection | **452 ms** -- it queued behind the parked write |

66| read on **its own** connection | **1.1 ms** |

68At rest the lock costs nothing; the existing cooperative-busy-retry test's

69timings are unchanged. Under contention a fiber waits for however long the

70connection's holder is parked, **bounded by the busy budget -- 5 seconds by

71default**. That is the number to weigh. The remedy is a connection per fiber,

72and WAL readers are cheap.

74A fiber that waits more than three times the busy budget for a connection

75**raises** rather than waiting forever, so two fibers deadlocking over two

76connections is a loud error instead of a silent stop.

79## THE BUSY BUDGET NOW MEANS SOMETHING DIFFERENT

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

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

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

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

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

87enough that ordinary contention never reached it.

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

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

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

92they queue behind the holder.

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

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

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

97available from the old number.

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

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

101

102## Not covered, deliberately

103

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

105 are not serialised and cannot be: the library cannot know when your statement

106 is finished. Holding a statement across steps on a connection another fiber

107 uses keeps the hazard above.

108- **Multi-statement transactions.** `BEGIN` … `COMMIT` spans several

109 primitives, so another fiber's query can still interleave inside your

110 transaction. Give a transaction its own connection.

111

112Both are in `docs/concurrency.md`.

113

114## New

115

116- `sqlite-busy-budget`, `sqlite-set-busy-budget!` -- the retry budget, explicitly.

117- `sqlite-open-statements` -- statements open through the high-level API. 0 at

118 rest; non-zero at rest is a leaked statement.

119- `sqlite-connection-locked?` -- diagnostic.

120- `docs/concurrency.md`.

121- `test/test-sqlite-connection-pin.sgl` -- the regression test. Four arms, the

122 first two differing by exactly one read, which is the variable that decides

123 the bug.

124- `sigil-test` / `sigil-test-runner` as dev-dependencies: `sigil test` refuses

125 to build a native package without them, so the README's `sigil test --report`

126 could not have worked.