PESSIMISTIC_WRITE locking not working with Spring JPA Repository and Oracle 12c

user_9823403 0
Viewed 252

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 the 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 should happen with PESSIMISTIC_WRITE

What is causing me headaches What is causing me headaches

spring-mvc jpa oracle hibernate java
2 Answers

I'm sure it's working just fine.

Just enable SQL logging, and check out the executed statement. It will contain the FOR UPDATE clause, meaning it's working just fine.

There are plenty of tests I wrote myself for Hibernate that would fail if this feature would not work, so I doubt that Hibernate will release a version where this is not supported.

You don't need to run this with 2 computers to make it work. You can write an integration test for it, like this one.

user_10251180

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.

user_98234030
Related