QSql module and multithreaded application

Question

I'm troubled by how the Sql module works with multithreaded application in Qt. http://doc.qt.io/qt-5/threads-modules.html#threads-and-the-sql-module clearly states that "A connection can only be used from within the thread that created it".

However this piece of code I wrote works :

#include <QCoreApplication>
#include <QSqlQuery>
#include <QtConcurrentRun>

void    req()
{
  QSqlQuery q("INSERT INTO users (username) VALUES (\"test\")");
}

void    db()
{
  QSqlDatabase _db;

  _db = QSqlDatabase::addDatabase("QMYSQL");
  _db.setDatabaseName("dbname");
  _db.setHostName("host");
  _db.setUserName("username");
  _db.setPassword("password");
  if (_db.open())
    std::cout << "Ok" << std::endl;
  else
    std::cout << "Error" << std::endl;
}

int     main(int ac, char **av)
{
  QCoreApplication app(ac, av);
  QtConcurrent::run(db);
  sleep(1);
  QtConcurrent::run(req);
  return app.exec();
}

My application design requires multiple threads to interact with a database. Thoses threads are spawned and managed by QtConcurrent::run().

So, since this piece of code works should I go for it or will I run into troubles doing that ?

Any help, documentation or explanation is welcome ! Thank you.

score 6 · Accepted Answer · edited Nov 23 '15 at 12:19

6

The above code is fine because QSqlQuery creates its own QSqlDatabase. It would be a problem if it referenced _db as created by db(). The down side is that it doesn't actually do anything.

QSqlDatabase is not a QObject but it has-a driver which is a QObject and therefore has thread affinity.

If creating loads of QSqlDatabases is prohibitive, create worker threads which maintains their own connection. Then dispatch queries to these threads rather than creating new threads and therefore new connections.

edited Nov 23 '15 at 12:19

Christophe Weis

2,518
4
28
32

answered Sep 27 '11 at 09:35

spraff

32,570
22
121
229

What do you mean by "The down side is that it doesn't actually do anything." ? My query is executed, and the entry inserted. If I understand decently, QSqlQuery will create its own QSqlDatabase using the database configuration from the db created by `QSqlDatabase::addDatabase()` ? Does that imply a lot of overhead (because of objects' copies or something)? – Xaqq Sep 27 '11 at 09:48
1

Your `QSqlQuery` object knows nothing about the `QSqlDatabase` you also created because you're _not_ using the "`db` connected" constructor `QSqlQuery::QSqlQuery(QString&, QSqlDatabase)` (i.e. you don't pass the pre-created `db` to the query). The way you instantiate the query means that a new, separate DB connection will be created/torn down each time `req()` is called. Which is thread-safe, but potentially expensive. – FrankH. Sep 27 '11 at 11:45
I did some more testing, and calling multiple QtConcurent::run(req) is causing the test program to segfault. edit: it seems that the query instanciation is not thread safe. – Xaqq Sep 27 '11 at 11:50
1

I'd guess that's because although many Qt method calls are reentrant, they're not always thread-safe, and the global database object is getting screwed-up. Create one QSqlDatabase per worker thread with its own connection, and create all queries in that thread from that database. – spraff Sep 27 '11 at 12:12

QSql module and multithreaded application

1 Answers1