Hibernate synchronizing updates


#1

Hi All,

I have an engine which generates price change for an item.
There is a scenario where there are two price change events separated by 10 milliseconds for a particular item ID = 1

Record in DB

itemId = 1, price = 35 , lastUpdated = Thu Jun 28 6:00:00 2018

I am using Spring data JPA along with hibernate to update the record in the DB

Thread1 entered the below method with itemId = 1 , price = 40 , date = Thu Jun 28 20:05:39 620 2018
Thread2 entered the below method with itemId = 1, price = 42 , date = Thu Jun 28 20:05:39 630 2018

Both the threads have events that differ by 10milli seconds.

How can I ensure that the latest price (42) is always updated in the database. Because there is a good chance that both Thread1 & Thread 2 entered the update method and found that the lastUpdatedTime of the record in the database is Thu Jun 28 6:00:00 2018 and both go ahead and commit to the database.

In this process there is a good chance that Thread1 commits after Thread2 and the latest price update which is 42 will be over written by Thread1 to 40

(other than synchronizing the method updateItemPrice - is there a solution )

@Transactional
public void updateItemPrice(String itemId,  double price, Date date) {
          Item item = itemsRepository.findOne(itemId);
          // check if event time is later than the one in the DB.
         if(date.getTime() > item.getLastUpdated().getTime() ) {
             item.setPrice(price);
             itemsRepository.save(item);
         }
}

Is it a good idea to write a native query in my spring data JPA repository (itemsRepository) like below
so that DB level locking is achieved

@Transactional(readOnly=false)
@Modifying
@Query("UPDATE Item i set  i.price= ?3  WHERE i.id =?1   " +
        "AND i.lastUpdated < ?2  ")
public void updateItemPrice( int itemId, Date date, double price );

TIA


#2

The second option is correct:

@Transactional(readOnly=false)
@Modifying
@Query("UPDATE Item i set  i.price= ?3  WHERE i.id =?1   " +
        "AND i.lastUpdated < ?2  ")
public void updateItemPrice( int itemId, Date date, double price );

It’s similar to how optimistic locking works, just that in your case you are not throwing an exception but rather linearize the updates based on the date parameter.