qt simultaneous MySQL queries in different threads = crash

Question

I have a multithreaded Qt5 app written in C++ where each thread is writing to the same database.

I share the same database QSqlDatabase variable across threads, but each thread creates its own query. I am regularly getting crashes and I've captured this info at the time of a crash:

Driver error [QMYSQL3: Unable to store statement results]
Database error [Commands out of sync; you can't run this command now]
Type [2]
Number [2014]

First of all, can I make simultaneous MySQL queries in multiple threads? If so, do I need to protect SQL calls with a mutex? Or do I need to post more info....

UPDATE: I have a seperate DBManager thread which handles opening/closing the DB, and simple database writes. I did this because my DB often goes offline, and I don't want other threads to hang for up to 2 minutes while a db open fails. No I have more threads which do reports, and must retrieve substantial amounts of data from the db.

As noted below, sharing the db handle across threads is not permitted. So now perhaps this is more of a design question - what is the best way to handle this situation? I don't want each thread that does DB access to attempt it's own open and wait for 2 minutes in case the DB is offline

gbjbaanb · Accepted Answer · 2014-04-21 17:28:58Z

3

Read the documentation for the SQL module, in particular the threads section that states each connection can only be used within the thread that created it.

So adding a mutex is not good enough, you will have to either create a new connection object in each thread, or pass the required data to/from a dedicated thread that performs all DB queries.

answered Apr 21, 2014 at 17:28

gbjbaanb

52.8k12 gold badges111 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TSG Over a year ago

I thought about having a DB thread do all of the queries, but how would I pass the query one way and results back? (Signals and slots for DB results?)

gbjbaanb Over a year ago

any way you want signal/slot sounds ok, but you have to worry abotu lots of data passing. I'd be more concerned that the result recordset is difficult to marshal to the other threads, so you'd have to copy the recordset data into a neutral data structure to pass. Its probably much easier to just create a new DB connection per thread. Test to see how expensive it is, as many DB drivers pool connections anyway.

TSG Over a year ago

My problem is that the DB goes offline sometimes, so I have a seperate thread that tries at intervals to reopen the DB. Only if it succeeds do other threads try to do DB access - by sharing the QSqlDatabase pointer (if non zero). I'm afraid of trying to open a new connection which might hang my GUI thread for 2 minutes while an open fails

RobbieE Over a year ago

Why would a connection in a separate thread hang your GUI thread?

mbroadst · Accepted Answer · 2014-04-22 21:07:16Z

3

I generally handle this problem by providing a factory method on my database manager class, something that looks similar to this (semi-pseudocode):

static QHash<QThread, QSqlDatabase> DatabaseManager::s_instances;
static QMutex DatabaseManager::s_databaseMutex;
QSqlDatabase DatabaseManager::database() {
    QMutexLocker locker(s_databaseMutex);
    QThread *thread = QThread::currentThread();

    // if we have a connection for this thread, return it
    if (s_instances.contains(thread))
        return s_instances[thread];
    }

    // otherwise, create a new connection for this thread
    QSqlDatabase connection =
        QSqlDatabase::cloneDatabase(existingConnection, uniqueNameForNewInstanceOnThisThread);

    // open the database connection
    // initialize the database connection

    s_instances.insert(thread, connection);
    return connection;
}

As long as you make sure you create an initial connection (you can modify the factory to include that, or just create the initial connection in your DatabaseManager constructor), you should be able to replace most existing cases where you're using a QSqlDatabase with:

QSqlQuery query(DatabaseManager::database());
query.exec(<some sql>);

without worrying about which thread you're calling from. Hope that helps!

answered Apr 22, 2014 at 21:07

mbroadst

8887 silver badges8 bronze badges

3 Comments

TSG Over a year ago

Interesting - but not sure how this helps. Since each thread gets its own connection, aren't you adding complexity for nothing? Why not just let each thread open its own connection? (I suspect there is a benefit I don't understand)

mbroadst Over a year ago

As gbjbaanb pointed out above, there is a hard requirement that there be a separate connection per thread, so this complexity has to be isolated somewhere. While you're correct that you could just create a new connection right where you create your thread, I find this approach more elegant in that a) it creates the connections in a lazy manner (creating them once you need them, rather than front loading a ton of connections), and b) you never have to think about database thread issues again, letting you to focus on the real work you're trying to do

Michal Fapso Over a year ago

Hi @mbroadst, thanks for your answer. It helped me a lot. I have just posted a bit more complete code below. If you like it, you can add it to your own answer and I will remove mine then.

Michal Fapso · Accepted Answer · 2016-07-01 20:28:47Z

This is just mbroadst's excellent answer with minor changes. This code should be complete and directly compilable:

database_manager.h:

#ifndef DATABASEMANAGER_H
#define DATABASEMANAGER_H

#include <QMutex>
#include <QHash>
#include <QSqlDatabase>

class QThread;

class DatabaseManager
{
    public:
        static QSqlDatabase database(const QString& connectionName = QLatin1String(QSqlDatabase::defaultConnection));
    private:
        static QMutex s_databaseMutex;
        static QHash<QThread*, QSqlDatabase> s_instances;
};

#endif // DATABASEMANAGER_H

database_manager.cpp:

#include "database_manager.h"

#include <QSqlDatabase>
#include <QMutexLocker>
#include <QThread>
#include <stdexcept>

QMutex DatabaseManager::s_databaseMutex;
QHash<QThread*, QHash<QString, QSqlDatabase>> DatabaseManager::s_instances;

QSqlDatabase DatabaseManager::database(const QString& connectionName)
{
    QMutexLocker locker(&s_databaseMutex);
    QThread *thread = QThread::currentThread();

    // if we have a connection for this thread, return it
    auto it_thread = s_instances.find(thread);
    if (it_thread != s_instances.end()) {
        auto it_conn = it_thread.value().find(connectionName);
        if (it_conn != it_thread.value().end()) {
            return it_conn.value();
        }
    }

    // otherwise, create a new connection for this thread
    QSqlDatabase connection = QSqlDatabase::cloneDatabase(
        QSqlDatabase::database(connectionName),
        QString("%1_%2").arg(connectionName).arg((int)thread));

    // open the database connection
    // initialize the database connection
    if (!connection.open()) {
        throw std::runtime_error("Unable to open the new database connection.");
    }

    s_instances[thread][connectionName] = connection;
    return connection;
}

Usage: Instead of QSqlDatabase::database(), use DatabaseManager::database()

Edited 2016-07-01: fixed bug with multiple connection names

Collectives™ on Stack Overflow

qt simultaneous MySQL queries in different threads = crash

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related