sqlite3_step(stmt) inside a transaction fails with error 5 without my busy-handler being called

user_2563449 0
Viewed 150

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

I am using sqlite 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 working well, however I did find that in the following scenario my busy-handler is not being called:

  1. Code begins transaction (BEGIN TRANSACTION),
  2. Code working with sqlite-statement (sqlite3_prepare_v2(), sqlite3_bind_xxx()),
  3. Eventually when calling sqlite3_step() I receive the ‘database is locked’ (error 5) 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 any other applications. For this reason, I cannot see why would my busy-handler callback not get called.

My question:

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

sqlite c++ database
1 Answers

...after consulting with SQLite Forum...

The solution:

Replace the command BEGIN TRANSACTION with BEGIN IMMEDIATE TRANSACTION.

More

In sqlite there are three types of transactions:

  1. Deferred (the default)
  2. Immediate,
  3. Exclusive

If the transaction-type is not explicitly specified in the BEGIN TRANSACTION statement then the default DEFERRED transaction-type is selected.

In the DEFERRED transaction-type sqlite will not try to acquire any locks until it encounters the first command that requires this lock.

So if the transaction contained one READ command and one WRITE command, then sqlite will do:

BEGIN TRANSACTION  //  No locks acquired
    SELECT...      //  Try to acquire READ  lock
    INSERT...      //  Try to acquire WRITE lock

In multi-threading environment, if the above transaction will be executed simultaniously by two (or more) different threads then a deadlock could occur:

  1. Thread-A acquires READ lock,
  2. Thread-B acquires READ lock,
  3. Thread-A tries to acquire a WRITE lock, which can be acquired only once all READ and WRITE locks are released,
  4. Thread-B tries to acquire a WRITE lock...

This condition is a deadlock, and as sqlite documentation explains: if a potential deadlock is identified then the busy-handler will not get called as it cannot resolve this condition.

The above described how DEFERRED transaction work.

In both other cases - IMMEDIATE and EXCLUSIVE - the WRITE lock is acquired right in the beginning of the transaction, thus no risk of deadlocks:

BEGIN IMMEDIATE TRANSACTION  //  Try to acquire the WRITE lock
    SELECT...                //  ...no locks activity...
    INSERT...                //  ...no locks activity...

Since my code is using sqlite in Multi-Thread threading mode I had to replace my BEGIN TRANSACTION command with BEGIN IMMEDIATE TRANSACTION.

This solved the problem.

user_25634490
Related