←back to thread

SQLite concurrency and why you should care about it

(jellyfin.org)
353 points HunOL | 7 comments | 01 Nov 25 12:59 UTC | HN request time: 0.882s | source | bottom
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 #
1. 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 #
2. gwking ◴[01 Nov 25 22:35 UTC] No.45786047[source]▶
One tidbit that I don't see mentioned here yet is that ATTACH requires a lock. I just went looking for the documentation about this and couldn't find it, especially for WAL mode (https://www.sqlite.org/lockingv3.html mentions the super-journal, but the WAL docs do not mention ATTACH at all).

I have a python web app that creates a DB connection per request (not ideal I know) and immediately attaches 3 auxiliary DBs. This is a low traffic site but we have a serious reliability problem when load increases: the ATTACH calls occasionally fail with "database is locked". I don't know if this is because the ATTACH fails immediately without respecting the normal 5 second database timeout or what. To be honest I haven't implemented connection pooling yet because I want to understand what exactly causes this problem.

replies(1): >>45792829 #
3. 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 #
4. BinaryIgor ◴[02 Nov 25 11:14 UTC] No.45789479[source]▶
Exactly - it's also strange that they didn't find a simple solution of setting PRAGMA busy_timeout=N; if you google/search SQLITE_BUSY: database is locked there are plenty of solid results describing the problem and solution
5. 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 #
6. johannes1234321 ◴[02 Nov 25 16:42 UTC] No.45791553{3}[source]▶
I have no experience in Go, but won't that be a full database lock and prevent transactions with more than one operation?
7. sgbeal ◴[02 Nov 25 19:44 UTC] No.45792829[source]▶
> I have a python web app that creates a DB connection per request (not ideal I know)

FWIW, "one per request per connection is bad" (for SQLite) is FUD, plain and simple. SQLite's own forum software creates one connection per request (it creates a whole forked process per request, for that matter) and we do not have any problems whatsoever with that approach.

Connection pools (with SQLite) are a solution looking for a problem, not a solution to a real problem.