Spring data CrudRepository and pessimistic lock

Question

I'm using

Spring Boot 1.4.2
Spring Data JPA 1.10.5
PostgreSQL 9.5 database

I want to have a findOne method with pessimistic lock in my Spring Data repository that is separate from the findOne method that is already provided.

Following this answer I wrote:

public interface RegistrationRepository extends CrudRepository<Registration, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("select r from Registration r where r.id = ?1")
    Registration findOnePessimistic(Long id);
}

This almost works.

Unfortunately, this does not refresh previous instance of my entity in the entity manager cache. I have two concurrent requests updating the status of my registration

the second one waits for the transaction of the first one to commit
the second one does not take into account the changes made by the first one.

Hence broken behavior.

Any clue why @Lock does not out of the box refresh the entity manager?

Update

Here is the requested example code:

public interface RegistrationRepository extends CrudRepository<Registration, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("select r from registration_table r where r.id = ?1")
    Registration findOnePessimistic(Long id);

}

public void RegistrationService {

    @Transactional
    public void doSomething(long id){
        // Both threads read the same version of the data 
        Registration registrationQueriedTheFirstTime = registrationRepository.findOne(id);

        // First thread gets the lock, second thread waits for the first thread to have committed
        Registration registration = registrationRepository.findOnePessimistic(id);
        // I need this to have this statement, otherwise, registration.getStatus() contains the value not yet updated by the first thread
        entityManager.refresh(registration);

        registration.setStatus(newStatus);
        registrationRepository.save(registration);
    }
}

You have to show us the code, which changes the entity-value. Why do you lock the table with 'PESSIMISTIC_WRITE' on a method which only read the entity? — Tobias Otto, May 31 '17 at 14:20
I use the code in a method annotated `@Transactional`, where I read the entity, update it, and write it back. Quite standard. I want to avoid concurrency on this operation, so I want to use a pessimistic lock. I just want to do the `select for update` before the `update`. — rcomblen, Jun 01 '17 at 06:45
The whole code block is transactional, and thus uses the same entitymanager. The `EntityManager` acts as the first level cache. You first retrieve the object without a lock, to retrieve it right again with a lock. But du to the first level cache you will retrieve that object instead of a fresh DB object. That is basically how the `EntityManager` works, if you don't want that you first have to `clear` the entity manager. Or rather why do you first retrieve it without a lock in the same tx (which is weird imho). — M. Deinum, Jun 01 '17 at 12:29
I would expect `CrudRepository` to clear that entity from the first level cache in case I specify pessimistic lock ... — rcomblen, Jun 01 '17 at 12:47
You don't need the first fetching. Delete it. Do you have any second-level cache control on your Registration entities? — Arnold Galovics, Jun 01 '17 at 13:14
I know by experience how hard is concurrency handling in online registrations, just wondering why you use pessimistic locking instead of optimistic. Are you trying to fix a double submit / two windows open problem ? — Testo Testini, Jun 04 '17 at 23:48
The question is not about how relevant pessimistic locking is in my case, the question is about why Spring-Data-JPA does not refresh the cache with the value locked in DB ... FYI I use pessimistic locking because have side effects to my status update (e.g. sending emails) that cannot be rolled back. — rcomblen, Jun 06 '17 at 05:27
@rcomblen maybe you had configured REPEATABLE_READ as DB transaction level? — Giulio Pulina, Nov 15 '21 at 13:34

score 10 · Answer 1 · answered Jun 01 '17 at 12:37

You need to use the entityManger transaction that Spring creates for you :

    @Transactional
    public void doSomething(long id){
        // Both threads read the same version of the data 
        Registration registrationQueriedTheFirstTime = registrationRepository.findOne(id);

        // First thread gets the lock, second thread waits for the first thread to have committed
        Registration registration = registrationRepository.findOnePessimistic(id);
        // I need this to have this statement, otherwise, registration.getStatus() contains the value not yet updated by the first thread
        entityManager.refresh(registration);

        EntityManager em = EntityManagerFactoryUtils.getTransactionalEntityManager(<Your entity manager factory>);
        em.refresh(registration);
        registration.setStatus(newStatus);
        registrationRepository.save(registration);
    }

}

If I use `entityManager.refresh` it just works fine. I would just expect that I would NOT need that. That code is running fine in production right now. — rcomblen, Jun 01 '17 at 12:46
@RahulGupta it seems there is no problem, rcomblen is just curios about why he has to manually refresh — hipokito, Mar 20 '19 at 16:24

Spring data CrudRepository and pessimistic lock

1 Answers1

Linked