sqlite3 db opened in read-only fires SQLITE_BUSY

Question

I have some problems when accessing a sqlite3 db in read-only. On the system (Linux embedded) there is a C++ application that opens (and keeps opened) a r/w connection to a db. This application reads and writes almost continuously.

Then there is a PHP web application that opens a read-only connections every 1 sec to fetch data. It seems to have no problems at all.

Finally there is a C application that should open another read-only connection to the same db and fetch data every few seconds. This application often runs into the busy error.

As far as I understand from the help and from the other questions on this topic, this is usually fired when you try to write and there's another write running transaction. The help also say "or in some cases read" but I cannot figure out which are these cases.

Trying to read when the C++ application is still writing may happen, but as far as I know this should not lead to this error. The read query will return the "old" values but should complete without problems.

Is it correct?

Here the C code that returns this error:

sqlite3 *db;
sqlite3_stmt *stmt;
char *qry=0;
int rc,value=0;

sqlite3_open_v2( "<mydb>", &db,SQLITE_OPEN_READONLY,NULL);
if (db == NULL)
{
    syslog(LOG_INFO,"Failed to open db\n");
    return -1;
}

qry=malloc(80);

if (qry) {
    sprintf (qry,"select value from dataplc WHERE address=%d",addr);

    rc = sqlite3_prepare_v2(db, qry,-1,&stmt,NULL);
    if (rc != SQLITE_OK) {
        syslog(LOG_INFO, "Failed to read: %d - rc: %d", addr,rc); // -5 -> db busy
        value = -1;
    } else {
        rc = sqlite3_step(stmt);
        if (rc == SQLITE_ROW) {
           value = sqlite3_column_int(stmt, 0);
           // do something
        }
    }
    free (qry);
}
sqlite3_finalize(stmt);   
sqlite3_close(db);
return value;

Is there something obviously wrong in either the code or the whole approach?

Answer 1

Presumably the prepare needs an exclusive access to read the current schema data of the db.

One possible solution would be to use a sqlite3_busy_handler, e.g.

#define ABORT 0
#define CONTINUE 1

int busy_handler(void *data, int attempt) {
    printf("attempt: %d\n", attempt);
    if(attempt < 10) {
        sqlite3_sleep(250);
        return CONTINUE;
    }
    return ABORT;
}

and set it before the call of sqlite3_prepare_v2 like so:

sqlite3_busy_handler(db, busy_handler, NULL);

The maximum number of retries and sleep time (in milliseconds) should be determined based on your requirements.

Like it was mentioned already in comments it makes sense to check the return code of sqlite3_open_v2 call.

Presumably it would also make sense to use the prepared statement like this:

rc = sqlite3_prepare_v2(db, "SELECT value FROM dataplc WHERE address = ?1", -1, &stmt, NULL);

and bind the parameter with:

sqlite3_bind_int(stmt, 1, addr);

How to Test

For testing one could use a trick that can be found in this fine answer: https://stackoverflow.com/a/57786662/2331445:

One could simply set the sleep time for example temporarily to 1 second (in busy_handler sqlite3_sleep(1000);).

Then add a getchar(); just before prepare.

You program just slightly modified regarding the above mentioned points would look like:

#include <stdio.h>
#include <sys/syslog.h>
#include "sqlite3.h"

#define ABORT 0
#define CONTINUE 1

int busy_handler(void *data, int attempt) {
    printf("attempt: %d\n", attempt);
    if (attempt < 10) {
        sqlite3_sleep(1000);
        return CONTINUE;
    }
    return ABORT;
}

int read_from_db(int addr) {
    sqlite3 *db;
    sqlite3_stmt *stmt;
    int rc, value = 0;

    rc = sqlite3_open_v2("mydb", &db, SQLITE_OPEN_READONLY, NULL);
    if (rc != SQLITE_OK) {
        sqlite3_close(db);
        syslog(LOG_INFO, "Failed to open db\n");
        return -1;
    }

    sqlite3_busy_handler(db, busy_handler, NULL);

    printf("press enter to continue:\n");
    getchar(); //only for testing

    rc = sqlite3_prepare_v2(db, "SELECT value FROM dataplc WHERE address = ?1", -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        syslog(LOG_INFO, "Failed to read: %d - rc: %d", addr, rc); // -5 -> db busy
        value = -1;
    } else {
        sqlite3_bind_int(stmt, 1, addr);

        rc = sqlite3_step(stmt);
        if (rc == SQLITE_ROW) {
            value = sqlite3_column_int(stmt, 0);
            // do something
        }
    }

    sqlite3_finalize(stmt);
    sqlite3_close(db);
    return value;
}


int main(void) {
    int val = read_from_db(42);
    printf("result: %d\n", val);
    return 0;
}

On the sqlite3 command line interface one could enter:

begin exclusive;

Then on console where the program runs you could press ENTER key. It will now show the printfs from the busy_handler. Once we release the database with a

commit;

the result is returned.