[FIXED] SPRING - JPA REPOSITORY - PESSIMISTIC_WRITE NOT WORKING - ORACLE12c

Issue

Spring-webmvc 4.1.6 / Spring-data-jpa 1.7.2 / Oracle 12c

I have my app running on localhost with debug mode in Netbeans. I use jetty-maven-plugin as a server and try to test a service method annotated with @Transactional.

Service Method.

    @Transactional("primaryTransactionManager")
    @Override
    public JsonResponseEntity<Dinero> Cancel_Deposit(Dinero dinero, EnumComportamientosDIN comportamiento_key, Boolean fromserver) {
            dateUtils = new DateRangeUtils();
                List<Dinerod> detalles = dinero.getDinerods();
                for (Dinerod detalle : detalles) {
                    Dinero origen_depo = dineroRepository.findOneByAplica(detalle.getAplica(), detalle.getAplicaid());
                    BigDecimal saldo_actual = origen_depo.getSaldo();
                    BigDecimal importe_debitado = detalle.getImporte();
                    saldo_actual = saldo_actual.add(importe_debitado);
                    origen_depo.setSaldo(saldo_actual);
                    dineroRepository.save(origen_depo);
                    
                }
    }

JPA Repository

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @QueryHints({@QueryHint(name = "javax.persistence.lock.timeout", value = "30000")})
    @Query("SELECT x FROM Dinero x WHERE x.mov = :aplica AND x.movid = :aplicaid")
    Dinero findOneByAplica(@Param("aplica") String aplica, @Param("aplicaid") String aplicaid);

I don't know if my test method is wrong but it is the following: since application is running on localhost; From another computer on the same network, I simulate that it is another person using the same application. So I run the same method with two different computers at the same time, trying to simulate a transaction crash. the same one I try to solve with PESSIMISTIC_WRITE.

But when I run the web form from two different computers at the same time, the exclusive locking of the record in the database is not done.

What should happen with PESSIMISTIC_WRITE

What is causing me headaches

Solution

I found the solution to the problem. As you said Vlad Mihalcea, in the log I could see that the query contains the FOR UPDATE clause, but I also noticed that 2 queries are executed, the first one does not have the FOR UPDATE clause and it is the one that is stored in the Entity Manager level 1 cache. The second query is the one with the FOR UPDATE.

So when 2 transactions arrive at the same time, they are left with the same entity in the cache and a dirty read of its values.

LOG:

Hibernate: select dinero0_.ID as ID1_74_, dinero0_.IDCONCEPTO as IDCONCEPTO36_74_, dinero0_.ESTATUS as ESTATUS11_74_, dinero0_.FECHAEMISION as FECHAEMISION14_74_, dinero0_.MOV as MOV20_74_, dinero0_.MOVID as MOVID21_74_, dinero0_.SALDO as SALDO31_74_, from DINERO dinero0_ where dinero0_.MOV=? and dinero0_.MOVID=?
Hibernate: select ID from DINERO where ID =? for update wait 30

Solution:

    @PersistenceContext(unitName = "XUnitName")
    private EntityManager em;


    @Transactional("primaryTransactionManager")
    @Override
    public JsonResponseEntity<Dinero> Cancel_Deposit(Dinero dinero, EnumComportamientosDIN comportamiento_key, Boolean fromserver) {
            dateUtils = new DateRangeUtils();
                List<Dinerod> detalles = dinero.getDinerods();
                for (Dinerod detalle : detalles) {
                    Dinero origen_depo = dineroRepository.findOneByAplica(detalle.getAplica(), detalle.getAplicaid());
                    em.refresh(origen_depo);
                    BigDecimal saldo_actual = origen_depo.getSaldo();
                    BigDecimal importe_debitado = detalle.getImporte();
                    saldo_actual = saldo_actual.add(importe_debitado);
                    origen_depo.setSaldo(saldo_actual);
                    dineroRepository.save(origen_depo);
                    
                }
    }

I understand that making the refresh explicit is because the Entity Manager performs 2 queries, one without FOR UPDATE and the other with FOR UPDATE.

But I would like to know why the Entity Manager does not perform a single query with the FOR UPDATE clause.

Answered By - Isaac Molina
Answer Checked By - Marie Seifert (JavaFixing Admin)