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

Solution 1:

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;