NixOS
libstore: use sqlit3 IMMEDIATE transactions
Motivation
Improve the sqlite3 busy situation.
This is still theory and needs testing.
Context
The default transaction behavior (of Sqlite3) is DEFERRED.
DEFERRED means that the transaction does not actually start until the database is first accessed. Internally, the BEGIN DEFERRED statement merely sets a flag on the database connection that turns off the automatic commit that would normally occur when the last statement finishes. This causes the transaction that is automatically started to persist until an explicit COMMIT or ROLLBACK or until a rollback is provoked by an error or an ON CONFLICT ROLLBACK clause. If the first statement after BEGIN DEFERRED is a SELECT, then a read transaction is started. Subsequent write statements will upgrade the transaction to a write transaction if possible, or return SQLITE_BUSY. If the first statement after BEGIN DEFERRED is a write statement, then a write transaction is started.
IMMEDIATE causes the database connection to start a new write immediately, without waiting for a write statement. The BEGIN IMMEDIATE might fail with SQLITE_BUSY if another write transaction is already active on another database connection.
source: https://www.sqlite.org/lang_transaction.html#immediate
Because Nix only uses transactions for write operations, changing the default will allow pushing the SQLITE_BUSY errors early instead of mid-transaction.
See also https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/
Priorities and Process
Add :+1: to pull requests you find important.
The Nix maintainer team uses a GitHub project board to schedule and track reviews.
https://briandouglas.ie/sqlite-defaults/ might also be good to take a look at.
How would one test this? Wouldn't mind trying to set up a test bench to test your theory, I'm just not quite sure what this does/solves.
