Wednesday, March 8, 2017

Spring native query executed within a transaction taking outdated value

By Hường Hana 1:30 AM , , , Leave a Comment

I'm using Spring Boot (1.4.4.REALEASE) with Spring Data in order to manage a MySql Database. I've got the following case:

  1. We update one revision performed in one equipment using the RevisionService.
  2. RevisionService saves the revision and calls the EquipmentService to update the equipment status.
  3. The updateEquipmentStatus does a call to a Db stored procedure in order to evaluate the equipment with its revisions altogether and update the field.

I've tried some options but don't achieve to get the updated status for the equipment. The updateEquipmentStatus method keeps writing the previous status for the equipment (not considering the current revision being stored in the transaction). The code is written this way:

RevisionService

@Service public class RevisionService{      @org.springframework.transaction.annotation.Transactional     public Long saveRevision(Revision rev){         //save the revision using JPA-Hibernate         repo.save(rev);         equipmentService.updateEquipmentStatus(idEquipment);     } }

EquipmentService

@Service public class EquipmentService{      @org.springframework.transaction.annotation.Transactional     public Long updateEquipmentStatus(Long idEquipment){         repo.updateEquipmentStatus(idEquipment);     } }

EquipmentRepo

@Repository public interface EquipmentRepo extends CrudRepository<Equipment, Long> {      @Modifying     @Procedure(name = "pupdate_equipment_status")     void updateEquipmentStatus(@Param("id_param") Long idEquipment);  }

As far as I understand, as both methods are annotated with Spring's transactional, the updateEquipmentStatus method should be executed in the scope of the current transaction. I've also tried with different options for the @Transactional annotation from updateEquipmentStatus, such as @Transactional(isolation=Isolation.READ_UNCOMMITTED) (which shouldn't be required, because I'm using the same transaction) and @Transactional(propagation=Propagation.REQUIRES_NEW), but keeps not considering the current status. That's how my stored procedure is saved into the MySql DB:

CREATE DEFINER=`root`@`localhost` PROCEDURE `pupdate_equipment_status`(IN `id_param` INT)     LANGUAGE SQL     NOT DETERMINISTIC     MODIFIES SQL DATA     SQL SECURITY DEFINER     COMMENT '' BEGIN  /*Performs the update considering tequipment and trevision*/  /*to calculate the equipment status, no transaction is managed here*/  END

I also want to clarify that if I execute some modification in the equipment itself (which affects only tequipment), the status is being properly updated. InnoDb is the engine being used for all the tables.

UPDATE

Just changed the repo method to use a nativeQuery instead and the same problem keeps happening, so the Db procedure being involved should be discarded:

@Modifying @Query(nativeQuery = true, value= "update tequipment set equipment_status = (CASE WHEN (...))") void updateEquipmentStatus(@Param("id_param") Long idEquipment);

UPDATE2

Having done more tests and added a log with TransactionSynchronizationManager.getCurrentTransactionName() in the methods, that's the concrete issue:

  • Changes done in the equipment service are properly picked by the updating function (When something in tequipment changes, the status in tequipment is calculated properly).
  • Changes done in the revision service (trevision) result in an outdated value in tequipment (it doesn't matter if Spring does it in a different transaction using REQUIRES_NEW or not). Spring seems to create a new transaction properly when using REQUIRES_NEW in establishEquipmentStatus, because the current transaction name changes, but the native query doesn't have the latest values (because of the transaction before not being commited?). Also tried removing @Transactional from establishEquipmentStatus so the same transaction is used, but the issue keeps happening.
  • I would like to highlight that the query used to update equipment status has a case expression with multiple subqueries using trevision.

UPDATE3

Adding the following code fixes it (programatically flushing the transaction state to the Database):

@Service public class EquipmentService{      @PersistenceContext     private EntityManager entityManager;      @org.springframework.transaction.annotation.Transactional     public Long updateEquipmentStatus(Long idEquipment){         entityManager.flush();         repo.updateEquipmentStatus(idEquipment);     } }

Still it would be great to find a declarative way to do it..

1 Answers

Answers 1

Changing to read uncommitted is the right idea but you'd also need to flush the entitymanager before your stored procedure is called. See this thread:

How to make the queries in a stored procedure aware of the Spring Transaction?

Personally I'd do it all in Spring unless you are absolutely forced to use a stored procedure.

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment