Problem with SQLite Database and threads "Database is locked"

[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.