On 22 Aug 2015, at 2:33pm, Jon Kuhn <jonkuhn at gmail.com> wrote: > 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.
Unfortunately you're feeding one thread by starving another. SQLite has its own backoff algorithm which should prevent any situation where both threads will be locked for more than in instant. Here's the key: > - Not using a busy handler seems to fix the problem. You are doing things in your layer which defeat SQLite's locking, or which just reproduce the things SQLite would be doing automatically. For diagnostic purposes do the following: Strip out your code which manages retrying. Strip out your busy handler. Strip out anything you do to handle contention, SQLITE_BUSY or any other temporary access problems. Use 'BEGIN' for all transactions, rather than trying to specify your own DEFERRED or IMMEDIATE. Pretend you've never seen _BUSY or _LOCKED before. Set a retry period of two minutes (I'm serious: it should be long enough that your users conclude that the application has crashed and reboot it.) using one of the following: <https://www.sqlite.org/c3ref/busy_timeout.html> <https://www.sqlite.org/pragma.html#pragma_busy_timeout> It doesn't matter which one, they do the same thing. Now try your application again. Does it work better or worse ? If it's worse, look at how long you are keeping your transactions open. You should never do a BEGIN unless you have all the data ready to complete the transaction and can immediately issue all the commands needed up to and including the END. Is there something in your design which prevents that ? Simon.
