Using thread to interract with the database

Vinorcola

Hi everybody,

In a project that make a lot of use of a database, I would like to manage every SQL query into a different thread to keep the GUI unblocked (especially for very long queries).
For now, I've used the eventLoop object between queries to update the GUI, but at some point, some very long queries (like INSERT INTO ... SELECT ... which can inster fex thousand of lines). are still blocking the GUI.

So my idea is to create an object (DatabaseInterraction) that manage the connection to the database and execute all the SQL queries of the application. This object contains for the moment some functions that execute one or several queries (with transaction use). For the moment, I didn't thought about how return the SELECT result (may be with QList<QSqlRecord> ?) but that is not relevant for now.

The thing is I've never used threads before so I'd like some help to design my code organisation. After read different topics, I find out that the best organization is to create a class that inherits QThread class and that create a new DatabaseInterraction object into the run method. But then, I still don't fully understand some things. For example, how to connect the DatabaseInterraction object with the widget. Because I read that we have to be very carefull when we share pointers between thread, so am I allowed to do this into the run method:

void DatabaseThread::run()
{
    m_databaseInterraction = new DatabaseInterraction();
    connect(this, SIGNAL(finished()), m_databaseInterraction, SLOT(deleteLater()));
    connect(m_databaseInterraction, SIGNAL(errorOccured(QString,QString)), this, SLOT(displayErrorMessage(QString,QString)), Qt::BlockingQueuedConnection);
    
    exec();
}

The displayErrorMessage slot just display a QMessageBox warning.

Here, I'm just a bit woried about using the "this" pointer (with represent the QThread object in the main thread) into the run() method which run into the new thread.

Thanks for your help.

amahta

I don't think you should be worried about using "this" inside any class at all because even when you don't write it, it's there! :)

What you should worry about though is that you need to keep main classes (which are used by all threads) totally separate and independent of how the threads are performing. That is because you can never know at what stage a thread is running unless you use lots of flags (lots of lots of flags if you're writing a complex app) and that will ruin the logic of using threads.

By the way in your case I would keep Database object independent of the threads that are Querying it. That means using the same pointer to Database :)

JKSH

Hi Vinorcola,

@Vinorcola said in Using thread to interract with the database:

I would like to manage every SQL query into a different thread to keep the GUI unblocked (especially for very long queries).

Yes, this is a good use case for threads.

After read different topics, I find out that the best organization is to create a class that inherits QThread class and that create a new DatabaseInterraction object into the run method.

Actually, since you want a permanent thread, I'd say the best approach is to create a NON-subclassed QThread, make DatabaseInteraction inherit QObject, and move your DatabaseInteraction object to the new thread.

This page shows you the different ways you can use threads; the 2nd-last example describes your situation: https://doc.qt.io/qt-5/threads-technologies.html

The 1st example in the QThread documentation shows you how to implement it: https://doc.qt.io/qt-5/qthread.html#details

But then, I still don’t fully understand some things. For example, how to connect the DatabaseInterraction object with the widget. Because I read that we have to be very carefull when we share pointers between thread

You're right: Sharing pointers between threads is risky. So, it's good to avoid doing that altogether. See this page for a discussion on passing data between threads: https://doc.qt.io/qt-5/threads-synchronizing.html

You can communicate through signals and slots, and avoid sharing any pointers/variables:

int main(int argc, char *argv[]) {

    ...

    QThread thread;
    MainWindow gui;
    DatabaseManager dbManager;
    dbManager.moveToThread(&thread);

    QObject::connect(&gui, SIGNAL(queryRequested(QStringList)), &dbManager, SLOT(executeQuery(QStringList)));

    QObject::connect(&dbManager, SIGNAL(queryFinished(QList<QSqlRecord>)), &gui, SLOT(processResults(QList<QSqlRecord>)));

    thread.start();

    ...
}

In this example, when your user clicks a button, your GUI can package all relevant forms into a QStringList (or a custom class, if you want), and then send the data to the other thread by emitting a signal. The DatabaseManager will do the same to return data to the GUI.

Note: Do NOT use a BlockingQueuedConnection, or your GUI will get blocked anyway

Vinorcola

Ok thanks for your further explanations. It will help me a lot.

Indead, the way I was thinking was bad: I wanted to put every SQL queries into the database thread (into differents functions) and create as much signals that there is function, while the SQL queries should be built into the main thread and then sent to the database thread. So I will manage to change that.

However, just a thing I didn't realy understand from your code. You created a DatabaseManager on the stack (into the main function, but let's say here that we are actualy into another function) and then move it to the thread. Does this variable will be move in the new thread stack? So it won't be destroy at the end of the function? Or is it better to call new, and connect the finished signal of the thread to the deleteLater slot of the DatabaseInterraction object?

Other thing, I guess we have to connect every signals and slots before starting the new thread?

JKSH

You're welcome :)

@Vinorcola said in Using thread to interract with the database:

I wanted to put every SQL queries into the database thread (into differents functions) and create as much signals that there is function, while the SQL queries should be built into the main thread and then sent to the database thread.

You can create the query string in the main thread if you want, but the QSqlQuery object must be created in the database thread: https://doc.qt.io/qt-5/threads-modules.html#threads-and-the-sql-module

You created a DatabaseManager on the stack (into the main function, but let's say here that we are actualy into another function) and then move it to the thread. Does this variable will be move in the new thread stack? So it won't be destroy at the end of the function? Or is it better to call new, and connect the finished signal of the thread to the deleteLater slot of the DatabaseInterraction object?

No, moveToThread() does not move the object to another stack. moveToThread() changes the object's thread affinity. This is an important concept; please read https://doc.qt.io/qt-5/qobject.html#thread-affinity

In my example, both the QThread and the DatabaseManager are permanent objects. They are not destroyed at the end of every query. This was my thought:

The DatabaseManager contains the QSqlDatabase object.
After the DatabaseManager has been moved to the other thread, it opens the connection to the database (from the new thread)
The GUI emits signals to send query parameters to the DatabaseManager. The DatabaseManager then uses these parameters to create the QSqlQuery and execute it.
The DatabaseManager emits signals to send the query results back to the GUI.

I guess we have to connect every signals and slots before starting the new thread?

You can connect them after starting the thread, no problem. But, you must connect them before your GUI emits any signals to query.

Vinorcola

@JKSH said in Using thread to interract with the database:

You can create the query string in the main thread if you want, but the QSqlQuery object must be created in the database thread

Yep I get that, I was not so clear in my post. Sorry.

One more question, is it better to keep the connection alive while the thread is running, or is it better to open the connection before every request, and close it at the end?

My guess is that's not realy relevant since the low level network API should close automatically the connection after an inactive period and reopen it for the next request. But I'm not realy sure of that.

JKSH

@Vinorcola said in Using thread to interract with the database:

One more question, is it better to keep the connection alive while the thread is running, or is it better to open the connection before every request, and close it at the end?

I'm not an expert in this area, but other people recommend closing at the end of every request: http://stackoverflow.com/questions/861552/when-should-i-open-and-close-a-connection-to-sql-server