Issue Overview: Nested Transactions and Implicit Locking in Multi-Step Database Operations

The core issue revolves around attempting to detach an attached database in SQLite while the database remains locked due to active transactions. The user’s code involves nested SQL operations executed through multiple sqlite3_exec() calls, leading to an unexpected "database is locked" error during the DETACH command. This problem arises from SQLite’s transaction handling semantics, particularly when mixing implicit and explicit transactions across nested callback functions.

The user’s workflow involves three layers of operations:

1. A top-level function (WFC_UpdateMeetingDatabasesIfNeeded) initiates a transaction via sqlite3_exec() to query subscription data.
2. A callback (updateSubscriptionDatabaseCallback) executes a nested query to fetch instance data.
3. A deeper callback (updateInstanceDatabaseCallback) performs database attachment, data migration, and detachment.

The error occurs because SQLite does not support nested transactions. The outermost sqlite3_exec() call starts an implicit transaction that remains open while inner callbacks execute additional queries. When the innermost callback attempts to DETACH the database, the parent transaction still holds locks on database resources. This violates SQLite’s locking protocol, which requires all transactions to be fully committed or rolled back before modifying database connections (e.g., detaching databases).

Possible Causes: Transaction Scope Mismanagement and Lock Retention

1. Implicit Transaction Chaining

SQLite automatically wraps individual SQL statements in transactions when no explicit BEGIN is used. However, when using sqlite3_exec() with multiple statements separated by semicolons, SQLite treats them as part of a single transaction. The user’s code combines explicit BEGIN/END blocks with implicit transaction management from nested sqlite3_exec() calls, creating overlapping transaction scopes.

2. Write Operations in Read Transactions

The initial SELECT query in WFC_UpdateMeetingDatabasesIfNeeded starts a read transaction. Subsequent CREATE TABLE, REPLACE INTO, and ALTER TABLE operations attempt to upgrade this to a write transaction. SQLite allows this upgrade only if no other connections are active, but the nested callback structure creates complex locking conditions that may prevent clean transition between transaction types.

3. Resource Retention During Callback Execution

The attached database (fetched_db_%d) remains locked because:

• The parent transaction started by the outermost sqlite3_exec() retains a lock on the main database
• The ALTER TABLE and DROP TABLE operations acquire schema locks that persist until transaction commit
• File handles for temporary databases created via ATTACH remain open while referenced by active transactions

4. Transaction Isolation Levels

SQLite’s default DEFERRED transaction mode acquires locks progressively. The sequence of read operations followed by write operations creates a situation where the database connection holds a reserved lock while preparing for writing, blocking subsequent DETACH commands that require exclusive access.

Troubleshooting Steps, Solutions & Fixes: Transaction Boundary Enforcement and Lock Mitigation

1. Transaction Scope Analysis

Diagnostic Steps:

• Enable SQLite’s trace mode using sqlite3_trace_v2() to log all transaction boundaries
• Check for BEGIN/COMMIT mismatches in the generated SQL query string
• Verify transaction depth using sqlite3_txn_state() after each operation

Solution:
Restructure the code to use explicit transaction control with a single top-level transaction:

/* Top-level function */
sqlite3_exec(db, "BEGIN IMMEDIATE;", 0, 0, 0);
/* Perform all nested operations */
sqlite3_exec(db, "COMMIT;", 0, 0, 0);

Rationale:
The IMMEDIATE transaction type acquires a write lock immediately, preventing concurrent access issues. By containing all operations within a single explicit transaction, you avoid implicit transaction stacking.

2. Detach Timing and Lock Release

Diagnostic Steps:

• Use sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED, ...) to monitor active connections
• Check for open statements with sqlite3_next_stmt()
• Verify attached databases with PRAGMA database_list;

Solution:
Ensure all operations on the attached database complete before detaching:

/* Modified query string */
"BEGIN IMMEDIATE;"
"ATTACH '%s' AS fetched_db_%d;"
/* ... data migration steps ... */
"COMMIT;"  /* Releases all locks */
"DETACH fetched_db_%d;";  /* Now safe */

Rationale:
Committing the transaction before detaching ensures all locks are released. The DETACH should be executed outside any transaction scope.

3. Callback Function Transaction Handling

Diagnostic Steps:

• Audit all callback functions for internal sqlite3_exec() calls
• Check for recursive transaction initiation in nested queries

Solution:
Refactor callback architecture to avoid nested sqlite3_exec() calls:

/* Replace nested sqlite3_exec() with prepared statements */
sqlite3_stmt *stmt;
sqlite3_prepare_v2(db, "SELECT...", -1, &stmt, 0);
while(sqlite3_step(stmt) == SQLITE_ROW) {
    /* Process rows directly */
}
sqlite3_finalize(stmt);

Rationale:
Prepared statements execute within the current transaction context without creating nested implicit transactions. This maintains a single transaction scope throughout the operation.

4. Temporary File Handling

Diagnostic Steps:

• Verify file permissions on temporary database files
• Check for retained file handles using OS-specific tools (lsof on Unix)

Solution:
Explicitly close temporary databases and enforce file deletion:

/* Add after DETACH */
remove(filename_buf);

Implementation Note:
Ensure the DETACH completes successfully before attempting file deletion. Use atomic filesystem operations for temp files if possible.

5. Connection Busy Handling

Diagnostic Steps:

• Implement a busy handler with sqlite3_busy_handler()
• Check for SQLITE_BUSY return codes in all database operations

Solution:
Configure an appropriate busy timeout:

sqlite3_busy_timeout(db, 5000);  /* 5 second timeout */

Rationale:
This allows SQLite to automatically retry locked operations, particularly important in scenarios with nested connections or concurrent access.

6. Schema Lock Mitigation

Diagnostic Steps:

• Monitor schema changes using SQLITE_LOCKED return codes
• Check for pending ALTER TABLE operations

Solution:
Use transactional DDL and avoid intermediate commits:

"BEGIN IMMEDIATE;"
"CREATE TABLE ...;"
"ALTER TABLE ...;"
"COMMIT;"  /* All schema changes atomically committed */

Rationale:
SQLite requires exclusive locks for schema changes. Grouping all DDL operations in a single transaction minimizes lock contention.

7. Cursor Management

Diagnostic Steps:

• Check for unfinalized statements using sqlite3_next_stmt()
• Verify all prepared statements are properly reset/finalized

Solution:
Add comprehensive error handling:

sqlite3_stmt *stmt = NULL;
if(sqlite3_prepare_v2(db, query, -1, &stmt, 0) != SQLITE_OK) {
    /* Handle error */
}
while(sqlite3_step(stmt) == SQLITE_ROW) {
    /* Process row */
}
if(sqlite3_finalize(stmt) != SQLITE_OK) {
    /* Handle finalization error */
}

Rationale:
Unfinalized statements can retain database locks indefinitely, preventing detachment.

8. Connection Pooling

Diagnostic Steps:

• Check for multiple connections to the same database
• Verify connection isolation levels

Solution:
Use separate connections for different database operations:

sqlite3 *temp_db;
sqlite3_open(":memory:", &temp_db);
/* Perform temp operations on temp_db */
sqlite3_close(temp_db);

Rationale:
Isolating temporary operations to separate connections prevents lock contention on the main database.

9. Write-Ahead Logging (WAL) Mode

Diagnostic Steps:

• Check current journal mode with PRAGMA journal_mode;
• Verify WAL compatibility with application requirements

Solution:
Enable WAL mode for better concurrency:

sqlite3_exec(db, "PRAGMA journal_mode=WAL;", 0, 0, 0);

Implementation Note:
WAL mode allows concurrent reads and writes, but requires proper management of WAL files during detachment.

10. Comprehensive Lock Hierarchy

Final Integration Strategy:

1. Begin immediate transaction at top level
2. Perform all data fetching and temporary database operations
3. Attach external databases
4. Execute data migration within transaction
5. Commit transaction to release locks
6. Detach external databases
7. Clean up temporary files

Sample Implementation:

char* query_template = 
"BEGIN IMMEDIATE;"
"ATTACH '%s' AS source_db;"
"CREATE TEMP TABLE migration_buffer AS SELECT * FROM source_db.data;"
"DELETE FROM main.target_table;"
"INSERT INTO main.target_table SELECT * FROM migration_buffer;"
"COMMIT;"
"DETACH source_db;";

/* Execute as single statement */
sqlite3_exec(db, query_template, 0, 0, &errmsg);

Verification Steps:

• Use PRAGMA lock_status; to confirm all locks released
• Check sqlite3_get_autocommit() returns 1 (indicating no active transaction)
• Verify temporary files are closed and removed

By systematically addressing transaction boundaries, lock acquisition order, and connection management, developers can resolve "database is locked" errors during detachment operations. The key insight is recognizing that SQLite’s transactional semantics require explicit control when mixing DDL, DML, and database connection operations across nested code structures.