←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.417s | 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 #
1. mickeyp ◴[01 Nov 25 13:49 UTC] No.45781634[source]▶
Indeed. Everyone who uses sqlite will get burnt by this one day and spend a lot of time chasing down errant write-upgraded transactions that cling on for a little bit longer than intended.
replies(1): >>45789496 #
2. BinaryIgor ◴[02 Nov 25 11:17 UTC] No.45789496[source]▶
SQLite has its quirks, but in this particular case all you need is set PRAGMA busy_timeout=<a few seconds> and the problem is solved; and if you google it, it's widely known issue with described (this) solution.

It's just weird that it's set to 0 by default rather than something resonable like 3000 or 5000 ms.