SQLite Forum

When using sqlite3_step(stmt) inside a transaction my busy-handler is not being called
Login

When using sqlite3_step(stmt) inside a transaction my busy-handler is not being called

(1) By PazO (JohnSmith) on 2020-07-14 14:21:56 [link] [source]

[sqlite version 3.28.0 (2019-04-16)]

I am using sqlite3 in a multi-threaded application.

Sqlite threading-mode is configured for multi-threading (compilation flag: SQLITE_THREADSAFE=2).

In accordance to that each thread is using its own database connection.

In addition, since the multiple connections can access the database simultaneously and in order to avoid the ‘database is locked’ (error 5) I implement a busy-handler callback and assign it to the connection using sqlite3_busy_handler() right after connection creation.

Generally speaking, all is functioning well, however I did find that in some conditions my busy-handler is not being called. For example, in the next scenario:

  1. Code begins transaction (“BEGIN TRANSACTION;”),
  2. Code working with sqlite-statement (sqlite3_prepare_v2(), sqlite3_bind_xxx()),
  3. Eventually when I call sqlite3_step() I receive the ‘database is locked’ error without my busy-handler ever being called.

I know that in sqlite documentation it says:

"...The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY..."

However, my condition is a simple stand-alone application that does not share the database with other applications. For this reason, I cannot see why the above scenario will not call my call-back. I can only assume this is a bug in sqlite (?).

Workaround: "BEGIN IMMEDIATE TRANSACTION;"

I found that when using IMMEDIATE transaction my callback is being called and all seems to work, but I am not sure on how solid this solution is, and whether this problem could arise elsewhere.

My question: Is there a way to configure sqlite to always call my busy-handler callback?

(2) By Clemens Ladisch (cladisch) on 2020-07-14 21:07:03 in reply to 1 [link] [source]

my condition is a simple stand-alone application that does not share the database with other applications

What SQLite cares about is that there are multiple connections.

Is there a way to configure sqlite to always call my busy-handler callback?

And what would your busy handler do? In case of a deadlock, waiting would not help.

I found that when using IMMEDIATE transaction my callback is being called and all seems to work, but I am not sure on how solid this solution is

A deadlock happens when two read-only transactions want to upgrade to read/write. BEGIN IMMEDIATE starts the transaction as read/write.

(3) By PazO (JohnSmith) on 2020-07-15 04:16:28 in reply to 2 [source]

Thank you for this relply.

Per your points:

What SQLite cares about is that there are multiple connections.

Yes, there are multiple-connections per each thread.

And what would your busy handler do? In case of a deadlock, waiting would not help

My busy handler will always return 1 (keep on trying), but every 30 repetitive retries it will sleep 10 ms.

A deadlock happens when two read-only transactions want to upgrade to read/write

This is very helpfull information.

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

In any case - if my busy handler is not being called it means that I cannot use this DEFFERED transaction, and alwys alternate to IMMEDIATE transaction...

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

Thank you again for this reply

(4) By anonymous on 2020-07-15 04:53:28 in reply to 3 [link] [source]

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

As was mentioned above, a deadlock (as in, a permanent deadlock that cannot be resolved no matter how long you wait) will occur when two read connections/transactions both try to write to the database. Consider the following scenario:

  1. Connection A starts a (deferred) transaction. It starts with no database locks.
  2. Connection B does the same. It too starts wit no database locks.
  3. Connection A reads from the database to check if a particular record of table Foo exists. Consequently, it upgrades to a shared lock.
  4. Connection B does the same. It also upgrades to a shared lock. (Note that any number of connections may hold shared locks in parallel.)
  5. Connection A writes the missing record to the database. Thus it upgrades to a reserve lock. But it must wait for all pending shared locks (i.e., the one held by connection B) to be released before proceeding with the write.
  6. Connection B tries to write the missing record as well. But it cannot upgrade to a reserve lock, because connection A already has one.

At this point, we have a deadlock - connection A is waiting for connection B to release its shared lock, and connection B is waiting for connection A to release its reserve lock. No amount of waiting will ever resolve this. SQLite detects the situation, and immediately returns SQLITE_BUSY to connection B rather than bothering with its busy handler.

Note that the above example describes what happens when using a rollback journal. With write-ahead logging, you don't have a deadlock issue per se, but since both writes cannot be allowed to proceed without violating the isolation property of ACID, it still fails anyway.

In any case - if my busy handler is not being called it means that I cannot use this DEFFERED transaction, and alwys alternate to IMMEDIATE transaction...

If your transaction will only be reading, you can use BEGIN DEFERRED without issue. However, if you transaction will be writing to the database at all, it is best to use BEGIN IMMEDIATE to avoid this issue.

(5) By Keith Medcalf (kmedcalf) on 2020-07-15 05:56:39 in reply to 3 [link] [source]

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.

(6) By PazO (JohnSmith) on 2020-07-15 07:14:04 in reply to 5 [link] [source]

Why are you using a custom busy handler?

Initially I did try to use the default handler but I kept on receiving the 'database is locked' error immediately when multi-threads were trying to access the database simultaneously (using different connections of course).

The particular deadlock situation you are seeing is a "circular wait"

Thank you for this explanation. I understand it now.

[my quote] Are we aware of any more such cases where sqlite is not calling the busy handler?

I meant, are there known cases, like the one discussed here - DEFERRED TRANSACTION, and the one documented in sqlite - multiple processes.

(7) By Keith Medcalf (kmedcalf) on 2020-07-15 11:37:26 in reply to 6 [link] [source]

No problem.

I meant, are there known cases, like the one discussed here - DEFERRED TRANSACTION, and the one documented in sqlite - multiple processes.

There should not be. The only other place you may see one is if you try to DROP a table that is in use in the same transaction as the DROP command is issued. For example, if you do:

BEGIN TRANSACTION;
SELECT * FROM x;   -- but do not read until SQLITE_DONE is returned or otherwise reset the statement
DROP TABLE x;      -- this will return a LOCKED/BUSY error because you cannot drop a table out from underneath an executing statement
COMMIT;            -- this will not take effect until you finish the SELECT, the read lock will be kept until you reset the SELECT

(8) By PazO (JohnSmith) on 2020-07-15 13:41:23 in reply to 7 [link] [source]

Thank you very much for this information.

This has been great help!

(9) By Keith Medcalf (kmedcalf) on 2020-07-15 14:38:33 in reply to 8 [link] [source]

You can find a good description of how the locking process works in SQLite3 from this page, which describes the standard/default "delete" journal mode. Where the description says "process" think "connection" as the arbitration is between database connections -- you can have multiple connections in the same process or multiple processes with a connection each, for example, and there is really no difference.

https://sqlite.org/lockingv3.html

Locking in databases that are using Write-Ahead-Logging is a little bit different and is described here:

https://sqlite.org/wal.html