12

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} }
Improve this question
5
  • connection object is not threadsafe, sharing it with multiple threads can cause issues Commented Sep 19, 2013 at 14:14
  • Is there a question here? If you'd like code review please post to codereview.stackexchange.com Commented Sep 19, 2013 at 14:15
  • Would you please elaborate a little more? Commented Sep 19, 2013 at 14:15
  • 1
    Looks like while connection may be technically thread-safe, you still won't want to pass it between threads: stackoverflow.com/questions/1531073/… Commented Sep 19, 2013 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. Commented Sep 19, 2013 at 17:06

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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

Improve this answer

1 Comment

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

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.