QSqlQuery - execution in one thread and reading the results in another

[napajejenunedk0]

Hello all.

Long time ago I've posted the same question in the forum, but without a single response. Now, again investigating the case, found a relevant Qt Centre forum topic where @wysota shares that:

"I don't know how internals of QSqlQuery work but I believe that for some backends the query can still call some database functions after it is executed (e.g. when iterating over results, next results can be fetched from the database on the fly) thus accessing it from a thread different than where the query was executed could result in undefined behaviour. I would be very careful about this."
...
"Today it works but from now on upon every crash or disfunction of your project you'll be wondering whether the database is causing that..."

Thus wondering whether it is a wrong QSqlQuery usage if one thread executes the query and passes the executed QSqlQuery to another thread, so that the latter could process the results. The answer is presumably "yes" - the query keeps a cursor to the database using the QSqlDatabase instance e.g. the database connection that was created in the other thread and is designated to be used by the latter, not by the thread that processes the results.

[Christian Ehrlicher]

Correct, you should not do that. Better just create a new QSqlDataBase connection in your thread and execute all there.

[napajejenunedk0]

@Christian-Ehrlicher Now looking at the sources of QSqlQuery, QSqlResult and QSqlRecord and have a question:

• QSqlQuery is interconnected to QSqlDatabase where the latter is connected to a specific QThread instance and it is unsafe, as you mentioned, to use a single QSqlQuery instance in another thread except for the one the QSqlQuery was created at
• Question #1: QSqlResult is interconnected with QSqlDriver and thus is it safe to pass the QSqlQuery's QSqlResult to the other thread? I presume it is not, since the QSqlResult could query the database through the database cursor that is already obtained, so that to fetch further QSqlRecords.
• Question #2: QSqlRecord has no dependencies as far as I can see:

class QSqlRecordPrivate
{
public:
    // ...
    QVector<QSqlField> fields;
    QAtomicInt ref;
};

except for QSqlField which again contains only pure data such as QString, QVariant, etc. Presumably it would be safe to read all QSqlRecords from the already executed QSqlQuery in the thread that executed the QSqlQuery and pass them to the other thread for further processing?

[Christian Ehrlicher]

Once again: it may work but it must not. It can be that a driver handle it differently or anything else. Don't know what's the problem doing a new connection in the thread.

[napajejenunedk0]

I already have a QSqlDatabase instance per each of both threads, but I want the query executed in the helper thread and then fetch the results in the main one, so that to improve performance.

[SGaist]

Hi,

What bottleneck do you have that requires that kind of architecture ?

[napajejenunedk0]

@SGaist The bottleneck are the multiple SQL queries that need to be executed while the application - a server one, should be responsive enough to answer messages received from clients. The SQL queries are now being executed on a dedicated thread where the result is being provided back to the calling thread if it was a SELECT query or a CREATE/INSERT/UPDATE where the last inserted id is the actual result. The calling thread deserializes the raw result - previously the whole QSqlQuery and now the already fetched QSqlRecords from the query, that has been executed on the helper thread if the query was a SELECT one. As @Christian-Ehrlicher already said it is prohibited to use any of the QSql-related classes across threads, but looking at the Qt sources, only the classes QSqlDatabase, QSqlDriver, QSqlQuery and QSqlResult are dangerous and should be avoided in multi-thread scenarios where QSqlRecord and its constituent QSqlFields are nothing more but data containers and are safe enough to be created by one and used by another thread. Also the documentation concerning Qt SQL module and thread usage mentions:
"A connection can only be used from within the thread that created it. Moving connections between threads or creating queries from a different thread is not supported.
In addition, the third party libraries used by the QSqlDrivers can impose further restrictions on using the SQL Module in a multithreaded program. Consult the manual of your database client for more information"

Despite the scarce documentation about thread usage with the Qt SQL module, the only limitations I see is that it is unsafe to pass around QSqlDatabase and QSqlQuery between threads. Also, @JKSH has given an example for a correct database usage with a dedicated database thread (which is exactly the scenario at hand). Found this forum topic just now. He gives as an example (and presumably recommends) using QList< QSqlRecord > as a transport of the output data coming from executed SELECT queries.
Again, it is possible to avoid the usage of QSqlRecord instances across threads, by creating a minified replica of the QSqlRecord e.g. a mapping between field/column name -> value but to me it is unworthy.

[Christian Ehrlicher]

I still don't understand what's the problem in doing the exec and fetch in one thread and passing the result back to the main thread thereafter. But do what you like but don't complain when something will not work with a new Qt version (or another SQL driver) later on.

[napajejenunedk0]

@Christian-Ehrlicher said in QSqlQuery - execution in one thread and reading the results in another:

doing the exec and fetch

I am doing exactly this now in the database thread: exec + extracting all resultant QSqlRecords from the QSqlQuery as you suggested:

Correct, you should not do that. Better just create a new QSqlDataBase connection in your thread and execute all there.

What I wasn't doing before in the database thread aside the execution of the QSqlQuery is the extraction of the result from the QSqlQuery in case it is a select one e.g. fetching all QSqlRecords. The last question at hand was whether QSqlRecord is safe enough to be passed between threads.

[Christian Ehrlicher]

From the current source it looks ok but as I said above - I would not rely on it.
And creating a QSqlRecord for every returned row is not cheap (and mostly useless). Just convert your data in the format you need (or return QVariants) in your thread and return those values.

[napajejenunedk0]

@Christian-Ehrlicher Ok, thanks.