Sharing a jdbc "Connection" across threads

Question

I have a main thread that runs periodically. It opens a connection, with setAutoCommit(false), and is passed as reference to few child threads to do various database read/write operations. A reasonably good number of operations are performed in the child threads. After all the child threads had completed their db operations, the main thread commits the transaction with the opened connection. Kindly note that I run the threads inside the ExecutorService. My question, is it advisable to share a connection across threads? If "yes" see if the below code is rightly implementing it. If "no", what are other way to perform a transaction in multi-threaded scenario? comments/advise/a-new-idea are welcome. pseudo code...

Connection con = getPrimaryDatabaseConnection();
// let me decide whether to commit or rollback
con.setAutoCommit(false);

ExecutorService executorService = getExecutor();
// connection is sent as param to the class constructor/set-method
// the jobs uses the provided connection to do the db operation
Callable jobs[] = getJobs(con); 
List futures = new ArrayList();
// note: generics are not mentioned just to keep this simple
for(Callable job:jobs) {
    futures.add(executorService.submit(job));
}
executorService.shutdown();
// wait till the jobs complete
while (!executorService.isTerminated()) {
  ;
}

List result = ...;
for (Future future : futures) {
    try {
       results.add(future.get());
    } catch (InterruptedException e) {
      try {
        // a jobs has failed, we will rollback the transaction and throw exception
        connection.rollback();
        result  = null;
        throw SomeException();
      } catch(Exception e) {
       // exception
      } finally {
         try {
           connection.close();
         } catch(Exception e) {//nothing to do}
      }    
   }
}
// all the jobs completed successfully!
try {
  // some other checks
  connection.commit();
  return results;
} finally {
  try {
      connection.close();
  } catch(Exception e){//nothing to do}
}

connection object is not threadsafe, sharing it with multiple threads can cause issues — coder, Sep 19 '13 at 14:14
Is there a question here? If you'd like code review please post to http://codereview.stackexchange.com/ — Dev, Sep 19 '13 at 14:15
Looks like while connection may be technically thread-safe, you still won't want to pass it between threads: http://stackoverflow.com/questions/1531073/is-java-sql-connection-thread-safe — Victor Sorokin, Sep 19 '13 at 14:59
I forgot to mention a point in my question, FYI, this module runs inside the Jboss-5, where the ServletContextListener starts the main scheduler thread, and importantly, the main thread fetch the connection from the configured datasource pool. — vvra, Sep 19 '13 at 17:06

score 3 · Answer 1 · answered Sep 19 '13 at 14:31

3

I wouldn't recommend you to share connection between threads, as operations with connection is quite slow and overall performance of you application may harm.

I would rather suggest you to use Apache Connections Pool and provide separate connection to each thread.

answered Sep 19 '13 at 14:31

nkukhar

1,975
2
18
37

Okay. If I can use the connection pool, how do I maintain a transaction, the transaction that succeeds only at the successful end of all the child threads, or the transaction rollbacks? Each thread get its connection from the connection pool? – vvra Sep 19 '13 at 14:50
Well, you can save connections retrieved from the pool in some shared collection, and do whatever you want with them after threads finish execution. But that is very poor way of managing transactions. I would prefer using spring transaction manager for instance. But note that it doesn't support transactions [inside executor tasks](http://stackoverflow.com/questions/10407301/how-to-use-spring-transaction-in-multithread) – nkukhar Sep 19 '13 at 15:56
Okay. Rather every worker threads fetch data, operate on it and inserts back to the database, I would like to change the workers to return all their processed data out to the main thread, such that, if I received all their objects rightly I update to the database everything in a single connection, the connection I fetch from the pool. – vvra Sep 20 '13 at 04:59

mike · Answer 2 · 2013-09-19T14:31:01.957

1

You could create a proxy class that holds the JDBC connection and gives synchronized access to it. The threads should never directly access the connection.

Depending on the use and the operations you provide you could use synchronized methods, or lock on objects if the proxy needs to be locked till he leaves a certain state.

For those not familiar with the proxy design pattern. Here the wiki article. The basic idea is that the proxy instance hides another object, but offers the same functionality.

edited Sep 19 '13 at 14:31

answered Sep 19 '13 at 14:18

mike

4,929
4
40
80

I too had this idea in my mind, but the synchronized execution is all I worried, so I just didn't give it a try. – vvra Sep 20 '13 at 04:44

score 1 · Answer 3 · answered Sep 19 '13 at 18:01

In this case, consider creating a separate connection for each worker. If any one worker fails, roll back all the connections. If all pass, commit all connections.

If you're going to have hundreds of workers, then you'll need to provide synchronized access to the Connection objects, or use a connection pool as @mike and @NKukhar suggested.

Sharing a jdbc "Connection" across threads

3 Answers3