How manage SQLITE_BUSY?

[Zapotecatl]

Hi, it is a question in general about sqlite3 that I couldn't find it on the forums.

My problem is when other process (other applications) try to do changes in my database, in some cases I get the SQLITE_BUSY.

I have read about this problem, I found I need to use sqlite3_busy_timeout(sqlite3*, int ms) in order to use a busy handler. I did experiments and I get less locked states in my database.

However, I have read if the cause of SQLITE_BUSY is a deadlocks or stale snapshots, the timeout will automatically return SQLITE_BUSY because there is no point in retrying (https://activesphere.com/blog/2018/12/24/understanding-sqlite-busy).

So to deal with SQLITE_BUSY due to deadlocks what I came up with is to retry with a while and let time pass with a sleep. Something like this:

bool retry = false;
do  {
       retry = false;
       ret = sqlite3_prepare_v2(...);

        if (ret == SQLITE_BUSY) {
            retry = true;
            sleep(3);
        } else {
          //Common execution
        }
} while (retry);

My question is: it is a correct way to handle the errors with SQLITE BUSY?

Or maybe there is other approach, for example, begin a transaction. Something like:

int r = sqlite3_exec(const_cast<sqlite3 *>(dbhandle), "BEGIN IMMEDIATE;", nullptr, NULL,  &e_msg);

//Execute my particular querys

int r = sqlite3_exec(const_cast<sqlite3 *>(dbhandle), "COMMIT TRANSACTION;", nullptr, NULL, &e_msg);

if happen somethin wrong (SQLITE_BUSY)
   int r = sqlite3_exec(const_cast<sqlite3 *>(dbhandle), "ROLLBACK TRANSACTION;", nullptr, NULL, &e_msg);

Any suggestion is welcome, thank you very much for taking the time!

[utelle]

Hi, it is a question in general about sqlite3 that I couldn't find it on the forums.

Usually you will be better off to direct general questions to the SQLite forum.

My problem is when other process (other applications) try to do changes in my database, in some cases I get the SQLITE_BUSY.

I have read about this problem, I found I need to use sqlite3_busy_timeout(sqlite3*, int ms) in order to use a busy handler. I did experiments and I get less locked states in my database.

Using sqlite3_busy_timeout() is the simplest approach to handling SQLITE_BUSY situations. This function implements a busy handler that simply waits a certain period of time, before retrying anything.

However, just waiting may not be the answer depending on the actual activities of the participating processes.

However, I have read if the cause of SQLITE_BUSY is a deadlocks or stale snapshots, the timeout will automatically return SQLITE_BUSY because there is no point in retrying (https://activesphere.com/blog/2018/12/24/understanding-sqlite-busy).

Right. In a deadlock situation one participant has to give up. Just waiting doesn't help. And that is what is described in the last paragraph Handling errors with busy_timeout in the blog article you referenced. BTW, this blog article is certainly one of the best sources available.

So to deal with SQLITE_BUSY due to deadlocks what I came up with is to retry with a while and let time pass with a sleep. Something like this: [...]

Hm, AFAIK this is what you can accomplish with sqlite3_busy_timeout().

My question is: it is a correct way to handle the errors with SQLITE BUSY?

If you use a busy timeout handler and still get SQLITE_BUSY at least one process has to abort/rollback its current transaction.

Or maybe there is other approach, for example, begin a transaction. Something like:

int r = sqlite3_exec(const_cast<sqlite3 *>(dbhandle), "BEGIN IMMEDIATE;", nullptr, NULL,  &e_msg);

//Execute my particular querys

int r = sqlite3_exec(const_cast<sqlite3 *>(dbhandle), "COMMIT TRANSACTION;", nullptr, NULL, &e_msg);

if happen somethin wrong (SQLITE_BUSY)
   int r = sqlite3_exec(const_cast<sqlite3 *>(dbhandle), "ROLLBACK TRANSACTION;", nullptr, NULL, &e_msg);

Yes, this is what you have to do in case of an SQLITE_BUSY error: rollback last transaction and retry it. Use this approach in conjunction with a busy timeout handler (sqlite3_busy_timeout()).