Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?

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.
                What design should i choose Ehen there are some threads I don't have access to? For example poco as a http request handler. Adding databases is not possible because i don't know which thread calls the handler.
– Gustavo
                Nov 25, 2017 at 8:14
                OK i think that is clear know. I will create a get function for a qsqldatabase object which uses the thread local storage to check if the database has been added and opened. If not, it will create the database and opens it.
– Gustavo
                Nov 25, 2017 at 12:05
                Exactly. The only "problematic" part here is the closing of the database connections. I would recommend some kind of reference-counting. If you want, I can add an example for that to my answer as well.
– Felix
                Nov 25, 2017 at 12:22
                Theoretically yes, but this will not disconnect the database connections once the threads exit! Instead, create a wrapper class that opens the connection on first use and closes it on destruction. Also, this will fail for the main thread - there you need to close the connection before the QCoreApplication gets destroyed.
– Felix
                Jun 10, 2018 at 8:39
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.
                At the moment I don't use signals and slots but a QSqlQuery object on the stack after locking the global database mutex.
– Gustavo
                Nov 23, 2017 at 14:30
                Seems you're okay. if you haven't many/huge interaction with database it's better to do this for each query: open();  query(); close();
– Farhad
                Nov 23, 2017 at 14:45
                That would mean I need to call addDatabase before but I call addDatabase inside main and every thread uses QSqlQuery without a db argument
– Gustavo
                Nov 23, 2017 at 14:54
                It is not a good idea to use one database connection from multiple threads, as stated here: doc.qt.io/qt-5/threads-modules.html.
– Felix
                Nov 24, 2017 at 12:54
                My example at least works.  I really not see why mutex will not help protect database connection as well.
– stanislav888
                Nov 24, 2017 at 14:12
                Just because it works, doesn't mean it should be done that way. You may just have been lucky until now. The documentation clearly discourages this, and you don't know what happens internally. Also, the mutex aproach prevents parallel reads, which are possible otherwise (at least on qt level, depends how sqlite handels it). But except that, it's a good example.
– Felix
                Nov 24, 2017 at 15:50
                I voted both your answers up @Felix and stanislav888 because both seem to work. The documentation from QT is some unclear about this, they say that QSqlDatabase(-constructor-object) is threadsafe but can not be passed between threads. However it seems to work if it is defined in the main-thread and referenced by sub-threads. It does not work properly when defined in one sub-thread and passed to another sub-thread in my case. I would skip the mutex though as it does not help, either it is thread safe or not :) Edit: I am using mysql-database
– Gunnar Sigfusson
                Aug 22, 2018 at 18:13
                Remember: Working != Correct. It can always fail at some point. Worst case scenario are silent errors that corrupt your data.
– Felix
                Aug 22, 2018 at 22:11
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.