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
- AI assisted search-based research actually works now - 21st April 2025
- Maybe Meta's Llama claims to be open source because of the EU AI act - 19th April 2025
- Image segmentation using Gemini 2.5 - 18th April 2025