Is MySQL Connector/JDBC thread safe?

Question

Is the standard MySQL JDBC driver thread-safe? Specifically I want to use a single connection across all threads, but each statement will only be used in a single thread. Are there certain scenarios that are safe and others that aren't? What's your experience here?

Solution 1

Transactions are started / committed per connection. Unless you're doing some very specific stuff (I can't really think of an example where that would be justified to be honest), you're better off with a connection pool and connection per thread.

Solution 2

Based on my recent experience, Connection object is not thread safe in Connector/J 5.1.33.

I've ran into a deadlock situation described in bug 67760. Not sure if it is a bug, but one reasonable advice from the discussion was:

[12 Dec 2012 20:33] Todd Farmer

Please do not use a single Connection object across multiple threads without proper synchronization. Connector/J - and more importantly, the MySQL client-server protocol - does not allow for concurrent operations using the same Connection object. If a Connection object must be shared across threads, it is the responsibility of the application code author to ensure operations are properly serialized.

Comments

[Graham Lea]

I wonder if there may be a possible use case where you wanted to do some kind of fork/join algorithm (c.f. ibm.com/developerworks/java/library/j-jtp11137.html) but entirely within one transaction, so you might open a connection and start a transaction in the originating thread, pass this to all the task executors and then commit in the originating thread after all joining has finished?

[Barry Kelly]

Example: streaming data insertion using load data local infile, into more than one table, where the data has mutual relations, and cannot be cheaply iterated over twice.

[Archie]

FYI, that bug is supposed fixed now in 5.1.37.