←back to thread

SQLite concurrency and why you should care about it

(jellyfin.org)
353 points HunOL | 2 comments | 01 Nov 25 12:59 UTC | HN request time: 0.023s | 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. 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 #
2. 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.