What is the correct way to open and close a database connection for a Qt worker thread

Question

I'm working on a scenario where we want to asynchronously execute INSERT/DELETE statements on a database table (it's a fire-and-forget scenario). I'm planning to simply fire a signal with relevant data, and have the thread's event loop process each signal similar to the following example:

Worker *worker = new Worker;
worker->moveToThread(&workerThread);
connect(&workerThread, &QThread::finished, worker, &QObject::deleteLater);
connect(signalSource, &SignalSource::dataReady, worker, &Worker::updateMyFancyTable);
workerThread.start();

The thread is supposed to have its own database connection opened as long as it is running. I would do it like this (with added error handling, of course):

connect(&workerThread, &QThread::started, worker, &Worker::establishDatabaseConnection);

When stopping the thread, it is supposed to finish its work and then close the database connection.

As far as I understand, calling quit() on the thread will first process all remaining signals (if any) and then exit the thread's event loop.

How do I properly close the database connection in a scenario like this?

1. Do I emit a signal for closing the connection before calling quit()?
2. Do I close the connection in a slot in the main thread which gets called once QThread::finished has been emitted?
3. Something else?

Answer 1

I think this discussion answers some of your questions: Stop processing event-queue immediately on QThread.exit(). So it seems that yes, all slots are executed before the thread exits. The suggested solution is to use QThread::requestInterruption and test QThread::currentThread()->isInterruptionRequested() in your slot. Note that the slot is still being called multiple times, so you'll have to return until the queue is empty.

In your case you could ignore calls after the interruption request and emit a signal to close the connection. So option 1 could be a solution. Or you can even close the connection the first time isInterruptionRequested() returns true in your slot, but you'll probably have to handle the case of an empty event queue and call the slot anyway.

Option 2 may not be a feasible solution, depending on how you handle the connection to the db: frequently, the connection can only be used in the thread that created it. This is the case for the Qt SQL module (https://doc.qt.io/qt-5/threads-modules.html#threads-and-the-sql-module). You could connect the finished() signal to a slot executed in the background thread instead, and close the connection there, but my understanding of https://doc.qt.io/qt-5/qthread.html#finished is that this may not be possible, as "when this signal is emitted, the event loop has already stopped running". Curious thing: I tested and the slot was called in the background thread... Docs say "except for deferred deletion events", so the dtor is called in the background thread, which would be an option.

In case of QSqlDatabase (or connections that work the same way), I prefer to handle the event queue manually, to have more control. For example creating a message queue, waiting on a semaphore for new requests and processing those requests in a QThread subclass. In the run() method, the connection is created first, used during the execution, and closed just before returning from the run() method. The run() method iterates until an interruption is requested. This makes the object independent, managing its own db connection. This seems simpler, particularly when you want to concurrently query the db.

Answer 2

Option 1 does the job, for cases where you want to have the database connection stay open while the thread is alive (your circumstances might be in favor of a per-query connection instead):

Worker *worker = new Worker;
worker->moveToThread(&workerThread);
connect(&workerThread, &QThread::finished, worker, &QObject::deleteLater);

// "SignalSource" is just a placeholder for whatever class sends the signals
connect(this, &SignalSource::dataReady, worker, &Worker::updateMyFancyTable);

// Open the database connection when the thread starts, close it shortly before the end of the thread
connect(&workerThread, &QThread::started, worker, &Worker::establishDatabaseConnection);
connect(signalSource, &SignalSource::endTriggered, worker, &Worker::closeDatabaseConnection);

// Start the thread and do some work
workerThread.start();

Then when the thread is supposed to be stopped:

// Put an event for closing the database connection in the event loop
emit endTriggered();
// Tell the thread to quit as soon as its event loop is empty
workerThread.quit();
// Wait one minute for the thread to finish
workerThread.wait(60 * 1000);

Edge case: If the thread does not finish within a minute, the database connection will not be cleaned up properly. In our case, this is unlikely and acceptable, in your case this might be different.

Additional note: If you use a database connection in multiple threads, use the QSqlDatabase::addDatabase overload which accepts the driver name rather than an existing QSqlDriver* since the driver would otherwise be used from two threads, which is not supported.