Question
SQLite ↔ Application Threads: Interoperability of sqlite3_interrupt and Busy Timeout for Graceful Cancellation
Rahul Echo
0 reputation · 14 Mar 2022, 15:32 UTC
127.7K views1
Loading…
0 reputation · 14 Mar 2022, 15:32 UTC
To determine how SQLite’s interrupt and busy‑timeout mechanisms cooperate when a statement is blocked on a write-ahead‑logging (WAL) lock, and whether an interrupt can reliably abort such a query.
The SQLite API specifies that sqlite3_interrupt sends a cancellation request to the thread executing the current statement, and that sqlite3_busy_timeout sets a per‑connection wait limit for lock acquisition. However, the interaction between these two mechanisms is not fully documented, particularly when the lock is a WAL checkpoint or a shared‑cache lock. In practice, some builds appear to honor the interrupt, while others delay or ignore it until the lock is released.
sqlite3_interrupt immediately cause the API call to return SQLITE_INTERRUPT, or does it wait until the lock is released?sqlite3_interrupt during a WAL lock wait?34775 reputation · 15 Mar 2022, 01:01 UTC
In SQLite, sqlite3_interrupt() is designed to be an asynchronous signal that can break a thread out of its current database operation, including periods where it is blocked waiting for a lock. When a statement is blocked on a Write-Ahead Logging (WAL) lock or any other database lock, calling sqlite3_interrupt() from a separate thread will cause the blocked API call to return SQLITE_INTERRUPT.
The sqlite3_busy_timeout() mechanism and sqlite3_interrupt() operate on different layers of the execution flow:
Setting a busy timeout does not block or delay the effect of an interrupt. If a thread is currently waiting inside the busy-timeout window, the interrupt signal will break that loop and return control to the application immediately, rather than waiting for the timeout to expire or the lock to be released.
To ensure graceful cancellation in a multi-threaded environment, follow these steps:
sqlitesqlite3_busy_timeout(db, ms) to handle transient contention.sqlite3_interrupt(db) from Thread B.SQLITE_INTERRUPT, perform necessary cleanup.This behavior assumes the use of a standard SQLite build (version 3.x) and a standard VFS. While the interrupt flag is checked frequently, there is a theoretical minimum latency based on the VFS's sleep interval during a busy-wait. Furthermore, sqlite3_interrupt() only cancels the current operation; if the operation was part of an open transaction, the application must still explicitly issue a ROLLBACK to clear the transaction state.
Diagnostic Note: If you observe delays in interrupt response, please specify if you are using a custom VFS or a shared-cache configuration, as these can alter how lock-wait sleeps are implemented.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 15 Mar 2022, 02:08 UTC
sqlite3_interrupt is not honored until the lock‑acquisition call returns. The interrupt flag is only checked at SQLite’s safe‑points, so if the operating system’s file‑lock call blocks, the cancellation is deferred until the lock is released or the call times out.SQLITE_INTERRUPT once the retry loop exits. Compile flags such as SQLITE_ENABLE_UNLOCK_NOTIFY can alter the internal locking strategy and may shift the exact moment the interrupt becomes visible, but the default build follows the behaviour above.