Asynchronous Database Access with Qt 4.x

How to code around the default synchronous database access in Qt 4.

The database support in Qt 4.x is quite robust. The library includes drivers for Oracle, PostgreSQL, SQLite and many other relational databases. Out of the box, the Qt database library also contains bindings for many widgets and provides data types for the transparent handling of result sets coming from a database. But, your application can pay a price for these conveniences. All database access is synchronous by default, which means that intensive and time-consuming SQL queries normally will lock up the UI unless precautions are taken. Using stored procedures on the server can sometimes help the situation; however, this is not always possible or desirable. And often, the length and cost of the queries generated by your application simply cannot be known in advance, so the door is left open for undesirable UI behavior. People don't want their application to “lock up” at odd moments; however, this is the default behavior, and so we must contend with it.

Fortunately, Qt 4.x also has robust support for multithreaded programming. By placing the heavy-duty database work in separate threads, the UI is free to respond to the user normally, without ungraceful interruptions. As with all concurrent programming, however, you must take precautions to ensure the correct sequence of interactions between threads. For example, when sharing data among threads, guard it properly using mutexes. When communicating between threads, consider carefully how the interaction will behave, and in what sequence. In addition, when utilizing a database connection within a thread separate from the UI thread, you must pay attention to some extra caveats. A proper implementation that keeps certain things in mind will make significant improvements in the UI behavior and responsiveness of a database application.

Thread Strategies

There are several ways to distribute the database load to separate threads of execution. Fortunately, all of them share the same characteristics when it comes to the details of creating and using a database connection properly. The primary consideration is to use a database connection only within the thread that created it. For regular synchronous applications, the default behavior is fine. The QSqlDatabase::addDatabase() static function creates a database connection within the context of the application's main UI thread. Queries executed within this same thread will then cause blocking behavior. This is to be expected.

In order to run queries in parallel with the main UI thread, so that they do not interrupt the main event processing loop, a database connection must be established in the thread in which the query executes, which should be separate from the main UI thread. However you structure the threading in your application, your design must be able to establish a connection within the context of each thread that will be performing database work.

For example, creating a thread pool in which a few threads handle the load of querying the database in a round-robin fashion (without the overhead of creating and destroying threads all the time) will push the time-consuming work outside the main event loop. Or, depending on the needs of your application, you simply can spawn threads on an as-needed basis to perform database work. In either case, you must create a connection per thread.

There is a further limitation (imposed by most of the underlying database-specific libraries used by Qt). As a general rule, connections cannot be shared by multiple threads. This means you cannot simply create a pool of connections on startup and hand them out to various threads as needed. Instead, each thread must establish and maintain its own connection, within its own context. To do otherwise is undefined, and probably disastrous. Multiple separate connections can be established in each thread by using the name parameter of the QSqlDatabase::addDatabase() static function, as shown in Listing 1.

In Listing 1, the thread objects establish two different database connections. Each connection is named separately, so that QSqlDatabase can maintain them properly in its internal list. And, most important, each connection is established within the separate thread of execution of each object—the run() method is invoked by QThread::start() once the new thread of execution is launched. The mechanism provided by QSqlDatabase to create new connections is thread-safe. Listing 2 shows another example of a more generic approach.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

copying overhead?

Anonymous's picture

First off, great article Dave!

I have one remark though, and it concerns QByteArray QMetaObject::normalizedSignature ( const char * method ) and it's explanation:

“Qt uses normalized signatures to decide whether two given signals and slots are compatible. Normalization reduces whitespace to a minimum, moves 'const' to the front where appropriate, removes 'const' from value types and replaces const references with values.”

As far as I understand it, the signal will copy the whole result on each emit. Now, thats not a good idea for very large queries

So I wondered whether it would be better to create the result on the heap and to pass it's pointer with the signal? Would that introduce new complications?

Kind regards,
Davor

Thank you.

Andy Fillebrown's picture

Thanks for the article. I was looking into calling slots in a thread-safe manner and suspected correctly that the way to do it is via the QThread event loop. I did not realize, though, that it is possible to abstract the slots to a worker thread the way you described in the article. Knowing this now has saved me a lot of time.

Thanks again,
~ andy.f

Great article!

Antonio Menezes's picture

Thank you for your article. You have answered this question in Qt, about how to get asynchronous databases connections and operations.

Regards,
Antonio.

What about QT's MVC framework?

Peter's picture

Great article! I was very glad to find something that goes beyond the the basic Trolltech documentation.

Do you have any comments or advice regarding asynchronous DB access that takes advantage of QT's SQL model/view framework?

I have found that QTableView and QSqlQueryModel are wonderfully easy to work with except in the case when the GUI is blocked by while waiting for more complex queries to return their results.

I have played around with creating and executing a QSqlQuery inside a worker thread and then using a signal to pass the QSqlQuery object to the GUI thread when the worker thread finishes. However, this causes unpredictable crashes that I think are related to breaking the rule that connections must only be used in the threads that created them. (My QSqlQueryModel lives in the GUI thread, but the QSqlQuery that provides the model with data was created with a database connection that lives in the worker thread)

After reading your article I suspect that I should instead create a new model (subclass of QAbstractTableModel) that uses a QList for its data instead of a QSqlQuery.

Thanks for sharing any experience or comments!

Correct URL for sample application

dberton's picture

The correct URL for the sample application is ftp.linuxjournal.com/pub/lj/listings/issue158/9602.tgz

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix