Solved QSqlQuery - execution in one thread and reading the results in another
-
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 executedQSqlQuery
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 theQSqlDatabase
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. -
Correct, you should not do that. Better just create a new QSqlDataBase connection in your thread and execute all there.
-
@Christian-Ehrlicher Now looking at the sources of
QSqlQuery
,QSqlResult
andQSqlRecord
and have a question:QSqlQuery
is interconnected toQSqlDatabase
where the latter is connected to a specificQThread
instance and it is unsafe, as you mentioned, to use a singleQSqlQuery
instance in another thread except for the one theQSqlQuery
was created at- Question #1:
QSqlResult
is interconnected withQSqlDriver
and thus is it safe to pass theQSqlQuery
'sQSqlResult
to the other thread? I presume it is not, since theQSqlResult
could query the database through the database cursor that is already obtained, so that to fetch furtherQSqlRecord
s. - 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 asQString
,QVariant
, etc. Presumably it would be safe to read allQSqlRecord
s from the already executedQSqlQuery
in the thread that executed theQSqlQuery
and pass them to the other thread for further processing? -
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.
-
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. -
Hi,
What bottleneck do you have that requires that kind of architecture ?
-
@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 aCREATE
/INSERT
/UPDATE
where the last inserted id is the actual result. The calling thread deserializes the raw result - previously the wholeQSqlQuery
and now the already fetchedQSqlRecord
s from the query, that has been executed on the helper thread if the query was aSELECT
one. As @Christian-Ehrlicher already said it is prohibited to use any of theQSql
-related classes across threads, but looking at the Qt sources, only the classesQSqlDatabase
,QSqlDriver
,QSqlQuery
andQSqlResult
are dangerous and should be avoided in multi-thread scenarios whereQSqlRecord
and its constituentQSqlField
s 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
andQSqlQuery
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) usingQList< QSqlRecord >
as a transport of the output data coming from executedSELECT
queries.
Again, it is possible to avoid the usage ofQSqlRecord
instances across threads, by creating a minified replica of theQSqlRecord
e.g. a mapping between field/column name -> value but to me it is unworthy. -
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.
-
@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
QSqlRecord
s from theQSqlQuery
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 theQSqlQuery
in case it is a select one e.g. fetching allQSqlRecord
s. The last question at hand was whetherQSqlRecord
is safe enough to be passed between threads. -
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. -
@Christian-Ehrlicher Ok, thanks.