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 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.