Is java.sql.Connection thread safe?

Question

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

Ovolo

See also Java thread safe database connections. – Rasheedarasher

Answer 1

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.

Counterglow

For example Postgres's implementation doesn't synchronize access to the autoCommit flag so it's not thread-safe. – Zoara

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. – Counterglow

Your voice may refer to java.sun.com/j2se/1.3/docs/guide/jdbc/spec/… 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." – Hornpipe

@janko: that's the chap, thanks, glad to know I'm not going nuts – Counterglow

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." – Placket

JDBC specification 4.x has no mention of threads and thread-safety at all. – Berkey

Answer 2

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.

Vacant

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. – Hengist

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). – Vacant

@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.. – Reynolds

@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 ). – Cordle

@BrianBulkowski I guess you can't really use Golang for writing J(ava)DBC drivers – Vacant

@AndreyAdamovich In software anything is possible, but it would seem ill advised - yes I agree. – Cordle

Answer 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.

Rasheedarasher

This was true for oracle 8i. In the newer version 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. – Berkey

Answer 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

Bibliophage

Answer 5

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.

Redon