How to properly handle two threads updating the same row in a database

Question

I have a thread called T1 for reading a flat file and parsing it. I need to create a new thread called T2 for parsing some part of this file and later this T2 thread would need to update the status of the original entity, which is also being parsed and updated by the original thread T1.How can I handle this situation?

I receive a flat file having the below sample records:

AAAA
BBBB
AACC
BBCC
AADD
BBDD

First this file is saved in database in Received status. Now all the records starting with BB or with AA need to be processed in a separate thread. Once it's successfully parsed, both threads will try to update the status of this file object in a database to Parsed. In some cases, I get staleObjectException. Edit: And the work done by any thread before the exception is lost. We are using optimistic locking. What is the best way of avoiding this problem?

Possible hibernate exceptions when two threads update the same Object?

The above post helps to understand some part of it, but it does not help to resolve my problem.

Answer 1

Part 1 - Your problem

The main reason for you receiving this exception is that you are using Hibernate with optimistic locking. This basically tells you that either thread T1 or thread T2 have already updated the state to PARSED and now the other thread is holding old version of the row with smaller version than the one held in the database and trying to update the state to PARSED as well.

The question here is "Are the two threads trying to preserve the same data?". If the answer is yes then even if the last update succeed there shouldn't be any problem, because eventually they are updating the row to the same state. In that case you don't need Optimistic locking because your data will, in any case be in sync.

The main problem comes if after the state is set to RECIEVED if the two threads T1 and T2 actually depending on one another when reseting to the next status. In that case you need to ensure that if T1 has executed first(or vice versa) T2 needs to refresh the data for the updated row and re-apply its changes based on the changes already pushed by T1. In this case the solution is the following. If you encounter staleObjectException you basically need to refresh your data from the database and restart your operation.

Part 2 analysis on the link posted Possible hibernate exceptions when two threads update the same Object? Approach 1, this is more or less the last to update Wins situation. It more or less avoids the optimistic locking (the version counting). In case you don't have dependency from T1 to T2 or reverse in order to set status PARSED. This should be good.

Aproach 2 Optimistic Locking This is what you have now. The solution is to refresh the data and restart your operation.

Aproach 3 Row level DB lock The solution here is more or less the same as for approach 2 with the small correction that the Pessimistic lock dure. The main difference is that in this case it may be a READ lock and you might not be even able to read the data from the database in order to refresh it if it is PESSIMISTIC READ.

Aproach 4 application level synchronization There are many different ways to do synchronization. One example would be to actually arrange all your updates in a BlockingQueue or JMS queue(if you want it to be persistent) and push all updates from a single thread. To visualize it a bit T1 and T2 will put elements on the Queue and there will be a single T3 thread reading operations and pushing them to the Database server.

If you use application level synchronization you should be aware that no all structures can be distributes in a multi-server deployment.

Well I can't think of anything else for now :)