[sqlite] Stuck in busy handler

[Simon Slavin]

On 22 Aug 2015, at 2:33pm, Jon Kuhn  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:




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.

[sqlite] Stuck in busy handler

[Jon Kuhn]

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