←back to thread

SQLite concurrency and why you should care about it

(jellyfin.org)
369 points HunOL | 4 comments | 01 Nov 25 12:59 UTC | HN request time: 0s | source
Show context
asa400 ◴[01 Nov 25 13:32 UTC] No.45781519[source]▶
In SQLite, transactions by default start in “deferred” mode. This means they do not take a write lock until they attempt to perform a write.

You get SQLITE_BUSY when transaction #1 starts in read mode, transaction #2 starts in write mode, and then transaction #1 attempts to upgrade from read to write mode while transaction #2 still holds the write lock.

The fix is to set a busy_timeout and to begin any transaction that does a write (any write, even if it is not the first operation in the transaction) in “immediate” mode rather than “deferred” mode.

https://zeroclarkthirty.com/2024-10-19-sqlite-database-is-lo...

replies(9): >>45781577 #>>45781605 #>>45781634 #>>45781639 #>>45782026 #>>45782543 #>>45783088 #>>45787431 #>>45789444 #
simonw ◴[01 Nov 25 14:47 UTC] No.45782026[source]▶
Yeah I read the OP and my first instinct was that this is SQLITE_BUSY. I've been collecting posts about that here: https://simonwillison.net/tags/sqlite-busy/
replies(3): >>45786047 #>>45788998 #>>45789479 #
1. apitman ◴[02 Nov 25 09:25 UTC] No.45788998[source]▶
The necessity of this sort of tribal knowledge kills a lot of the simplicity of sqlite for me. Honestly it seems to have a lot of footguns. I've tried to understand proper concurrent use of sqlite with Golang about 5 times and never come away feeling like I actually get it.
replies(1): >>45790538 #
2. nasretdinov ◴[02 Nov 25 14:21 UTC] No.45790538[source]▶
With Go it's quite straightforward actually: use WAL mode + two connection pools, one for reads and the other, with MaxConnections set to 1, for writes. This way you should never encounter any concurrency issues, and Go will serialise writes for you too
replies(1): >>45791553 #
3. johannes1234321 ◴[02 Nov 25 16:42 UTC] No.45791553[source]▶
I have no experience in Go, but won't that be a full database lock and prevent transactions with more than one operation?
replies(1): >>45796617 #
4. nasretdinov ◴[03 Nov 25 07:20 UTC] No.45796617{3}[source]▶
Setting MaxOpenConns to 1 essentially limits the number of concurrently running (write) transactions to 1, which is exactly what we want. Whenever a concurrent thread wants to open a new transaction it'll have to wait.

Note that the application needs to be aware of that there are two pools — one for write operations and one for reads (the latter with no or high connection limit). The separation can be ensured on SQLite level too by adding ?_query_only=1 to connection parameters or setting the respective pragmas in the read-only pool.