Monday, August 29, 2011

Avoiding dirty reads in a concurrent environment

Recently, I was facing issue of handling concurrent access and modifications to database using JPA. Most of the time inserts where happening based on dirty reads or unique key violations were happening, thereby making the data inconsistent. So, to overcome this, I used 2 separate things. Firstly, delegated few tasks to central db, by using before insert triggers (need to refresh the entity after persist in case of pre-insert trigger). Secondly, introduced version based modifications. Here is a nice article which explains this : http://java.dzone.com/articles/jpa-20-concurrency-and-locking.

One can use application based optimistic locks or db row level based pessimistic locks. While using locks you need to handle errors like lock timeout or optimistic lock exceptions and do multiple retries with some random short sleeps. It is important to remember that for most of the exceptions, jpa marks the transaction for rollbackonly, so one needs to begin a fresh transaction after exception, to have proper commits.