0

Need to insert data in database using multiple threads ,but even if a single thread fails to commit,all transaction must rollback.Tried to solve this by below approach.

Sharing connection object among thread,and using join() to wait for child thread to finish,but this looks like bad design,as i am sharing connection object among threads.

Can someone suggest better design to solve this (Not sure should i go for distributed txn manager or not)?

dReAmEr
  • 6,986
  • 7
  • 36
  • 63
  • Use CountDownLatch in parent thread https://docs.oracle.com/javase/7/docs/api/java/util/concurrent/CountDownLatch.html. Also use savePoint and rollback to control the rollback and for monitoring the transaction progress https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html – Saurabh Apr 30 '17 at 18:20
  • savePoint and rollback works on connection level,and if i use multiple connection,this will only rollback that particular txn,not all txn which are currently being executed in other threads. – dReAmEr Apr 30 '17 at 18:32
  • I see, then it demands the use of some transaction manager. JBoss provides one if you are using that as app server but then it also ties your application to JBoss. I don't remember it correctly but Spring had some TM offering as well. – Saurabh Apr 30 '17 at 18:46
  • 2
    There's not a lot of point in using multiple threads. Ultimately it will be sequentialized at the database at some level. Just do all the updates in a single transaction, and roll it back if anything fails. – user207421 May 01 '17 at 00:36

2 Answers2

3

I would suggest to queue all SQL actions from multiple threads in some intermediate data structure, and then put into database from a single thread. It is possible to have the thread safe intermediate structures like ConcurrentHashMap, ConcurrentLinkedQueue or you may just synchronize when working with it.

This way you even do not need to start the transaction in advance. The pending data may be less safe, but I assume they are not a lot safer in the database while the transaction is not committed yet.

Of course, this can only work if you do not have select statements picking uncommitted transaction data from the same transaction. Getting rid of such queries one or another way may require redesign.

Use CountDownLatch to detect when all data are ready and the database writing thread should start its action. If never happens, use reactor pattern for the database writing thread.

Audrius Meškauskas
  • 20,936
  • 12
  • 75
  • 93
2

Here are my quick thoughts with possible implementation steps:

  • Your parent processing thread will (1.) create new threads to do parallel DB inserts, (2.) create a CountDownLatch object (this will hold your parent thread until all child threads which are doing db inserts are finished) (3.) create a db connection object with auto commit mode as FALSE.
    • Suppose you are spawning 6 threads to do parallel DB inserts then you will create CountDownLatch object like this CountDownLatch countDownLatch = new CountDownLatch(6) and then spawn your parallel threads and do countDownLatch.await()
  • Your parallel threads will start inserting into DB, but key thing is that each of them is using db connection object in auto commit FALSE mode which was provided by parent thread, so basically no child thread will do the db commit.
    • Once each child thread is done they will do countDownLatch.countDown(); to decrement the latching counter.
    • Please note that you need to make available the countDownLatch as well as db connection object to each thread, I am sure you would know how.
  • Once latch counter reaches 0, your parent thread execution will start again (until latch counter is not 0, countDownLatch.await() will hold the thread) and then (1.) you can decide whether to commit or not based on result from each thread (2.) close the connection object. Now, Runnable doesn't return anything so better use Callable so that each thread can inform about their status.

If you are using Spring then it can ease your work with its transactional feature, but that becomes different story.

Now, few points about what you mentioned in your question - you mentioned "even if a single thread fails to commit,all transaction must rollback", basically if any of your db insert/access fails then you do not want to commit anything, so your Callable will return the status of their execution, I am not sure what else you could mean by this but I think if you have got the point about Callable then you should be fine. Also, you mentioned "but this looks like bad design,as i am sharing connection object among threads.", you will need to share the db connection object because once a transaction is committed you cannot rollback, so it you do not want to share the connection object then probably you need to have set of SQL statement to undo the work done by earlier db access and their commits.

hagrawal7777
  • 14,103
  • 5
  • 40
  • 70