8

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?

Gustavo
  • 919
  • 11
  • 34

3 Answers3

13

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.

Felix
  • 6,885
  • 1
  • 29
  • 54
  • 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 '17 at 08:14
  • I updated my answer and added sample code to generate names for non-Qt threads – Felix Nov 25 '17 at 11:49
  • 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 '17 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 '17 at 12:22
  • Man idea is to use another class as thread storage which holds the database name and on destruction it will close the database – Gustavo Nov 26 '17 at 11:44
  • Thats one possibility. As long as you make shure to close the connection before the thread exits, it should work. – Felix Nov 26 '17 at 12:52
  • Is the thread storage object destructor called when the thread is joined or is it magic i‘ll never know? – Gustavo Nov 26 '17 at 13:23
  • The local data is destroyed as soon as the thread exits, and *not* when QThreadStorage gets destroyed. As far as I understand the documentation, thats one of the last things that happen on the threads before they are done. For more details, read the docs (link is in the answer) – Felix Nov 26 '17 at 15:05
  • Can I just use `QThreadStorage mDatabasePool` insted of `QThreadStorage storage` as described in [this question](https://codereview.stackexchange.com/questions/181303/multi-client-tcp-server-with-a-thread-for-each-client-and-db-connection-pool/181307) in the `DatabaseService` part? – konstantin_doncov Jun 10 '18 at 06:14
  • 1
    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 '18 at 08:39
  • Ok, thanks. Then I will use your solution in the above answer, I think it's more simple and correct. – konstantin_doncov Jun 11 '18 at 06:08
1

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.

Farhad
  • 4,119
  • 8
  • 43
  • 66
  • 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 '17 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 '17 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 '17 at 14:54
-4

You choose not well approach. Should use shared QSqlDatabase object instead QSqlQuery. Please check next example of multithreading database access. If that will not clear for you please let me know. Will explain more.

stanislav888
  • 374
  • 2
  • 9
  • It is **not** a good idea to use one database connection from multiple threads, as stated here: https://doc.qt.io/qt-5/threads-modules.html. – Felix Nov 24 '17 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 '17 at 14:12
  • 1
    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 '17 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 '18 at 18:13
  • 1
    Remember: Working != Correct. It can always fail at some point. Worst case scenario are silent errors that corrupt your data. – Felix Aug 22 '18 at 22:11