←back to thread

SQLite concurrency and why you should care about it

(jellyfin.org)
353 points HunOL | 1 comments | 01 Nov 25 12:59 UTC | HN request time: 0.213s | 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 #
kijin ◴[01 Nov 25 15:41 UTC] No.45782543[source]▶
Wouldn't that "fix" make the problem worse on the whole, by making transactions hold onto write locks longer than necessary? (Not trying to disagree, just curious about potential downsides.)
replies(1): >>45785130 #
1. asa400 ◴[01 Nov 25 20:40 UTC] No.45785130[source]▶
It’s a reasonable question!

In WAL mode, writers and readers don’t interfere with each other, so you can still do pure read queries in parallel.

Only one writer is allowed at a time no matter what, so writers queue up and you have to take the write lock at some point anyway.

In general, it’s hard to say without benchmarking your own application. This will get rid of SQLITE_BUSY errors firing immediately in the situation of read/write/upgrade-read-to-write scenario I described, however. You’d be retrying the transactions that fail from SQLITE_BUSY anyway, so that retrying is what you’d need to benchmark against.

It’s a subtle problem, but I’d rather queue up writes than have to write the code that retries failed transactions that shouldn’t really be failing.