Triggers Updating FTS Tables Cause Transaction Rollbacks in SQLite

Transaction Rollback Due to FTS Table Updates via Triggers During Multi-Table Insertion

The core issue involves a transaction that fails when triggers attempt to update Full-Text Search (FTS) virtual tables during multi-step insertions into related base tables. The failure occurs specifically when using the Better-Sqlite3 library in JavaScript, though the same SQL statements succeed when executed directly via the SQLite command-line interface (CLI). The problem manifests as an aborted transaction when triggers associated with treatments or bibRefCitations tables insert data into their corresponding FTS tables (treatmentsFts, bibRefCitationsFts). However, a similar trigger on the figureCitations table does not cause failures. The discrepancy between triggers, combined with the library-specific behavior, points to a nuanced interaction between SQLite’s transaction model, FTS virtual tables, and client library implementation details.

The failure occurs under these conditions:

• A transaction wraps multiple INSERT operations across tables with AFTER INSERT triggers.
• Triggers propagate data from base tables (e.g., bibRefCitations) to contentless FTS tables (e.g., bibRefCitationsFts).
• The application uses INSERT ... RETURNING clauses (implicitly via Better-Sqlite3’s insertBibRefCitationGet_bibRefCitations_id.get() method) to retrieve auto-generated primary keys.
• The SQLite environment may involve a specific version or configuration where a historical bug related to FTS triggers and transactions resurfaced.

Root Causes: FTS Trigger Conflicts with Transaction Boundaries and RETURNING Clauses

The transaction rollback stems from three interrelated factors:

1. Contentless FTS Tables and Trigger-Induced Write Conflicts
   Contentless FTS tables (content='') rely entirely on external triggers to synchronize data. When a trigger attempts to insert into an FTS table during a transaction, SQLite’s internal locking mechanisms may interpret this as a write operation conflicting with the outer transaction’s scope. This is particularly acute when the outer transaction uses RETURNING clauses to fetch generated keys, as it creates a temporary state where the database connection is in a "query" mode while also executing write operations via triggers. The Better-Sqlite3 library’s strict handling of transactional states exacerbates this conflict, whereas the SQLite CLI’s more forgiving execution model masks the issue.

2. RETURNING Clause Interference with Trigger Execution
   The INSERT ... RETURNING syntax retrieves the last inserted rowid immediately after the INSERT operation. When a trigger fires during this INSERT, the RETURNING clause may attempt to finalize the statement’s results before the trigger’s FTS insertion completes. This creates a race condition where the transaction’s integrity depends on the order of operations. The figureCitations trigger’s success suggests that its FTS insertion either completes faster (due to smaller data size in captionText) or does not interact with the RETURNING logic in a conflicting way.

3. SQLite Bug #21127c1160: Triggered FTS Inserts in Transactions
   A previously reported bug (referenced indirectly by the user) involves transactions aborting when triggers modify FTS tables after an INSERT with a RETURNING clause. This bug arises because SQLite’s query planner temporarily holds locks on schema objects during RETURNING processing, which conflicting trigger-induced writes violate. The bug was partially fixed in SQLite 3.35.0 (2021-03-12), but edge cases persist in environments where multiple FTS updates occur within a single transaction.

Resolution: Isolating Trigger Logic, Avoiding RETURNING Clauses, and Library-Specific Workarounds

Step 1: Validate SQLite Version and Contentless FTS Configuration

Upgrade to SQLite 3.35.0 or later if not already using it. Verify FTS table definitions to ensure they are contentless (content='') and that triggers correctly map rowid values. For example, confirm that treatmentsFts includes all necessary columns (treatmentTitle, fulltext) and that the trigger uses new.id for rowid synchronization.

Step 2: Eliminate RETURNING Clauses in FTS-Critical Transactions

Replace INSERT ... RETURNING with separate INSERT and SELECT last_insert_rowid() statements. This decouples the key retrieval from the insertion, avoiding the locking conflict caused by RETURNING:

// Original (problematic):
const { bibRefCitations_id } = insertBibRefCitationGet_bibRefCitations_id.get(bibRefCitation);

// Revised:
db.prepare('INSERT INTO bibRefCitations (...) VALUES (...);').run(bibRefCitation);
const bibRefCitations_id = db.prepare('SELECT last_insert_rowid() AS id;').get().id;

Step 3: Defer FTS Updates Using Temporary Tables or Queues

Modify triggers to write FTS data indirectly via temporary tables, then process the queue after the transaction commits. This requires adding a pendingFtsUpdates table and a post-commit hook:

CREATE TABLE pendingFtsUpdates (
  ftsTable TEXT,
  rowid INTEGER,
  columns TEXT
);

CREATE TRIGGER bc_afterInsert AFTER INSERT ON bibRefCitations 
BEGIN
  INSERT INTO pendingFtsUpdates (ftsTable, rowid, columns)
  VALUES ('bibRefCitationsFts', new.id, new.fulltext);
END;

-- In application code, after transaction commit:
db.transaction(() => {
  const updates = db.prepare('SELECT * FROM pendingFtsUpdates;').all();
  updates.forEach(update => {
    db.prepare(`INSERT INTO ${update.ftsTable} (rowid, fulltext) VALUES (?, ?);`)
      .run(update.rowid, update.columns);
  });
  db.prepare('DELETE FROM pendingFtsUpdates;').run();
});

Step 4: Use Deferred Triggers or Disable Triggers During Transaction

If immediate FTS updates are unnecessary, disable triggers during the transaction and manually refresh FTS data afterward:

db.prepare('PRAGMA defer_foreign_keys = ON;').run();  -- Relax constraint checks
db.prepare('DROP TRIGGER bc_afterInsert;').run();     -- Disable trigger
-- Execute inserts...
db.prepare('CREATE TRIGGER bc_afterInsert ... ').run(); -- Re-enable trigger
-- Manually update FTS tables using `INSERT INTO bibRefCitationsFts SELECT ... FROM bibRefCitations;`

Step 5: Switch to Contentless FTS External Content Tables with Notindexed

For large datasets, configure FTS tables to reference base tables directly using content='bibRefCitations' and exclude non-searchable columns with notindexed:

CREATE VIRTUAL TABLE bibRefCitationsFts USING fts5 (
  fulltext,
  content='bibRefCitations',
  content_rowid='id'
);
-- Remove triggers; FTS updates are automatic but read-only.

Step 6: Profile Better-Sqlite3 Transaction Handling

Investigate whether Better-Sqlite3 enforces stricter isolation levels (e.g., IMMEDIATE transactions) compared to the SQLite CLI’s default DEFERRED mode. Force transactions to begin with BEGIN DEFERRED to reduce locking contention:

db.prepare('BEGIN DEFERRED;').run();
-- Execute inserts...
db.prepare('COMMIT;').run();

Step 7: Validate figureCitations Trigger for Idiosyncratic Differences

Analyze why the figureCitations trigger succeeds. Likely reasons include:

• figureCitationsFts has fewer indexed columns (captionText vs. treatmentTitle + fulltext), reducing lock contention.
• figureCitations inserts occur later in the transaction, after initial locks have been released.
• The captionText column contains smaller strings, minimizing temporary storage conflicts.

Replicate these conditions in other triggers by simplifying FTS column sets or reordering insertion steps.

By methodically isolating trigger logic, avoiding RETURNING clauses, and adjusting transactional semantics, the conflict between FTS updates and multi-step transactions can be resolved. The solution hinges on recognizing SQLite’s internal locking behavior and adapting the client library’s interaction model to align with it.