Does MySQL Connector/J Support Multithreaded Programming?

Mark Matthews

Users often ask me if our JDBC driver supports multithreaded programming. The answer I always give is a qualifed 'yes'....'but you shouldn't be doing it!'.

Although the JDBC API requires that JDBC drivers support multithreaded access, the JDBC API itself is not designed to be used in a multithreaded way. It is only intended that multithreaded access will not cause the driver to enter an 'unknown' state with regards to communications to the database.

Database operations are logically broken down into units of work. Whether or not this unit of work should be transactional or not depends on your business requirements. In any case, one thread of control will usually expect to be able to perform multiple operations in a unit of work, and not have the state of the database or objects created through the JDBC API change state in non-deterministic ways while it is doing its work.

This 'isolation' is not possible when you have multiple threads access the same java.sql.Connection or java.sql.Statement instance, unless you build complicated locking algorithms into your own code. Both java.sql.Connection and java.sql.Statement have requirements that cause their internal state to change on certain events, which also has side-effects on object instances they created.

An example of this behavior is that java.sql.Statement requires that ResultSet instances that are open are explicitly closed when a new query is executed on the Statement. This leads to a race condition when you use a Statement instance from more than one thread at a time and are processing ResultSets. In some cases a thread can complete processing a ResultSet before another thread issues a query, and other times it can not. There are many other similar behaviors buried in the guts of the JDBC specification.

By the time you add all of the necessary synchronizations to be able to safely use the JDBC API from multiple threads, you'll find that there is no performance increase, and you have introduced complex code that is hard to maintain and debug.

The answer to this problem is to realize that a unit of work is scoped by a java.sql.Connection instance. In fact, even transaction control is scoped to a java.sql.Connection, via Connection.setAutoCommit(), Connection.commit() and Connection.rollback(). Therefore, let only one thread at a time use a Connection instance to do its unit of work. There are multiple strategies for doing this, from giving each thread its own Connection instance, or sharing Connection instances via a Connection pool. Whichever strategy you choose, you will find that your code is easier to maintain, the performance will be as good or better than using multiple threads that share Connections, and as a bonus, your code will be more robust, and much easier to debug!

Hopefully, this article has shown you ways to avoid that temptation to use multiple threads with a shared JDBC connection! Stay tuned for the next newsletter, where I'll cover JDBC Connection pooling in-depth!