Spring native query executed within a transaction taking outdated value

Question

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.

Population

Answer 1

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..

Population

Answer 2

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.

Unwashed

Hi, sorry for the delay. Actually, the problem is not stored-procedure specific, but seems that it has more to do with many tables being involved in the same transaction (it takes old table values). More or less that's the execution flow: 1. Update the revision using Hibernate. 2. Execute the updateEquipmentStatus method, which fires a modifying native query (this query uses some data involved in the step 1, which is outdated). – Population

Answer 3

@Transactional
@Modifying(clearAutomatically = true)
@Query(value = "CALL PR_CALC_PAYMENT(:id_payment)", nativeQuery = true)
void reportPayment(@Param("id_payment") Long paymentId);

This works for me, this annotation updates the persistence context. I noticed that if I call the method that calls the procedure, since it is a transaction the persistence context will not be updated until the method terminates.

https://www.baeldung.com/spring-data-jpa-modifying-annotation

Calif

Thanks for your response! Years later, I whish I could test it, hope to be useful for anybody, after all! – Population