Resolving SQLITE_BUSY on Database Close with FTS4 Triggers and In-Memory Attached Databases

Understanding the SQLITE_BUSY Error During Database Closure with FTS4 Triggers and Attached In-Memory Databases

Root Cause: Cross-Database Contention in In-Memory Attached Databases with FTS4 Triggers

The SQLITE_BUSY error encountered when closing the database connection (db1) arises from unresolved contention between two in-memory databases (db1 and db2) that are interconnected via the ATTACH DATABASE command. This contention is exacerbated by the use of FTS4 virtual tables and triggers that create implicit dependencies between the databases. The error occurs specifically when:

1. A virtual table (fts) in db1 or db2 is configured to reference a content table (history) via the content= parameter.
2. A trigger (history_ai) in db2 fires during an INSERT operation initiated through db1 after attaching db2 to db1.
3. The db2 connection is opened in shared cache mode (cache=shared) but remains active after db1 is detached.

The error manifests exclusively when both databases are in-memory. When db2 is a file-based database, the issue disappears because SQLite’s file-based databases handle locks and transactions differently compared to in-memory databases with shared caches. The SQLITE_BUSY error indicates that the database connection (db1) cannot release its resources cleanly due to an unresolved lock or pending transaction, even though all explicit statements have been finalized.

Critical Factors Leading to Resource Contention and Locks

1. FTS4 Virtual Table Content References and Cross-Database Triggers
   The FTS4 content= parameter creates a dependency between the virtual table (fts) and the content table (history). When the trigger in db2 executes during an INSERT initiated by db1, it attempts to modify the fts table in db2. However, because db1 is attached to db2, this operation creates a cross-database transaction that SQLite cannot fully resolve when working with in-memory databases. The FTS4 extension internally manages auxiliary tables (e.g., fts_content, fts_docsize), which may retain locks if transactions are not explicitly committed.

2. Shared Cache Mode in In-Memory Databases
   Opening db2 with mode=memory&cache=shared allows multiple connections to access the same in-memory database. However, when db1 attaches db2, both connections (db1 and db2) interact with the same in-memory instance. SQLite’s shared cache mode does not fully isolate transactions across connections, leading to scenarios where one connection (db1) holds a lock that conflicts with the other (db2).

3. Trigger Execution Context and Implicit Transactions
   The history_ai trigger in db2 executes within the context of db1’s connection after the ATTACH command. This creates a situation where db1’s connection modifies db2’s schema objects (the fts table). SQLite’s transaction model requires that such cross-database operations be atomic, but in-memory databases lack the durability guarantees of file-based databases. When db1 detaches db2, any pending transactions related to the trigger’s INSERT into fts may not be fully finalized, leaving db1 in a state where it cannot release locks.

4. Unsafe Schema Modifications and trusted_schema Configuration
   The unsafe use of virtual table warning (observed in early iterations of the code) stems from SQLite’s security model, which restricts schema modifications (e.g., creating triggers that reference virtual tables) unless PRAGMA trusted_schema=1 is set. While enabling trusted_schema resolves this warning, it does not address the underlying lock contention caused by the FTS4 trigger’s cross-database operations.

Comprehensive Resolution: Isolation, Transaction Management, and Schema Design

Step 1: Eliminate Cross-Database Triggers and Virtual Table Dependencies
Restructure the schema to avoid triggers that modify virtual tables in attached databases. Instead, consolidate the FTS4 virtual table and its content table within the same database. For example:

• Remove the ATTACH DATABASE workflow.
• Use a single database (db1) for both the history table and its associated fts virtual table.

Step 2: Replace In-Memory Attached Databases with Temporary Databases
If cross-database operations are unavoidable, use file-based temporary databases instead of in-memory databases. Temporary databases (e.g., file:db2?mode=memory&cache=private) do not suffer from the same locking issues as shared in-memory databases. Modify the ATTACH command as follows:

ATTACH DATABASE 'file:db2.sqlite' AS new;  -- File-based, no shared cache

This ensures that transactions in db1 and db2 are isolated and can be closed independently.

Step 3: Explicitly Commit Transactions Before Detaching
Force a COMMIT after the INSERT OR IGNORE operation to ensure all changes to db2 are finalized before detaching:

exec(db1, "INSERT OR IGNORE INTO new.history (entry) SELECT entry FROM history;");
exec(db1, "COMMIT;");  -- Finalize transaction
exec(db1, "DETACH DATABASE new;");

Step 4: Close Connections in Reverse Dependency Order
Even if the application logic requires db2 to remain open after closing db1, prioritize closing db2 first in scenarios involving shared in-memory databases:

close(db2);  // Close dependent database first
close(db1);  // Now safe to close primary database

This ensures that db1 does not hold references to db2 during shutdown.

Step 5: Disable Shared Cache for In-Memory Databases
Avoid using cache=shared for in-memory databases when attaching them. Shared caches are designed for concurrent access from multiple connections, but they introduce locking complexity. Instead, use a private cache:

db2 = open("file:db2?mode=memory&cache=private");

Step 6: Audit FTS4 Trigger Logic for Implicit Locks
Modify the history_ai trigger to minimize lock retention. For example, ensure that the trigger does not initiate long-running transactions or nested operations. If the FTS4 INSERT is the only operation, this is unlikely to be the culprit, but review the trigger’s SQL for unnecessary complexity.

Step 7: Use sqlite3_next_stmt to Identify Leaked Statements
Before closing db1, iterate over all prepared statements to confirm they are finalized. Extend the debugging code to log all statements:

sqlite3_stmt *stmt = nullptr;
while ((stmt = sqlite3_next_stmt(db1, stmt)) != nullptr) {
    fprintf(stderr, "Unfinalized statement: %s\n", sqlite3_sql(stmt));
    sqlite3_finalize(stmt);  // Force cleanup
}

Step 8: Enable SQLite’s Debugging Modes
Compile SQLite with -DSQLITE_DEBUG and use sqlite3_trace_v2 to log all database operations. This reveals hidden transactions or locks held by the FTS4 extension:

sqlite3_trace_v2(db1, SQLITE_TRACE_STMT | SQLITE_TRACE_PROFILE,
    [](unsigned mask, void *ctx, void *p, void *x) -> int {
        fprintf(stderr, "Statement: %s\n", sqlite3_expanded_sql((sqlite3_stmt*)p));
        return 0;
    }, nullptr);

Final Solution Summary
The SQLITE_BUSY error is resolved by:

1. Avoiding shared in-memory databases for attached schemas with FTS4 triggers.
2. Using file-based temporary databases for cross-database operations.
3. Explicitly committing transactions before detaching.
4. Closing dependent databases first.

By addressing these factors, the contention between db1 and db2 is eliminated, allowing both connections to close cleanly without SQLITE_BUSY.