SQLite ↔ Application Threads: Interoperability of sqlite3_interrupt and Busy Timeout for Graceful Cancellation

Cancellation Behavior During Lock Waits

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.

Interoperability with Busy Timeout

The sqlite3_busy_timeout() mechanism and sqlite3_interrupt() operate on different layers of the execution flow:

• Busy Timeout: Implements a polling loop (a busy-handler) that sleeps and retries lock acquisition until the timeout expires.
• Interrupt: Sets a flag in the database connection structure that is checked frequently by the SQLite engine, including during the sleep/retry cycles of the busy-handler.

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.

Implementation and Verification

To ensure graceful cancellation in a multi-threaded environment, follow these steps:

1. Initialize Connection: Set your desired sqlite3_busy_timeout(db, ms) to handle transient contention.
2. Execute Operation: Start the long-running query or write operation in Thread A.
3. Trigger Cancellation: Call sqlite3_interrupt(db) from Thread B.
4. Handle Result: In Thread A, check the return code. If it is SQLITE_INTERRUPT, perform necessary cleanup.

Technical Assumptions and Constraints

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.