Is MySQL Connector/JDBC thread safe?

16
votes

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?

3
javamysqljdbcconcurrencythread-safety
"..While you can share a connection across threads (especially if each thread has its own Statement), it's usually not a good idea. The JDBC API is not really designed to be used in a thread-safe way, and most JDBC connections (including MySQL's) can only process a single query at a time.." forums.mysql.com/read.php?39,171022,171195#msg-171195 – Tim
@Tim, Yes, I saw the post earlier. It doesn't go into details about any possible problems though, so I felt that it would be interesting to hear about other peoples experiences. Also, I felt that it's a valid question that belonged in the stack overflow question database. Feel free to post that link as an answer. :) – Emil H
Consider using a connection pool instead. – Dana the Sane

3 Answers

11
votes

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.

4
votes

If autocommit = 1, then it is very feasible to have multiple threads share the same connection, provided the access to the connection is synchronized. If autocommit = 0, you will have to control access to the connection via some sort of mutex until the commit happens.

Unless you absolutely are limited in the amount of connections your application can have, a connection pool may be a more viable alternative.

1
votes

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.