SQLite Database Locked on ATTACHED Database After SELECT in Transaction

Understanding Lock Behavior During Multi-Database Transactions

Issue Overview: Lock Escalation on ATTACHED Databases During SELECT Queries

The core issue revolves around SQLite’s locking mechanism when executing SELECT queries involving an ATTACHed database during an active transaction. The problem manifests as a “database is locked” error on the attached database (e.g., no_trans_db.sqlite) when attempting to write to it from a separate connection while a transaction is open on the main database connection. This behavior contradicts the expectation that read-only operations (like SELECT) on an attached database should not escalate locks beyond the duration of the query itself.

Key Observations

1. Lock Retention After SELECT: When a SELECT query joins tables from both the main and attached databases, SQLite acquires a SHARED lock on the attached database. This lock persists until the transaction on the main connection is committed or rolled back.
2. Multi-Database Transaction Scope: SQLite treats all databases attached to a connection as part of a single transactional scope. Any read or write operation within an open transaction implicitly extends locks to all involved databases.
3. ACID Compliance and Isolation: SQLite enforces strict isolation guarantees (ACID’s “I”). Once a transaction reads from a database, it must hold locks to ensure the data remains consistent until the transaction concludes.

Reproduction Scenario
The issue is reproducible using two SQLite CLI instances:

1. Main Connection:

   .open main_db.sqlite
   ATTACH 'no_trans_db.sqlite' AS no_trans;
   BEGIN TRANSACTION;
   INSERT INTO main_table VALUES (2, 'b');
   SELECT ... FROM main_table INNER JOIN no_trans_table ...; -- Locks no_trans_db.sqlite
   

2. Second Connection:

   .open no_trans_db.sqlite
   INSERT INTO no_trans_table VALUES (2, 'y'); -- Fails with "database is locked"
   

After the SELECT on the main connection, the attached database (no_trans) retains a SHARED lock, blocking writes from other connections.

Underlying Mechanism
SQLite’s locking model operates as follows:

• SHARED Lock: Acquired during reads. Multiple connections can hold SHARED locks simultaneously.
• RESERVED Lock: Acquired when a write is anticipated (e.g., INSERT, UPDATE).
• EXCLUSIVE Lock: Required to commit changes.

In the default rollback journal mode, a SHARED lock on an attached database is retained for the duration of the transaction to ensure that the data read during the transaction remains consistent. This is critical for isolation but conflicts with workflows where an attached database is expected to remain writable by other connections.

Possible Causes: Why Locks Escalate Beyond Expectations

1. Transaction Scope Spanning Multiple Databases
   When a transaction is open, SQLite treats all attached databases as part of a single atomic unit. Even if only one database is modified, any read operation on an attached database extends transactional locks to it. This is intentional to maintain consistency but causes unintended lock retention.

2. Query Planner Behavior
   Complex queries (e.g., joins with subqueries like NOT EXISTS) may force SQLite to create temporary structures (e.g., auto-indexes) that require persistent access to the attached database. These structures are tied to the transaction’s lifespan, preventing lock release.

3. Journal Mode Limitations
   In rollback journal mode (default), SHARED locks are held until transaction completion. In WAL mode, readers and writers can coexist more freely, but improper configuration or mixed journal modes between databases can lead to inconsistencies.

4. Connection Management Flaws
   Using a single connection for both transactional and non-transactional operations conflates lock scopes. Separate connections are not truly isolated if they share attached databases.

5. Misunderstanding of SQLite’s Locking Semantics
   Developers often assume that read operations release locks immediately after execution. However, SQLite’s ACID compliance requires locks to persist until transaction boundaries to prevent phantom reads or inconsistent states.

Troubleshooting Steps, Solutions, and Fixes

Step 1: Diagnose Lock Status with PRAGMA lock_status
Compile SQLite with -DSQLITE_DEBUG=1 to enable the lock_status pragma. This reveals real-time lock levels:

PRAGMA lock_status;
-- Output after SELECT:
-- main     → reserved
-- no_trans → shared

This confirms that the attached database retains a SHARED lock during the transaction.

Step 2: Switch to WAL Journal Mode
Enable Write-Ahead Logging (WAL) for both databases:

PRAGMA journal_mode=WAL;

Why This Works:

• Readers (SHARED locks) do not block writers (EXCLUSIVE locks) in WAL mode.
• Writers can proceed even if a reader holds a SHARED lock.
• The transaction on the main connection will see a snapshot of the attached database at the transaction’s start, while other connections can write freely.

Caveats:

• WAL mode requires all connections to use the same journal mode.
• Not suitable for network file systems (e.g., NFS).

Step 3: Decouple Transactional and Non-Transactional Operations
Use separate connections for operations involving the attached database:

• Main Connection: Handles transactions on the primary database.
• Secondary Connection: Executes SELECT queries on the attached database without an active transaction.

Example Workflow:

1. Main Connection:

   BEGIN TRANSACTION;
   INSERT INTO main_table ...;
   -- Do NOT query attached databases here
   COMMIT;
   

2. Secondary Connection:

   SELECT ... FROM main_table INNER JOIN no_trans_table ...;
   -- Locks released immediately after query
   

Step 4: Use ATTACH WITH NOLOCK or Read-Only Mode
Attach the database in read-only mode or with nolock=1 to bypass locks:

ATTACH 'file:no_trans_db.sqlite?nolock=1' AS no_trans;

Caution:

• nolock=1 disables file locking entirely. Use only if writes are strictly serialized externally.
• Read-only mode (mode=ro) prevents writes but does not eliminate SHARED locks.

Step 5: Restructure Queries to Avoid Joins
If possible, refactor queries to avoid cross-database joins. For example:

1. Fetch keys from the main database first.
2. Use batched queries to retrieve data from the attached database.

Example:

-- Main connection (transactional)
SELECT id FROM main_table WHERE ...;
-- Secondary connection (non-transactional)
SELECT * FROM no_trans_table WHERE id IN (...);

Step 6: Implement Connection Pooling with Isolation
Use a connection pool where:

• One pool handles transactional operations (main database).
• Another pool handles non-transactional reads (attached databases).
  Ensure connections in the second pool never have open transactions.

Step 7: Upgrade SQLite and Verify Behavior
While the issue is not version-specific, newer SQLite versions (≥3.37.0) offer improved concurrency in WAL mode. Verify behavior with:

sqlite3 --version

Step 8: Monitor and Adjust Timeouts
Increase the busy timeout to reduce contention:

PRAGMA busy_timeout = 30000; -- 30 seconds

This allows retries before returning “database is locked” errors.

Step 9: Avoid Long-Running Transactions
Minimize the duration of transactions to reduce lock retention windows. For batch operations, commit in chunks:

BEGIN;
INSERT 1000 rows;
COMMIT;
BEGIN;
INSERT next 1000 rows;
COMMIT;

Step 10: Validate Application Logic for ACID Requirements
Reassess whether the application truly requires cross-database transactional consistency. If not, relax isolation requirements by:

• Using READ UNCOMMITTED isolation (not natively supported; emulate via WAL).
• Decoupling databases logically.

Final Recommendations
The locking behavior observed is not a bug but a deliberate design choice by SQLite to enforce ACID guarantees. To resolve the conflict:

1. Adopt WAL Mode: This is the most effective solution for concurrent read/write access.
2. Isolate Connections: Use separate connections for transactional and non-transactional operations.
3. Refactor Queries: Eliminate cross-database joins within transactions.

By aligning the application’s concurrency model with SQLite’s locking semantics, the “database is locked” errors can be systematically eliminated.