Correctly using QSqlDatabase in multi-threaded programs

Question

Based on the Qt documentation:

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.

The question that bothers me, is what happens when I copy-construct database instance. For example, here is code in main thread:

int main(int argc, char** argv) {
...
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", "DB1");
    db.setHostName("localhost");
...

and here is connection in worker threads:

void MyThread::run() {
    QSqlDatabase db(QSqlDatabase::database("DB1"));
    if (db.open()) {
    ...
}

Is this thread safe or not? Normally, such operation would be safe in C++, but since QT use implicit sharing and thread affinity I am not sure anymore.

They say: A connection can only be used from within the thread that created it, but what does that mean? Is QSqlDatabase::addDatabase point where connection is created or it actually when open() function is called.

UPDATE:

After answer from Laszlo Papp, and eventually looking into Qt source code, I must say that design of this part of Qt looks flawed to me.

If I understand correctly, QSqlDatabase uses implicit sharing under the hood, but unfortunately it is not a true implicit sharing, since copy constructor of QSqlDatabase instance will not create a new instance of shared data when it is needed. To make things worse, you can not create temporally connection, but instead you must use static methods addDatabase/removeDatabase, in which case you have to synchronize threads to avoid name collision.

This of course makes using QSqlDatabase in QtConcurrent very hard, especially if connection should be buried deep behind some abstraction. Since we do not know on which thread out code is going to run, we can not keep connection open between two calls. And if we wanted to spawn dynamic number of task, we would need to make sure that tasks do not use same name of database.

All this makes me wonder about design goals and if implicit sharing is suitable for this particular case. IMHO, much better solution would be to let copy constructor really do it job and make a copy of connection for you. Those who do not want to have private/temporary copies, could still use addDatebase/removeDatabase, in which case method database() needs to be modified to return reference.

Solution 1

They say: A connection can only be used from within the thread that created it, but what does that mean? Is QSqlDatabase::addDatabase point where connection is created or it actually when open() function is called.

The former. See the documentation for details:

The QSqlDatabase class represents a connection to a database.

The QSqlDatabase class provides an interface for accessing a database through a connection. An instance of QSqlDatabase represents the connection. The connection provides access to the database via one of the supported database drivers, which are derived from QSqlDriver. Alternatively, you can subclass your own database driver from QSqlDriver. See How to Write Your Own Database Driver for more information.

Create a connection (i.e., an instance of QSqlDatabase) by calling one of the static addDatabase() functions...

This last sentence should clear your concern up.

Solution 2

You can use QSqlDatabase::cloneDatabase for getting a "real" copy of a database, that is ready to be opened in any thread.

You need to do this in the thread that initialized the cloned Database, but you can move the obtained not yet opened database to any thread and work with it there.

Comments

[user362515]

Too bad cloneDatabase is not thread-safe - would have made the life much easier.

[konstantin_doncov]

If I have multiple objects which call method of the single object which calls database, is it thread-safe? If it's not, should I use mutex? Or what would be the best practice for a multithreaded server?