Multiple consumers trying to update same row of Mysql(5.7 version) table. How to fix concurrent update issue

Question

Multiple consumers are trying to update 'processedRecords' and 'erredRecords' fields of JobTracker. But I am facing issue where random values updated in these columns. Mysql is on default isolation level(MySQL version 5.7)

I thought of locking row by using PESSIMISTIC_WRITE. But following code doesn't give any exception and also row values are not updated. Someone please help me in resolving this.

Please note: 1. Any solution that changes isolation level for entire DB is not helpful as it would effect other flows in the application. 2. Both 'processedRecords' and 'erredRecords' are of type Long.

@Override
@Transactional
public Boolean updateRecords(Long jobTrackerId, Long processedRecords, Long erredRecords) {
    try{
        JobTracker jobTracker = getEntityManager(true).find(JobTracker.class, jobTrackerId);
        getEntityManager(true).lock(jobTracker, LockModeType.PESSIMISTIC_WRITE);
        if(jobTracker == null){
            return false;
        }
        if(processedRecords!=null){
            jobTracker.setProcessedRecords(processedRecords);
        }
        if(erredRecords!=null){
            jobTracker.setErredRecords(erredRecords);
        }
        if(jobTracker.getErredRecords() != null && jobTracker.getProcessedRecords() != null && jobTracker.getTotalRecords() != null){
            if(jobTracker.getErredRecords() + jobTracker.getProcessedRecords() == jobTracker.getTotalRecords()){
                jobTracker.setStatus("Completed");
            }else if(jobTracker.getErredRecords() + jobTracker.getProcessedRecords() > jobTracker.getTotalRecords()){
                jobTracker.setStatus("Erred");
            }
        }
        getEntityManager(true).merge(jobTracker);
        return true;
    }catch(PersistenceException e){
        logger.error("For job id:" + jobTrackerId + " Exception while updating the jobTracker records data", e);
        return false;
    }catch (Exception e){
        logger.error("For job id:" + jobTrackerId + "Generic Exception while updating the jobTracker records data",e);
        return false;
    }
}

When using `EntityManager` directly, I have always used `EntityManager.find(Class, Object, LockModeType)`. Can you try that? — manish, Oct 08 '18 at 10:14
Yes, i tried with that. But faced same issue. I could't find any exception in logs. But values were not updating. Basically logs after find was not logging. — AUK, Oct 08 '18 at 10:57

Alain Cruz · Answer 1 · 2018-10-08T11:45:18.763

0

Using the @Transactional annotation we can set the isolation level of a Spring managed transactional method. This means that the transaction in this method is executed will have that isolation level. In your case, all you have to do is the following.

@Transactional(isolation=Isolation.READ_COMMITTED)

Now if you were to set the isolation level for your whole DB you would do the following.

<property name="hibernate.connection.isolation">2</property>

edited Oct 08 '18 at 11:45

answered Oct 05 '18 at 23:49

Alain Cruz

4,757
3
25
43

Thanks for the reply. In my scenario i can't change isolation level for entire DB as it will effect other flows. I wanted to modify for this particular DB call. – AUK Oct 08 '18 at 10:04
Done, I just updated my answer. Now you should be able to set the isolation level for a specific transaction using Spring transactional manager. Please, let me know if it helps. – Alain Cruz Oct 08 '18 at 11:30
Yes i tried with this annotation too. But the problem is, this annotation works for 4.2 + . Unfortunately our application uses 3.6. We get this exception if we use annotation in 3.6 `HibernateJpaDialect does not support custom isolation levels since the \u0027prepareConnection\u0027 flag is off. This is the case on Hibernate 3.6 by default; either switch that flag at your own risk or upgrade to Hibernate 4.x, with 4.2+ recommended`. – AUK Oct 09 '18 at 06:27

Multiple consumers trying to update same row of Mysql(5.7 version) table. How to fix concurrent update issue

1 Answers1