JDBC: Can I share a connection in a multithreading app, and enjoy nice transactions?

Question

It seems like the classical way to handle transactions with JDBC is to set auto-commit to false. This creates a new transaction, and each call to commit marks the beginning the next transactions. On multithreading app, I understand that it is common practice to open a new connection for each thread.

I am writing a RMI based multi-client server application, so that basically my server is seamlessly spawning one thread for each new connection. To handle transactions correctly should I go and create a new connection for each of those thread ? Isn't the cost of such an architecture prohibitive?

Answer 1

Yes, in general you need to create a new connection for each thread. You don't have control over how the operating system timeslices execution of threads (notwithstanding defining your own critical sections), so you could inadvertently have multiple threads trying to send data down that one pipe.

Note the same applies to any network communications. If you had two threads trying to share one socket with an HTTP connection, for instance.

• Thread 1 makes a request
• Thread 2 makes a request
• Thread 1 reads bytes from the socket, unwittingly reading the response from thread 2's request

If you wrapped all your transactions in critical sections, and therefore lock out any other threads for an entire begin/commit cycle, then you might be able to share a database connection between threads. But I wouldn't do that even then, unless you really have innate knowledge of the JDBC protocol.

If most of your threads have infrequent need for database connections (or no need at all), you might be able to designate one thread to do your database work, and have other threads queue their requests to that one thread. That would reduce the overhead of so many connections. But you'll have to figure out how to manage connections per thread in your environment (or ask another specific question about that on StackOverflow).

update: To answer your question in the comment, most database brands don't support multiple concurrent transactions on a single connection (InterBase/Firebird is the only exception I know of).

It'd be nice to have a separate transaction object, and to be able to start and commit multiple transactions per connection. But vendors simply don't support it.

Likewise, standard vendor-independent APIs like JDBC and ODBC make the same assumption, that transaction state is merely a property of the connection object.

Answer 2

It's uncommon practice to open a new connection for each thread. Usually you use a connection pool like c3po library.

If you are in an application server, or using Hibernate for example, look at the documentation and you will find how to configure the connection pool.