Unexpected Commit Rollback in Multi-Threaded SQLite Application

SQLite Commit Rollback Due to SQLITE_BUSY in Multi-Threaded Environment

In a multi-threaded application where two threads access the same SQLite database using separate connections, an unexpected behavior can occur during commit operations. Specifically, when one thread (Thread 1) attempts to commit a transaction while another thread (Thread 2) is actively reading from the database, the commit operation may appear to succeed (returning SQLITE_OK), but the changes are not actually written to the database. Instead, the transaction is effectively rolled back, and the database file’s modification timestamp remains unchanged. This issue is particularly prevalent in SQLite versions 3.17.0 and above, and it is related to how SQLite handles the SQLITE_BUSY state during commit operations.

The root cause of this behavior lies in the interaction between the two threads and how SQLite manages locks and transactions. When Thread 1 attempts to commit a transaction, it must acquire an EXCLUSIVE lock on the database. However, if Thread 2 is holding a SHARED lock (due to an ongoing read operation), Thread 1 will be unable to acquire the EXCLUSIVE lock and will instead receive an SQLITE_BUSY error. In some cases, despite receiving SQLITE_BUSY, Thread 1 may still report a successful commit (SQLITE_DONE), leading to the illusion that the transaction was committed when, in fact, it was rolled back.

Interrupted Write Operations and SQLITE_BUSY Handling

The core issue arises from how SQLite handles the SQLITE_BUSY state during commit operations, particularly when a database connection is interrupted by another thread. In SQLite, when a connection is interrupted (e.g., via sqlite3_interrupt), the internal state of the connection may be altered in a way that affects how SQLITE_BUSY is handled. Specifically, in versions 3.17.0 and above, a change was made to reset the p->rc (return code) variable to SQLITE_OK at the beginning of the sqlite3VdbeExec function. This change, while intended as an optimization, inadvertently affects how SQLITE_BUSY is handled during commit operations.

When Thread 1 attempts to commit a transaction, it may encounter SQLITE_BUSY multiple times due to Thread 2 holding a SHARED lock. In versions prior to 3.17.0, SQLite would correctly handle this situation by retrying the commit operation until the EXCLUSIVE lock could be acquired. However, in versions 3.17.0 and above, the reset of p->rc to SQLITE_OK causes the commit operation to prematurely report success (SQLITE_DONE) even though the transaction was not actually committed. This behavior is exacerbated when the database connection is interrupted, as the interruption can further alter the internal state of the connection.

Additionally, the lack of a proper busy handler or timeout mechanism can contribute to this issue. Without a busy handler, the application may repeatedly attempt to commit the transaction without waiting for the SHARED lock to be released, leading to a high likelihood of encountering SQLITE_BUSY. If the application does not handle SQLITE_BUSY correctly (e.g., by rolling back the transaction and retrying), the transaction may be effectively rolled back without the application being aware of it.

Implementing PRAGMA busy_timeout and Proper Transaction Handling

To address this issue, several steps can be taken to ensure that commit operations are handled correctly in a multi-threaded environment. The first and most important step is to implement a busy handler or set a busy timeout using the PRAGMA busy_timeout statement. This will allow SQLite to automatically handle SQLITE_BUSY errors by waiting for a specified amount of time before retrying the operation. For example, setting a busy timeout of 5000 milliseconds (5 seconds) can be done as follows:

PRAGMA busy_timeout = 5000;

This will ensure that SQLite waits for up to 5 seconds for the SHARED lock to be released before retrying the commit operation. This approach is far more efficient than manually retrying the commit operation in the application code, as it allows SQLite to handle the retries internally.

In addition to setting a busy timeout, it is important to properly handle SQLITE_BUSY errors in the application code. When a commit operation returns SQLITE_BUSY, the application should roll back the current transaction and retry the operation after a short delay. This can be done using a loop that checks the return code of the commit operation and retries if necessary:

int retry_count = 0;
int result;
do {
    result = sqlite3_step(commit_stmt);
    if (result == SQLITE_BUSY) {
        sqlite3_exec(db, "ROLLBACK", 0, 0, 0);
        usleep(100000); // Sleep for 100 milliseconds
        retry_count++;
    }
} while (result == SQLITE_BUSY && retry_count < MAX_RETRIES);

This code will retry the commit operation up to MAX_RETRIES times if SQLITE_BUSY is encountered. If the maximum number of retries is reached, the application can either abort the transaction or notify the user of the failure.

Another important consideration is the use of sqlite3_interrupt to cancel long-running queries. While interrupting a query can be useful for improving responsiveness, it can also interfere with the internal state of the database connection, particularly in versions 3.17.0 and above. To avoid this, it is recommended to use a busy handler or timeout mechanism instead of interrupting queries. If interrupting queries is necessary, care should be taken to ensure that the interruption does not interfere with ongoing transactions.

Finally, it is important to ensure that the application is using the latest version of SQLite, as the issue described here has been fixed in version 3.33.0. If upgrading to a newer version of SQLite is not possible, the application can work around the issue by avoiding the use of sqlite3_interrupt and implementing a proper busy handler or timeout mechanism.

Summary of Solutions

By following these steps, the application can avoid the unexpected commit rollback behavior and ensure that transactions are committed correctly in a multi-threaded environment.