CODEWITHSUNDEEP
c++databasesqliteqtlocking
user3132457
user3132457

Reputation: 1019

Determine whether SQLite database is locked

I've read other answers on how to detect whether the SQLite database is locked, and it suggests to use sqlite3_busy_handler/sqlite3_busy_timeout. I'm using Qt which has support of SQLite databases but that doesn't matter here.

Because in the use case of my application another application might access the same database, I need to handle this case.

Here's the options I set to the database when opening it:

auto db = QSqlDatabase::database();
db.setDatabaseName(m_sDatabasePath);
db.setConnectOptions("QSQLITE_BUSY_TIMEOUT=0");
if (!db.open())
    return false;

I have a function which should determine whether database is locked or not:

int callback(void *data, int)
{
    std::cout << "callback" << std::endl;
    return 0;
}

bool isDatabaseLocked(const QSqlDatabase &db)
{
    if (auto driver = db.driver())
    {
        // get driver handler
        QVariant v = driver->handle();
        if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0)
        {
            // v.data() returns a pointer to the handle
            auto handle = *static_cast<sqlite3 **>(v.data());
            if (handle)
            {
                std::cout << sqlite3_busy_handler(handle, callback, nullptr) << std::endl;
                std::cout << sqlite3_busy_timeout(handle, 0) << std::endl;
            }
        }
    }

    return true;
}

When the database is lcoked, I execute this function and get printed two 0s (SQLITE_OK) while I'd expect to get 5 (SQLITE_BUSY). And the callback function isn't called either.

What am I doing wrong?

Upvotes: 3

Views: 4291

Answers (1)

p-a-o-l-o
p-a-o-l-o

Reputation: 10047

As clearly stated in the documentation, the sqlite3_busy_handler function sets a callback function as the busy handler for the connection. Such callback might be invoked whenever a lock is met. In your code, the call to sqlite3_busy_handler returns SQLITE_OK, which is perfectly fine: it means the attempt to set the callback succeeded, no reason to return SQLITE_BUSY at this time, since the call only affects the connection, not the database.

About the same applies to the sqlite3_busy_timeout function, which in turn sets a different kind of busy handler (which replace the callback you just set, by the way) which simply sleeps for the specified amount of milliseconds until the lock is released.

Now, generally speaking, how to detect if a resource is locked? The usual way is: try to lock it.

So you could use a function like this:

bool isDatabaseLocked(const QSqlDatabase & db)
{
    QSqlQuery q(db);
    if(q.exec("BEGIN EXCLUSIVE")) //tries to acquire the lock
    {
        q.exec("COMMIT"); //releases the lock immediately
        return false; //db is not locked
    }
    return true; //failed to acquire the lock: returns true (db is locked)
}

To make it return immediately when the lock cannot be acquired, clear the busy handler, setting the busy timeout to zero for this connection (before opening it):

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setConnectOptions("QSQLITE_BUSY_TIMEOUT=0");

To test the function, open a command line sqlite3 session on the database, and lock it by typing:

sqlite> BEGIN EXCLUSIVE;

When you want to release the lock, just type

sqlite> COMMIT;

Upvotes: 3

Related Questions