store: writers serialise instead of failing under contention !235

merged merged by cmc on 2026-09-04 15:58 UTC · krz/gitbay:write-serialisation into main

Discussion

cmc

Stacked on !234. Last of the v1.12.0 six.

Every transaction in the store package writes. A deferred one takes the write lock at its first write, by which point another writer may hold it; SQLite answers SQLITE_BUSY and does not run the busy handler for that case, so busy_timeout cannot help and the transaction fails outright.

Measured, eight concurrent writers and eight readers over two seconds:

writes write errors reads
today (deferred) 19,864 15,484 4,192
_txlock=immediate 13,834 0 8,387
immediate + 1 conn 11,483 0 11,411

44% of concurrent read-then-write transactions fail today. immediate takes the lock at BEGIN, where busy_timeout applies, and the failures go to zero — readers gain too, because failing writers currently spin.

SetMaxOpenConns(1), the issue's other suggestion, is the third row: it also removes the failures but caps write throughput for reader throughput this instance does not need.

TestConcurrentWritersDoNotFail fails 229 of 320 without the DSN change; I verified that by reverting it.

Behaviour change worth knowing: a heavily contended write now blocks for up to busy_timeout (5s) instead of returning an error immediately.

Closes #121

retargeted from hook-batch to main: !234 merged

2026-09-04 15:58 UTC