66

To rephrase the question: should I avoid sharing instances of classes which implement java.sql.Connection between different threads?

Boris Pavlović
  • 58,387
  • 26
  • 115
  • 142
  • 1
    See also [Java thread safe database connections](https://stackoverflow.com/questions/9440505/java-thread-safe-database-connections). – Vadzim Jul 19 '18 at 21:41

5 Answers5

74

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.

rev_dihazum
  • 798
  • 1
  • 8
  • 19
skaffman
  • 381,978
  • 94
  • 789
  • 754
  • 6
    For example Postgres's implementation doesn't synchronize access to the autoCommit flag so it's not thread-safe. – Boris Pavlović Oct 07 '09 at 12:00
  • 1
    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 Oct 07 '09 at 12:08
  • 12
    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 Oct 07 '09 at 12:15
  • 1
    @janko: that's the chap, thanks, glad to know I'm not going nuts – skaffman Oct 07 '09 at 12:17
  • 23
    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 Oct 08 '09 at 14:37
  • 1
    JDBC specification [4.x](https://download.oracle.com/otndocs/jcp/jdbc-4_2-mrel2-spec/index.html) has no mention of threads and thread-safety at all. – Roman Konoval Apr 14 '19 at 22:18
14

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.

Andrey Adamovich
  • 19,123
  • 13
  • 83
  • 125
  • 9
    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 Oct 07 '09 at 12:12
  • 2
    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). – Andrey Adamovich Oct 07 '09 at 13:03
  • @AndreyAdamovich : "advised to have number of connections in the pool higher than number of worker threads" why? I mean if I have many connections in connection pool I will end up with problem of Thrashing.. – Amandeep Jiddewar Feb 14 '13 at 07:29
  • 1
    @AndreyAdamovich : Thread safety is absolutely something that can be designed and enforced. Java is not a language that is particularly good at either, and in the years since that comment was written, we've seen languages that are far better about execution analysis ( such as Golang ). – Brian Bulkowski Feb 15 '16 at 21:07
  • 1
    @BrianBulkowski I guess you can't really use Golang for writing J(ava)DBC drivers – Andrey Adamovich Feb 16 '16 at 13:22
  • @AndreyAdamovich In software anything is possible, but it would seem ill advised - yes I agree. – Brian Bulkowski Feb 22 '16 at 22:16
4

This is rather an old thread, but for those who are looking for an answer regarding Microsoft SQL Server, here is the answer:

SQLServerConnection is not thread safe, however multiple statements created from a single connection can be processing simultaneously in concurrent threads.

and

SQLServerConnection implements a JDBC connection to SQL Server.

From all the above, you can share statements but not Connections, and in case you need a connection in each thread, you may use a thread pool.

Read more here

Hanash Yaslem
  • 53
  • 1
  • 9
3

Oracle JDBC and Multithreading docs:

Because all Oracle JDBC API methods are synchronized, if two threads try to use the connection object simultaneously, then one will be forced to wait until the other one finishes its use.

So it may be safe in Oracle case but concurrent access would suffer from bottleneck.

Vadzim
  • 21,258
  • 10
  • 119
  • 142
  • 2
    This was true for oracle 8i. In the newer [version](https://docs.oracle.com/cd/B19306_01/java.102/b14355/apxtips.htm#i1005436) we have `Controlled serial access to a connection, such as that provided by connection caching, is both necessary and encouraged. However, Oracle strongly discourages sharing a database connection among multiple threads. Avoid allowing multiple threads to access a connection simultaneously. If multiple threads must share a connection, use a disciplined begin-using/end-using protocol.` – Roman Konoval Apr 14 '19 at 22:11
1

We had ArrayOutOfBoundsException on the Websphere statement cache of it's pooleddatasource, and we had to disable that cache.

We had a treatment that was blocking itself.

All of that because of current access to the connection, so the conclusion by real life practice, is that you must not do that.

Mirak
  • 41
  • 6