Optimizing SQLite for servers (via) Sylvain Kerkour's comprehensive set of lessons learned running SQLite for server-based applications.
There's a lot of useful stuff in here, including detailed coverage of the different recommended PRAGMA
settings.
There was also a tip I haven't seen before about BEGIN IMMEDIATE
transactions:
By default, SQLite starts transactions in
DEFERRED
mode: they are considered read only. They are upgraded to a write transaction that requires a database lock in-flight, when query containing a write/update/delete statement is issued.The problem is that by upgrading a transaction after it has started, SQLite will immediately return a
SQLITE_BUSY
error without respecting thebusy_timeout
previously mentioned, if the database is already locked by another connection.This is why you should start your transactions with
BEGIN IMMEDIATE
instead of onlyBEGIN
. If the database is locked when the transaction starts, SQLite will respectbusy_timeout
.
Recent articles
- The last six months in LLMs, illustrated by pelicans on bicycles - 6th June 2025
- Tips on prompting ChatGPT for UK technology secretary Peter Kyle - 3rd June 2025
- How often do LLMs snitch? Recreating Theo's SnitchBench with LLM - 31st May 2025