SQLite in C++. DB is BUSY (Multithread)

Question

I've got a problem. I'm using SQLite3 in my C++ project. In the log, I've got errors: DB is locked error code 5. As I know, error code 5 means that DB is busy. To solve this, I started to use WAL journal mode. But it doesn't help.

In my program, I've got 2 connections to the same DB. I use mutexes for both DB connections. I'm opening connections with this code:

if (sqlite3_open_v2(db_path.c_str(), &this->db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX, 0) ) {
    LOG4CPLUS_FATAL(this->logger, "Can not open/create DB " << sqlite3_errmsg(db));
    sqlite3_close(this->db);
}

if (sqlite3_exec(this->db, "PRAGMA journal_mode = WAL;", 0, 0, &err)) {
    LOG4CPLUS_ERROR(this->logger, "SQL det journal mode error: " << err);
    sqlite3_free(err);
}

The first connection is used for inserting data to the DB. It happens 4 times every second.
The second connection is used for starting transaction, selecting, updating, deleting data, and committing. It happens every 5 seconds.

I'm getting errors from the first connection.

Please help me to solve this problem.

Update:

First connection:

void readings_collector::flushToDb()
{
    this->db_mutex.lock();

    LOG4CPLUS_DEBUG(this->logger, "Flush to DB start.");

    const char *query = "INSERT INTO `readings` (`sensor_id`, `value`, `error`, `timestamp`) VALUES (?,?,?,?)";
    sqlite3_stmt *stmt = NULL;

    int rc = sqlite3_prepare_v2(this->db, query, -1, &stmt, NULL);
    if (SQLITE_OK != rc) {
        LOG4CPLUS_ERROR(this->logger, "sqlite prepare insert statment error: " << sqlite3_errmsg(this->db));
    }

    LOG4CPLUS_TRACE(this->logger, "--------------------");
    LOG4CPLUS_TRACE(this->logger, this->readings.size());

    while(!this->readings.empty()) {
        sensor::reading temp_reading = this->readings.front();
        this->readings.pop();

        LOG4CPLUS_TRACE(this->logger, "Reading " << temp_reading.sensor_id << " : " << temp_reading.value << " : " << temp_reading.error << " : " << temp_reading.timestamp);

        sqlite3_clear_bindings(stmt);

        sqlite3_bind_int(stmt, 1, temp_reading.sensor_id);
        sqlite3_bind_text(stmt, 2, temp_reading.value.c_str(), sizeof(temp_reading.value.c_str()), NULL);
        sqlite3_bind_int(stmt, 3, temp_reading.error);
        sqlite3_bind_int(stmt, 4, temp_reading.timestamp);

        rc = sqlite3_step(stmt);
        if (SQLITE_DONE != rc) {
            LOG4CPLUS_ERROR(this->logger, "sqlite insert statment exec error: " << sqlite3_errmsg(this->db) << "; status: " << rc);
        }
    }
    sqlite3_finalize(stmt);

    LOG4CPLUS_TRACE(this->logger, "Flush to DB finish.");

    this->db_mutex.unlock();
}

Second connection:

void dataSend_task::sendData()
{
    this->db_mutex.lock();

    char *err = 0;

    LOG4CPLUS_INFO(this->logger, "Send data function");

    if (sqlite3_exec(this->db, "BEGIN TRANSACTION", 0, 0, &err)) {
        LOG4CPLUS_ERROR(this->logger, "SQL exec error: " << err);
        sqlite3_free(err);
    }

    if (sqlite3_exec(this->db, this->SQL_UPDATE_READINGS_QUERY, 0, 0, &err)) {
        LOG4CPLUS_ERROR(this->logger, "SQL exec error: " << err);
        sqlite3_free(err);
    }

    this->json.clear();
    this->readingsCounter = 0;

    if (sqlite3_exec(this->db, this->SQL_SELECT_READINGS_QUERY, +[](void *instance, int x, char **y, char **z) {
                         return static_cast<dataSend_task *>(instance)->callback(0, x, y, z);
                     }, this, &err)) {

        LOG4CPLUS_ERROR(this->logger, "SQL exec error: " << err);
        sqlite3_free(err);
    } else {
        LOG4CPLUS_TRACE(this->logger, "Json data:  " << this->json);

        if (this->curlSend()) {
            if (sqlite3_exec(this->db, this->SQL_DELETE_READINGS_QUERY, 0, 0, &err)) {
                LOG4CPLUS_ERROR(this->logger, "SQL exec error: " << err);
                sqlite3_free(err);
            }
        }
    }
    if (sqlite3_exec(this->db, "COMMIT", 0, 0, &err)) {
        LOG4CPLUS_ERROR(this->logger, "SQL exec error: " << err);
        sqlite3_free(err);
    }

    this->db_mutex.unlock();

    this->json.clear();
}

Could you share more of your code? It would definitely help narrow the bug. — Krystian Sakowski, Aug 10 '15 at 00:14
Are the connections being made from two different threads? Or two different processes? — harmic, Aug 10 '15 at 10:36
Two different threads. And both connections is used from other threads. — Dm3Ch, Aug 10 '15 at 11:09

score 2 · Accepted Answer · answered Aug 11 '15 at 01:16

As you've no doubt realized, SQLite only allows one connection at a time to be be updating the database.

From the code you have pasted, it looks as though you have two separate mutexes, one for the readings_collector instance, another for the dataSend_task instance. These would protect against multiple executions of each of the two functions but not against both of those functions running at once.

It wasn't that clear from your question what the purpose of the mutexes is, but it certainly isn't going to prevent both of those connections from simultaneously trying to update the database.

I can suggest two approaches to fix your problem.

The first would be to use a single shared mutex between those two instances, so that only one of them at a time can be updating the database.

The second would be to take advantage of the facilities SQLite provides for resolving contention when accessing the database. SQLite allows you to install a 'busy handler' which will be called in the event that an attempt is made to access a database which is already locked by another thread or process. The busy handler can take whatever action is desired, but the simplest case is normally just to wait a while and try again, which is catered for by the built in busy handler which you can install by calling sqlite3_busy_timeout.

For example, immediately after opening your database connection, you could do this:

sqlite3_busy_timeout(this->db, 1000); // Wait 1000mS if busy

It is also possible to set such a timeout by command, using the busy_timeout pragma.

You may also wish to consider starting your transaction using BEGIN IMMEDIATE TRANSACTION or BEGIN EXCLUSIVE TRANSACTION so that the transaction can be guaranteed to complete without blocking. See the documentation on transactions.

score 1 · Answer 2 · edited May 23 '17 at 10:34

Please check these two Stack Overflow posts. They seem to be related to your issue.

Can different connections of the same sqlite's database begin transactions concurrently?

If you read the SQLite documentation, you will see that it supports multiple connections for reading only, you cannot write to the database from mulitple connections, because it's not designed for that.

Read and Write Sqlite database data concurrently from multiple connections

Multiple processes can have the same sqlite database open at the same time, several read accesses can be satisfied in parallel.

In case of write, a single write to the database does lock the database for a short time, nothing, even reading, can access the database file at all.

Beginning with version 3.7.0, a new “Write Ahead Logging” (WAL) option is available. In which Reading and writing can proceed concurrently. By default, WAL is not enabled. To turn WAL on, please refer to Sqlite documentation.

SQLite in C++. DB is BUSY (Multithread)

2 Answers2