To rephrase the question: should I avoid sharing instances of classes which implement java.sql.Connection between different threads?
+7
A:
If the JDBC driver is spec-compliant, then technically yes, the object is thread-safe, but you should avoid sharing connections between threads, since the activity on the connection will mean that only one thread will be able to do anything at a time.
You should use a connection pool (like Apache Commons DBCP) to ensure that each thread gets its own connection.
skaffman
2009-10-07 11:40:50
For example Postgres's implementation doesn't synchronize access to the autoCommit flag so it's not thread-safe.
Boris Pavlović
2009-10-07 12:00:20
A voice at the back of my head is telling me that the JDBC spec requires all java.sql objects to be thread-safe, but I can't find a reference to that.
skaffman
2009-10-07 12:08:15
Your voice may refer to http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec/jdbc-spec.frame9.html where it says "We require that all operations on all the java.sql objects be multi-thread safe and able to cope correctly with having several threads simultaneously calling the same object."
janko
2009-10-07 12:15:49
@janko: that's the chap, thanks, glad to know I'm not going nuts
skaffman
2009-10-07 12:17:14
On that Sun JDBC guide language you quote, you should have quoted the final, bolded sentence. I read it as them admitting that multithreading is mostly a failure and one thread per connection is the current expectation. "In practice we expect that most of the JDBC objects will only be accessed in a single threaded way. However some multi-thread support is necessary, and our attempts in previous drafts to specify some classes as MT safe and some as MT unsafe appeared to be adding more confusion than light."
John M
2009-10-08 14:37:42
+3
A:
java.sql.Connection is an interface. So, it all depends on the driver's implementation, but in general you should avoid sharing the same connection between different threads and use connection pools. Also it is also advised to have number of connections in the pool higher than number of worker threads.
Superfilin
2009-10-07 11:47:07
An interface is a contract, and a contract *could* specify that all implementations have to be thread safe. It's just that this is not the case for java.sql.Connection.
Wim Coenen
2009-10-07 12:12:59
Yes, interface is a contract and you can put some additional requirements in the documentation that describes the contract, but as you said java.sql.Connection documentation does not define thread-safety requirement, and even if it defined that, still thread-safety is not something that can be strictly described and enforced. Implementation may still violate the contract (sometimes by mistake, sometimes by design e.g. IdentityHashMap).
Superfilin
2009-10-07 13:03:47