Using QSqlQuery from multiple threads

Question

I have a lot of C++11 threads running which all need database access at some time. In main I do initalize the database connection and open the database. Qt documentation says that queries are not threadsafe so I use a global mutex until a QSqlQuery exists inside a thread.

This works but is that guaranteed to work or do I run into problems at some time?

Answer 1

A look at the Documentation tells us, that

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.

So you do indeed need one connection per thread. I solved this by generating dynamic names based on the thread:

auto name = "my_db_" + QString::number((quint64)QThread::currentThread(), 16);
if(QSqlDatabase::contains(name))
    return QSqlDatabase::database(name);
else {
    auto db = QSqlDatabase::addDatabase( "QSQLITE", name);
    // open the database, setup tables, etc.
    return db;
}

In case you use threads not managed by Qt make use of QThreadStorage to generate names per thread:

// must be static, to be the same for all threads
static QThreadStorage<QString> storage;

QString name;
if(storage.hasLocalData())
    name = storage.localData();
else {
    //simple way to get a random name
    name = "my_db_" + QUuid::createUuid().toString();
    storage.setLocalData(name);
}

Important: Sqlite may or may not be able to handle multithreading. See https://sqlite.org/threadsafe.html. As far as I know, the sqlite embedded into Qt is threadsafe, as thats the default, and I could not find any flags that disable it in the sourcecode. But If you are using a different sqlite version, make shure it does actually support threads.

Answer 2

You can write class with SQL functions and use signals-slots to do the queries and get result from database.

It's thread-safe also no need to use mutex.