16

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?

Emil H
  • 39,840
  • 10
  • 78
  • 97
  • 3
    "..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.." http://forums.mysql.com/read.php?39,171022,171195#msg-171195 – Tim Jul 30 '09 at 22:19
  • 1
    @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 Jul 30 '09 at 22:23
  • 1
    Consider using a connection pool instead. – Dana the Sane Jul 30 '09 at 22:32

3 Answers3

11

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.

ChssPly76
  • 99,456
  • 24
  • 206
  • 195
  • 1
    I wonder if there may be a possible use case where you wanted to do some kind of fork/join algorithm (c.f. http://www.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? – Graham Lea Nov 11 '10 at 06:30
  • 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. – Barry Kelly Aug 19 '13 at 12:25
4

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

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.

Alex
  • 367
  • 1
  • 3
  • 17