Using QSqlDatabase connection in QtConcurrent::run (pseudo connection pooling)

Question

I'm trying to find an efficient way to handle some database queries in Qt. The scenario essentially is that we have many events that we need to write to the db. We cannot block the main thread while writing so these writes are done in separate threads using QtConcurrent::run.

Now, the issue is that currently each concurrent run requires creating a new connection to the DB. We'd like to simply be able to create the connection once and reuse it, but the Qt docs state a connection may only be used in the thread that created it. Using QtConcurrent makes this quite problematic since we don't know in which thread we'll be run.

Note that we have no interest in making writes to the database be parallel, that is, we can impose the restriction that only one thread uses the db connection at once.

Is there any way to use one DB connection and still use QtConcurrent? Or do we, as I fear, have to use a QThread and implement our own signalling rather than making use of the concurrent framework?

Answer: The answers seem to indicate as I suspected, that it just can't be done. QtConcurrent and DB connections don't play well together. This is really too bad. I guess I'll just go back to creating my own thread and using custom signals and slots to communicate.

Luca Carlon · Accepted Answer · 2011-10-16 21:25:23Z

1

According to the Qt documentation it is not possible. QtConcurrent::run() takes a thread from a pool of threads, so you don't know which thread will be used each time. I don't of a way to handle that. It will take the first thread available.

I really think you shouldn't be using QtConcurrent::run() in this situation. A good way I could think is to use a QThread with a QEventLoop. This is very simple: you simply create your reimplementation of QThread invoking exec() immediately. Something like this:

class MyEventLoop : public QThread
{
public:
   MyEventLoop() {
      db = QSqlDatbase::addDatabase(<connection_name>);
      // ...
   }

   ~MyEventLoop() {
      QSqlDatabase::removeDatabase(<connection_name>);
      // ...
   }

   bool event(QEvent* event)
   {
      qDebug("Processing event.");
      return true;
   }

   void run() {exec();}

private:
   QSqlDatabase db;
};

You then reimplement QEvent to include whatever you need to execute your query. This creates only one thread and only one connection. You don't have to create any queue and you don't have to handle concurrency. If you need to know when your query is finished you can create signals at the end of the query. To request a new query you can simply do something like this:

QCoreApplication::postEvent(<pointer_to_thread_instance>, <pointer_to_event_instance>);

Another good approach is to use a pool of QThreads, each with it's own connection. That anyway could be useful if you needed concurrency.

answered Oct 16, 2011 at 21:25

Luca Carlon

10k15 gold badges62 silver badges96 bronze badges

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

3 Comments

edA-qa mort-ora-y Over a year ago

I'm trying to avoid another thread since the ability to directly call threaded functions (the purpose of QtConcurrent) is really convenient compared to the signals/slots communication mechanism.

Luca Carlon Over a year ago

There is no signal/slot used. I don't see why you say it is convenient compared to QMetaObject for instance. Anyway sorry then, I don't know of any way to use one connection only with QtConcurrent::run(). Best thing I can think of is one connection for each thread used by it. Hope someone can be of more help.

edA-qa mort-ora-y Over a year ago

You have to add signals/slots since I need some way to communicate with the thread. Using QtConcurrent I invoke a function and use the QFuture to get the result. That has be replaced with signals and slots. That is, this DB thread has no activity it can do on its own, it needs some other thread to tell it what to do.

AMCoded · Accepted Answer · 2015-10-25 03:08:04Z

1

This article helped me a lot Asynchronous Database Access with Qt 4.x. I think constructing a worker object in the thread and using queued connection to call it's slots, is better than driving new Events and posting them to the thread. You may download the sample files from this link.

answered Oct 25, 2015 at 3:08

AMCoded

1,4442 gold badges25 silver badges39 bronze badges

Comments

Paul-Sebastian Manole · Accepted Answer · 2017-12-21 16:45:51Z

My solution is to use QtConcurrent with a custom thread pool that does not destroy its threads. And in each thread's context, I create a dedicated QSqlDatabase connection with that thread's name as the connection's name such that each threads acquires the same connection each time it needs to talk to the database.

Setup:

mThreadPool = new QThreadPool(this);
// keep threads indefinitely so we don't loose QSqlDatabase connections:
mThreadPool->setExpiryTimeout(-1);
mThreadPool->setMaxThreadCount(10); /* equivalent to 10 connections */

qDebug() << "Maximum connection count is "
         << mThreadPool->maxThreadCount();

Destructor:

// remove the runnables that are not yet started
mThreadPool->clear();
// wait for running threads to finish (blocks)
delete mThreadPool;

Sample API implementation returning a future which can be used to get the data form the database when it is available:

QFuture<QList<ArticleCategory> *> 
DatabaseService::fetchAllArticleCategories() const
{
    return QtConcurrent::run(mThreadPool, 
&DatabaseService::fetchAllArticleCategoriesWorker, mConnParameters);
}

Notice my solution does not manage the objects it creates. The calling code needs to manage that memory (the QList returned above).

Accompanying thread worker function:

QList<ArticleCategory> *DatabaseService::fetchAllArticleCategoriesWorker(const DatabaseConnectionParameters &dbconparams)
{
    try {
        setupThread(dbconparams);
    } catch (exceptions::DatabaseServiceGeneralException &e) {
        qDebug() << e.getMessage();
        return nullptr;
    }

    QString threadName = QThread::currentThread()->objectName();
    QSqlDatabase db    = QSqlDatabase::database(threadName, false);

    if (db.isValid() && db.open()) {
        QSqlQuery q(db);
        q.setForwardOnly(true);
        // ...
    }
// else return nullptr
// ...
}

If you've noticed, setupThread is always called at the start of the worker which basically preps the database connection for the calling thread:

void DatabaseService::setupThread(const DatabaseConnectionParameters &connParams)
{
    utilities::initializeThreadName(); // just sets a QObject name for this thread

    auto thisThreadsName = QThread::currentThread()->objectName();

    // check if this thread already has a connection to a database:
    if (!QSqlDatabase::contains(thisThreadsName)) {
        if (!utilities::openDatabaseConnection(thisThreadsName, connParams))
        {
            qDebug() << "Thread"
                    << thisThreadsName
                    << "could not create database connection:"
                    << QSqlDatabase::database(thisThreadsName, false).lastError().text();
        }
        else
        {
            qDebug() << "Thread"
                    << thisThreadsName
                    << "successfully created a database connection.";
        }
    }
}

Trenton Schulz · Accepted Answer · 2011-10-16 18:35:41Z

0

IIRC correctly, this issue has much more to deal with the backend than Qt. For example, in the past -- it still may -- PostgreSQL required that each thread had its own connection, but MySQL had other ways to work with threading. As long as you played by the rules of the backend, things worked OK.

In the past, for PostgreSQL, I created a system where I would push a QSqlQuery off into a queue and another thread would empty the queue, executed the query, and pass the sqlresult back. This was fine as long as I always used the same "threaded" connection. It did not matter that I created the connection in the main thread, it only mattered when the time came for execution.

QtConcurrent would be a good match for this system, though really only at one-at-a-time. It would free up the main thread though.

You maybe could create a queue of connections. When your function executes, it pulls a connections from the queue, runs it's query and adds it to the end of the queue after the query is complete. This would ensure you would only use one connection is used for each thread. Though not necessarily the same thread for each connection.

Again, this really is dependent on the backend. Check the developer documentation for the database on threading and make sure you abide by those rules.

answered Oct 16, 2011 at 18:35

Trenton Schulz

7783 silver badges8 bronze badges

1 Comment

edA-qa mort-ora-y Over a year ago

Since our DB is just configured in a file I can't really rely on any back-end.

Collectives™ on Stack Overflow

Using QSqlDatabase connection in QtConcurrent::run (pseudo connection pooling)

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related