I've been using RWMutex'es around SQLite calls as a precaution since I couldn't quite figure out if it was safe for concurrent use. This is perhaps overkill?
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?
> I've been using RWMutex'es around SQLite calls as a precaution since I couldn't quite figure out if it was safe for concurrent use. This is perhaps overkill?
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.
If this is Go and database/sql (as the modernc tidbit points to) this comment is ill advised.
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.
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.
Do you really need concurrency? My understanding is read are like… picoseconds, because everything happens in memory. You don’t have a separate server to call.
A quick search suggests SSD reads are in the milliseconds. I’ve found that, or microseconds to be roughly accurate. SQLite queries, however, can take seconds (or more in pathological cases) depending on how optimized the indices are to the query being run, load, etc. This is one of the reasons I don’t love using SQLite in Bun servers. It makes me nervous that a bad query will bring down the single thread of the app.
it is easy to assume most programmers today know roughly how long things take since this has existed in various forms since Jeff/Peter first published it:
From my limited experience and understanding, I believe concurrent reads are OK, and concurrent writes are queued internally. As long as you don't mix the order of the writes you're doing, SQLite can queue them to as best as it can.
The database file itself is safe for concurrent use, but the internal sqlite3 database structure itself can be unsafe to share across thread, depending on how your sqlite3 library was compiled. See https://www.sqlite.org/threadsafe.html
> I couldn't quite figure out if it was safe for concurrent use
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
sqlite supports concurrent access. It's optional, though. It also requires help from the os/file system (that's usually only an issue in specific contexts).
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.
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?