Issue Overview: SQLITE_BUSY and SQLITE_LOCKED Error Code Differentiation

The error message "database is locked" accompanied by the code = Busy (5) in System.Data.SQLite.SQLiteException indicates a SQLITE_BUSY condition, not SQLITE_LOCKED. While both errors relate to database or table locking conflicts, their root causes, implications, and resolutions differ significantly. SQLITE_BUSY (error code 5) occurs when a database connection attempts to modify the database or acquire a write lock while another connection holds a conflicting lock. SQLITE_LOCKED (error code 6), on the other hand, arises when a specific table or resource within the database is exclusively reserved by another operation, often during schema modifications or internal lock escalations.

The confusion between these errors stems from their similar terminology and overlapping contexts involving locks. However, SQLite’s internal error messaging distinguishes them explicitly:

• SQLITE_BUSY (5): Returns the message "database is locked".
• SQLITE_LOCKED (6): Returns the message "database table is locked".

This distinction is embedded in SQLite’s C source code, where error strings are mapped to numeric codes. For example, the SQLite function sqlite3_errstr(5) returns the "database is locked" message, while sqlite3_errstr(6) returns "database table is locked". Wrappers like System.Data.SQLite propagate these messages and codes directly from the core SQLite library, ensuring consistency across implementations.

The SQLITE_BUSY error typically occurs in multi-threaded or multi-process environments where concurrent write operations collide. For instance, if Connection A holds a RESERVED lock during an active transaction and Connection B attempts a write operation, Connection B will receive SQLITE_BUSY. SQLITE_LOCKED is less common and often involves internal lock contention, such as when a prepared statement attempts to modify a table that another operation is actively restructuring.

Possible Causes: Concurrency Conflicts and Lock State Mismanagement

1. Concurrent Write Operations Without Proper Isolation
SQLite uses a file-based locking mechanism to manage concurrency. When multiple connections attempt to write to the database simultaneously, SQLITE_BUSY errors occur if the required locks cannot be acquired within the timeout period. For example, a web application with poor connection pooling might spawn multiple threads that execute write transactions without serialization, leading to lock contention.

2. Long-Running Transactions Holding Locks Open
A transaction that remains open for an extended period (e.g., a bulk insert operation without explicit commits) retains locks on the database, blocking other connections. This is a common cause of SQLITE_BUSY errors in applications that do not follow the principle of "transaction brevity."

3. Schema Modifications and Internal Lock Escalation
SQLITE_LOCKED errors often surface during schema changes, such as ALTER TABLE or CREATE INDEX, where SQLite requires exclusive access to specific internal structures. If a connection holds a read lock on a table while another attempts to modify its schema, the latter may receive SQLITE_LOCKED.

4. Misconfigured Busy Timeout or Retry Logic
The default behavior of SQLite is to immediately return SQLITE_BUSY when a lock is unavailable. If the busy_timeout pragma is not set (e.g., PRAGMA busy_timeout=5000;), or if the application lacks retry logic for busy conditions, errors will propagate to the user.

5. Shared Cache Mode or Write-Ahead Logging (WAL) Misuse
In shared cache mode, multiple connections within the same process share a common page cache. While this can reduce memory overhead, it increases the likelihood of SQLITE_LOCKED errors due to granular lock management. Similarly, incorrect configuration of WAL mode (e.g., mixing WAL with other journal modes across connections) can lead to unexpected locking behavior.

Troubleshooting Steps, Solutions & Fixes: Resolving Lock Contention and Configuring Retries

Step 1: Confirm the Error Code and Message
Verify the numeric error code and message text to distinguish between SQLITE_BUSY and SQLITE_LOCKED. In System.Data.SQLite, inspect the SQLiteErrorCode property or parse the exception message:

try {  
    // Execute database operation  
} catch (SQLiteException ex) {  
    if (ex.ResultCode == SQLiteErrorCode.Busy) {  
        // Handle SQLITE_BUSY (5)  
    } else if (ex.ResultCode == SQLiteErrorCode.Locked) {  
        // Handle SQLITE_LOCKED (6)  
    }  
}  

Step 2: Implement Busy Timeout and Exponential Backoff Retry Logic
Configure a busy handler to automatically retry operations after a short delay. In raw SQLite, use sqlite3_busy_timeout() or the busy_timeout pragma. In applications, implement retry loops with exponential backoff:

int maxRetries = 5;  
int retryDelayMs = 100;  
for (int attempt = 0; attempt < maxRetries; attempt++) {  
    try {  
        // Execute database operation  
        break;  
    } catch (SQLiteException ex) when (ex.ResultCode == SQLiteErrorCode.Busy) {  
        if (attempt == maxRetries - 1)  
            throw;  
        Thread.Sleep(retryDelayMs);  
        retryDelayMs *= 2; // Exponential backoff  
    }  
}  

Step 3: Optimize Transaction Scope and Lock Acquisition Order
Minimize the duration of transactions by committing immediately after write operations. Use BEGIN IMMEDIATE or BEGIN EXCLUSIVE transactions to acquire write locks upfront, reducing the window for contention:

BEGIN IMMEDIATE;  
-- Perform writes  
COMMIT;  

Step 4: Enable Write-Ahead Logging (WAL) Mode
WAL mode allows readers and writers to coexist more gracefully by separating write operations into a separate log file. Enable it with:

PRAGMA journal_mode=WAL;  

Note that all connections must use WAL for consistent behavior.

Step 5: Avoid Schema Changes During High Concurrency
Schedule schema modifications during maintenance windows or low-activity periods. If schema changes are unavoidable, ensure all other transactions are closed and retry logic is in place for SQLITE_LOCKED.

Step 6: Monitor and Analyze Locking Behavior
Use tools like sqlite3_db_status() or logging to identify long-held locks. On Unix systems, the fuser or lsof commands can show which processes have the database file open.

Step 7: Review Connection Pooling and Thread Safety
Ensure database connections are properly pooled and threads do not share connections without synchronization. Configure connection pools to limit the number of concurrent writers.

Step 8: Handle SQLITE_LOCKED with Statement Reset and Retry
SQLITE_LOCKED often requires re-preparing statements. In the SQLite C API, call sqlite3_reset() on the prepared statement and retry. In higher-level wrappers, recreate the command or connection.

By systematically addressing lock contention through configuration, transaction management, and retry logic, most SQLITE_BUSY and SQLITE_LOCKED errors can be mitigated or resolved entirely.