I am running into a situation with 2 threads where a busy handler is being called on one thread and unless the busy handler returns 0 and the calling code retries the query, neither thread will be able to make progress. Below is some background information:
I am working on project that uses SQLite to save records in an in memory database and periodically push them to an on-disk database. The project is multi-threaded with several threads accessing the databases (memory and on-disk) at the same time. Each thread uses its own connection to the database. We have built a layer on top of SQLite that, among other things, manages retrying when a database operation returns SQLITE_LOCKED or SQLITE_BUSY. When a transaction is first attempted, it is started with BEGIN DEFERRED TRANSACTION and when it is retried it is started with BEGIN EXCLUSIVE TRANSACTION. This is done with the intent to prevent starvation. We also register a busy handler. For a given connection the busy handler will return 1 for up to a minute and then, after 1 minute has passed return 0. If 1 minute passes it is regarded as an error. I have peeled back the layers and written some plain C++11 sample code that demonstrates the situation I am running in to. I have omitted error checking and cleanup code to make the code easier to read. The code can be found here: https://copy.com/yeT6DShawgrzg27X Also, a visualization of the callstacks can be found here: https://copy.com/kdrtb2h18vqIVrvB I have identified the following workarounds that seem to resolve this issue, but I would like to understand which is the best solution and why (I want to know specifically what rules/best practices we may be violating): - Opening the on-disk database in shared-cache mode seems to fix the problem. (We tried this since the in-memory database must be opened in shared-cache mode to share it between threads, and thought it may be good to have both using the same mode) - Repeating transactions with BEGIN DEFERRED TRANSACTION rather than BEGIN EXCLUSIVE TRANSACTION seems to fix the problem. - Not using a busy handler seems to fix the problem. Thanks, Jon
