Anyone out there who's an expert on #SQLite concurrency, and would be willing to help out a little open source project with some debug advice? Boost for reach?
@Floppy I might know a few things about that?
Main thing is you can concurrently read but never concurrently write, per database. A way to get around that is to build a queue service in front of the DB to accept & buffer writes and then apply them serially.
Also, I've found these optimizations (like write-ahead log) handy: https://phiresky.github.io/blog/2020/sqlite-performance-tuning/
@lmorchard @Floppy We use rqlite (SQLite server) for that exact thing at $dayjob.
@lmorchard thanks, I’ll take a read and see if that gets me anywhere!
@Floppy which project?
@Floppy @manyfold in my experience, it’s simply a combination of using a busy wait and upgrading transactions to write transactions immediately (BEGIN IMMEDIATE) if you know they’ll write (because the latter doesn’t respect the busy wait). See https://www.sqlite.org/c3ref/busy_timeout.html and https://sqlite.org/lang_transaction.html
@Floppy @manyfold there’s also some interesting stuff here that may apply in your case: https://kerkour.com/sqlite-for-servers