←back to thread

SQLite concurrency and why you should care about it

(jellyfin.org)
369 points HunOL | 1 comments | 01 Nov 25 12:59 UTC | HN request time: 0.198s | 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. BinaryIgor ◴[02 Nov 25 11:06 UTC] No.45789444[source]▶
Also worth mentioning - it happens more often when you set journal_mode=WAL, which is not a default.

The default is DELETE mode, where the rollback journal is deleted at the conclusion of each transaction. What's more - in this mode (not-WAL), readers can coexist, but they do block the writer (which is always one) and the writer block readers - concurrency is highly limited.

In WAL mode - which pretty much always you should set - there's also at most one writer, but writer can coexist with readers.