Problem with SQLite Database and threads "Database is locked"

[davidesalvetti]

Hi all,

I'm having a problem trying to manage my database in a multi-thread application.
I have 3 different thread that access the same local SQLite database, one of them is the main thread.
The main thread creat the connection to the database using the function:

    db = QSqlDatabase::addDatabase("QSQLITE");    //db is a global variable
    db.setDatabaseName(str + "/prove.db");

while the other two threads create a clone of the db:

db_2 = QSqlDatabase::cloneDatabase(db,"second");

db_3 = QSqlDatabase::cloneDatabase(db,"third");

In a cronological order, the first database (the one of the main thread) is created, then is created the second and the the third.
I access to it from the third thread and then it gives me the problem when I try to acces with the second.

The problem is that when I try to access using the second thread (but I don't know if it's a case that happans only with the second thread), I get the error

"Unable to fetch row" " database is locked"

Am I doing something wrong? The cloneDatabase method should be thread safe, isn't it?

if you need more information don't hesitate to ask for them, please.
If somebody has any hint it would be appreciated a lot.

Thanks in advance

[Kent-Dorfman]

I believe sqlite concurrency only works for pure read operations. I think your clone operations are considered writes, even though they are to different databases.

[SGaist]

Hi,

How are you passing the main DB connection to your threads ?

[KroMignon]

@davidesalvetti Hi, I don' remember where I found this, but since Qt 5.11, sharing the same connection between threads is not allowed.
You have to create a QSqlDatabase for each thread. The easiest is to add the thread id to the connection name to avoid name collision.
Something like this:

QSqlDatabase MyClass::getDBConnection()
{
    // Starting with Qt 5.11, sharing the same connection between threads is not allowed.
    // Use a dedicated connection for each thread requiring access to the database,
    // using the thread address as connection name.

    QSqlDatabase cnx;

    QString dbName = QStringLiteral("myConnection_%1").arg(qintptr(QThread::currentThreadId()), 0, 16);
    if(QSqlDatabase::contains(dbName))
    {
        cnx = QSqlDatabase::database(dbName);
    }
    else
    {
        cnx = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), dbName);
        cnx.setDatabaseName(m_dbPath);
        cnx.setConnectOptions("QSQLITE_BUSY_TIMEOUT=1000");
        if (!cnx.isValid() || !cnx.open())
        {
            qDebug() << "DB connection creation error!";
        }
    }
    return cnx;
}

[davidesalvetti]

@SGaist I have a global file that contains the declaration of the main DB, that is included in all the three threads.

[davidesalvetti]

@Kent-Dorfman so what should I do to avoid this problem?

[davidesalvetti]

@KroMignon I didn't specified it but I'm using it 5.9.1 with MinGW 32 bit compiler, do you think it can be something that happens also in previous versions?

[KroMignon]

@davidesalvetti I don't remember exactly what the problem is, but when QSqlDatabase() is not created in the same thread in which is it used, then something goes wrong internally.
To avoid this kind of issues, starting with Qt 5.11, Qt does not allow to used QSqlDatabase() is another thread as in own.

So yes, it is a bad practice to use same QSqlDatabase() in multiple threads.

Addendum: when cloning database with QSqlDatabase::cloneDatabase(), don't forget to call open(), as you can see in documentation:

Note: The new connection has not been opened. Before using the new connection, you must call open().

[davidesalvetti]

@KroMignon thanks for your answer. Yes, I'm opening the connection and debugging I can see that it opens the it.

What I didn't understand quite well is:
I can't use the same QSqlDatabase() in different threads or I can't connect to the same database in different threads? I'm cloning the main QSqlDatabase so I have different instances of QSqlDatabase, each one on it's own thread, is this a problem? Or using different instances it's correct?

[KroMignon]

@davidesalvetti said in Problem with SQLite Database and threads "Database is locked":

I can't use the same QSqlDatabase() in different threads or I can't connect to the same database in different threads?

You can/should only use QSqlDatabase() in the thread in which it has been created.
All QSqlDatabase() created in main thread, must only be used in main thread. QSqlDatabase() must be create in the thread in which you want to use it.
This is why I've create a little function which create a new QSqlDatabase() when I need to dialog with database. So I am always sure the QSqlDatabase() I use is the right one.
You should never store locally in your class a QSqlDatabase() instance, only create one when you need it, and destroy it after. This is the recommended usage for QSqlDatabase().

Extract for QSqlDatabase documentation:

Warning: It is highly recommended that you do not keep a copy of the QSqlDatabase around as a member of a class, as this will prevent the instance from being correctly cleaned up on shutdown. If you need to access an existing QSqlDatabase, it should be accessed with database(). If you chose to have a QSqlDatabase member variable, this needs to be deleted before the QCoreApplication instance is deleted, otherwise it may lead to undefined behavior.

[davidesalvetti]

@KroMignon The problem is that I have created three different QsqlDatabase() in three different thread, and in every thread I use the QSqlDatabase() created in that thread. In this way it should work but it keeps giving me the problem. But I'll do more tests.

Anyway I found a workaround for my personal application, but maybe other people may be interested in a solution.

[SGaist]

What workaround is that ?

Can you show how you are creating your database object in these threads ?

[davidesalvetti]

@SGaist The workaround is good only for a few people. Since I always worked with two different threads (the main thread and another thread) that have access to the database, I'm just telling the second thread to do the things that the third thread should do with the database, but obviously is just a solution for my case.

this is the way I'm createing the database connection:

void T_Analysis::connectDB()
{
    db_2 = QSqlDatabase::cloneDatabase(db,"second");    

    if(!db_2.open())
    {
        qDebug() << "error";
    }
    else
    {
        qDebug() << "okdb_2";
    }
}

void T_Usb::connectDB()
{
    db_3 = QSqlDatabase::cloneDatabase(db,"third");    

    if(!db_3.open())
    {
        qDebug() << "error";
    }
    else
    {
        qDebug() << "okdb_3";
    }
}

Main thread:

void MainWindow::connect()
{
     db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("Prova.db");

    if (!db.open())
    {
             //.... some code
    }

[KroMignon]

@davidesalvetti Hmm, I am not very confident in your solution. I would create a helper class to create/use right connection according to current thread.
Something like this (it is just a skeleton, not sure it is working as it is):

#include <QSqlDatabase>
#include <QThread>

class MyBDConnection
{
    QString m_dbPath;
    QString m_dbName;
    Q_DISABLE_COPY(MyBDConnection)
public:
    explicit MyBDConnection(const QString &sqlitePath, const QString &cnxName): 
                                                    m_dbPath(sqlitePath), m_dbName(cnxName) 
    {}

    QSqlDatabase getDBConnection()
    {
        // Starting with Qt 5.11, sharing the same connection between threads is not allowed.
        // Use a dedicated connection for each thread requiring access to the database,
        // using the thread address as connection name.

        QSqlDatabase cnx;

        QString dbName = QStringLiteral("%1_%2").arg(m_dbName).arg(qintptr(QThread::currentThreadId()), 0, 16);
        if(QSqlDatabase::contains(dbName))
        {
            cnx = QSqlDatabase::database(dbName);
        }
        else
        {
            cnx = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), dbName);
            cnx.setDatabaseName(m_dbPath);
            if (!cnx.isValid() || !cnx.open())
            {
                qDebug() << "DB connection creation error!";
            }
        }
        return cnx;
    }
}

And the only create on instance of this class and pass the pointer to each class which need connection to DB.