fix: prevent SQLITE_BUSY on concurrent v2 writes

TheLastCicada · TheLastCicada · commit 95792e441f27 · 2026-06-02T11:36:00.000-07:00
The busy_timeout PRAGMA is per-connection, but it was only set on the
single connection touched at startup by setV2WALMode(); the other pooled
connections defaulted to busy_timeout=0 and failed immediately under
write contention. Sequelize's sqlite dialect does not honour
dialectOptions.busyTimeout, so set busy_timeout on every pooled
connection via the shared installSqlitePragmas hook.

Also set transactionType=IMMEDIATE on the v2 sqlite configs. SQLite
ignores busy_timeout when a DEFERRED transaction upgrades a held read
lock to a write lock (it returns SQLITE_BUSY at once to avoid deadlock),
which intermittently failed read-then-write transactions (e.g. cascade
delete staging) racing the background sync. IMMEDIATE acquires the write
lock up front so they queue on busy_timeout instead.
diff --git a/src/config/config.js b/src/config/config.js
@@ -118,10 +118,18 @@ export default {
     logging: mirrorLogging,
   },
   // V2 Database Configurations
+  //
+  // transactionType IMMEDIATE: managed transactions BEGIN IMMEDIATE so the
+  // write lock is taken up front. SQLite ignores busy_timeout when a DEFERRED
+  // transaction tries to upgrade a held read lock to a write lock (it returns
+  // SQLITE_BUSY at once to avoid deadlock), so read-then-write transactions
+  // racing the background sync intermittently failed. IMMEDIATE makes them
+  // queue on busy_timeout instead.
   v2Local: {
     dialect: 'sqlite',
     storage: `${v2PersistanceFolder}/data.sqlite3`,
     logging: localLogging,
+    transactionType: 'IMMEDIATE',
     dialectOptions: {
       busyTimeout: 10000,
     },
@@ -130,11 +138,13 @@ export default {
     dialect: 'sqlite',
     storage: `${v2PersistanceFolder}/simulator.sqlite3`,
     logging: false,
+    transactionType: 'IMMEDIATE',
   },
   v2Test: {
     dialect: 'sqlite',
     storage: `${testDbDir}/test-v2-${testRunId}.sqlite3`,
     logging: false,
+    transactionType: 'IMMEDIATE',
     dialectOptions: {
       busyTimeout: 30000, // 30 seconds - allows SQLite to wait for locks instead of immediately failing
     },
@@ -143,6 +153,7 @@ export default {
     dialect: 'sqlite',
     storage: `${testDbDir}/testMirror-v2-${testRunId}.sqlite3`,
     logging: false,
+    transactionType: 'IMMEDIATE',
   },
   v2Mirror: {
     username: getConfigV2().MIRROR_DB?.DB_USERNAME || '',
diff --git a/src/database/sqlite-pragmas.js b/src/database/sqlite-pragmas.js
@@ -4,6 +4,12 @@ const SQLITE_PRAGMAS = [
   'PRAGMA cache_size = -65536',
   'PRAGMA temp_store = MEMORY',
   'PRAGMA mmap_size = 268435456',
+  // busy_timeout is per-connection: SQLite waits up to this many ms for a
+  // write lock before returning SQLITE_BUSY. Sequelize's sqlite dialect does
+  // not honour dialectOptions.busyTimeout, so it must be set on every pooled
+  // connection here - otherwise only the single connection touched at startup
+  // gets it and the rest fail immediately under concurrent writes.
+  'PRAGMA busy_timeout = 30000',
 ];
 
 const applySqlitePragmas = async (connection) => {
