title: Sharing a jdbc "Connection" across threads
body: <p>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...</p>
  
  <pre><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}
  }
  </code></pre>