SQLite Forum

When using sqlite3_step(stmt) inside a transaction my busy-handler is not being called
Login
Why are you using a custom busy handler since it sounds like it is doing pretty much what the built-in busy handler does?

 > I understand the lock, but why deadlock? Wouldn't calling my busy handler resolve this lock?

No.  This is why the busy_handler is not called because it would provide no benefit.  This is also known colloquially as a "Mexican Standoff" or "Deadly Embrace".

<https://en.wikipedia.org/wiki/Mexican_standoff>  
<https://en.wikipedia.org/wiki/Deadlock>

The particular deadlock situation you are seeing is a "circular wait".  When you issue the "BEGIN DEFERRED" on a connection, that connection obtains a READ lock on the database which it will hold until the transaction is ended -- this prevents other connections from COMMITting an update to the database.  When when a connection INTENDS to WRITE it must first obtain an INTENT lock.  In order to actually COMMIT the transaction, an EXCLUSIVE lock is required (meaning all the READ locks must be released).

So if you have multiple access, two connections may request READ locks.  This is OK.  One of them may then request an INTENT lock, which is OK.  Eventually it will require an EXCLUSIVE lock (all other READ locks must be released) so it can actually update the database.  Presumably this will eventually occur.

However, if after connection 1 has obtained an INTENT lock, another connection 2 requests an INTENT lock, that lock cannot ever be granted because connection 2 already holds a READ lock, which will prevent the first connection from being able to complete its operations.  This situation will not change no matter how long the connection 2 waits for its INTENT lock, because connection 1 cannot proceed and release the INTENT lock until connection 2 releases its READ lock, which it cannot do because is cannot proceed without the INTENT lock.

So eventually you will have each connection waiting for the other and neither capable of making any progress until the situation is resolved by external means (such as a third party intervening and shooting one of the deadlocked participants in the head, thus allowing the other one to proceed).

SQLite3 detects this situation and refuses to permit connection 2 to wait for resources that will never become available by returning SQLITE_BUSY (the equivalent of a third-party shooting the newcomer in the head), thus preventing the deadlock from occurring (this is detection and prevention of the occurrence of a circular wait condition).

Using BEGIN IMMEDIATE fixes this situation because it causes the initial lock acquired to be an INTENT lock.  This means that a "circular wait" is quite impossible (by removing the "hold and wait" Coffman condition).

Note that in order to be effective, when a statement returns SQLITE_BUSY you *must* release any held locks (ROLLBACK) and start over again.  Any information that you have read from the database is also no longer valid and you must re-read it after restarting a new transaction.

 > Are we aware of any more such cases where sqlite is not calling the busy handler?

If there was a case where SQLite3 was not invoking a configured busy handler when it ought to be doing so that would be a *bug* and should be fixed, so if you find one then you should report it.  

In this case, however, it is likely an intended deadlock resolution response to the detection of an incipient "circular wait" condition -- the evidence of this is that preclusion of the "hold and wait" Coffman condition by using BEGIN IMMEDIATE resolves the observed behaviour.