Since I do a lot of cross-compiling I have been using https://modernc.org/sqlite for SQLite. Does anyone have some knowledge/experience/observations to share on concurrency and SQLite in Go?
You should not share sqlite connections between threads (or anything even remotely resembling threads): while the serialized mode ensures you won't corrupt the database, there is still per-connection state which may / will cause issues eventually e.g. [1][2][3]. Note that [1] does apply to read-only connections.
You can use separate connections concurrently. If you're using transactions and WAL mode you should have a pool of read-only connections (growable) and a single read/write connection. If you're not using multi-statement transactions (autocommit mode) then you can just have a pool.
[1] https://sqlite.org/c3ref/errcode.html
[2] https://sqlite.org/c3ref/last_insert_rowid.html (less of an issue now that sqlite has RETURNING)
[3] https://sqlite.org/c3ref/changes.html (possibly same)
Go database/sql handles ensuring each actual database connection is only by a single goroutine at a time (before being put back into the pool for reuse), and no sane SQLite driver should have issues with this.
If you're working in Go with database/sql you're meant to not have to worry about goroutines or mutexes. The API you're using (database/sql) is goroutine safe (unless the driver author really messed things up).
To be clear: each database/sql "connection" is actually a "pool of connections", that are already handed out (and returned) in goroutine safe way. The advise is to use two connection pools: a read-only one of size N, and a read-write one of size 1.
Regardless, mutexes are not needed.
All sane Go SQLite drivers should be concurrency safe.
That said, (and I'm biased but) there's something fishy about modernc concurrency: https://github.com/cvilsmeier/go-sqlite-bench#concurrent
Querying data with 2 threads shouldn't be 2x slower than using 1 thread; 4 threads shouldn't be 6x slower; or 8 threads 15x.
The folks at GoToSocial have long maitained a conccurency fix; I don't know if it's related to this performance issue.
Still the advice about using read-only and read-write connections still holds, for all drivers.
Why? Because in SQLite reads can be concurrent, but writes are exclusive. If you're using WAL, a single write can be concurrent with many reads, otherwise a writer also blocks readers. And SQLite handles all of this.
But SQLite locks are polling locks: a connection tries to acquire a lock; if it can't, it sleeps for a bit and tries again; rinse, repeat. When the other connection finally releases its lock, everyone else is sleeping. There's a lot of sleeping involved; also with exponential backoff.
Using read-only and read-write connections "fixes" this. Make the read-only a pool of N connections, and the read-write a single connection. Then, all waiting will be in Go channels, which is fast at waking waiters, and doesn't involve needless sleeping/pooling. As a bonus, context cancellation will be respected for this kind of blocking.
On this final point, my driver goes to great lengths to respect context cancellation, both of CPU queries (which you need to forcefully interrupt), and busy waiting on locks (which also doesn't work for most other drivers). So you can set a large busy timeout (one minute is the default) with the confidence that cancellation will work regardless. The dual connection strategy still offers performance benefits, though.
Three modes are supported: one where the library does no locking and you can only use the library from one thread at a time; one where global structures are locked but not connections so you can use each connection from one thread at a time; and one where everything is locked.
For actual concurrency you probably want one database connection per thread.
This guy is great: https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t...
https://colin-scott.github.io/personal_website/research/inte...
(This version is more useful since it shows change over time)
Something something, Cunningham's Law.
My sole reference: https://www.sqlite.org/lockingv3.html
This has been a big issue I have found when researching SQLite and trying to determine if it's suitable. I can't figure out the right way to do certain stuff or docs are difficult/outdated etc in end I always end up defaulting back to Postgres
> Making use of sql.DB from multiple goroutines is safe, since Go manages its own internal mutex.
Then I quote the database/sql documentation [2]:
> DB is a database handle representing a pool of zero or more underlying connections. It's safe for concurrent use by multiple goroutines.
[1]: https://stigsen.dev/notes/go-database-thread-safety/ [2]: https://pkg.go.dev/database/sql@go1.23.3#DB
So... you can get your ducks in a row in terms of checking sqlite docs, your sqlite config and compile-time options, and your runtime environment, and then remove the mutexes (modernc is another variable, but I don't know about that). Or, if it's working for you, you can leave them in.
Reasons to remove them might be... it's a pain to maintain them; it might help performance (sqlite is inherently one writer multiple readers, and since you're using RW locks, your locks may already align with sqlite's ability to use concurrency). If those aren't issues for you then you can leave them in.