2

I am trying to synchronize threads in my SpringBoot application through pessimistic locks in the database. For the test environment, I use HSQLDB. But for some reason, when calling the SELECT ... FOR UPDATE query, my all threads receive the result at the same time and continue to execute further in parallel. Although I expect threads will lock row sequentially. I use JPA and my code to get a pessimistic lock is very simple:

private EntityManager entityManager;
...

@Transactional(propagation = Propagation.REQUIRES_NEW)
public void doSomething() {
    sync();
    ...
}

public void sync() {
    MyEntity entity = entityManager.find(MyEntity.class, entityId, LockModeType.PESSIMISTIC_WRITE);
    log.debug("entity {} was locked", entity.getKey());
}

I use HSQLDB 2.6.1 and MVCC as concurrency control model. Does HSQLDB support pessimistic row locks?

Andrey Biryukov
  • 21
  • 2
  • Here is a possible answer to your problem: https://stackoverflow.com/questions/21977551/pessimistic-row-locking-with-hsqldb – Lennier Nov 23 '21 at 07:22
  • @Lennier, thank you! Thus, expression `SELECT ... FOR UPDATE` has no meaning when MVCC is enabled? HSQLDB doesn't allow to lock individual rows, only the entire table? – Andrey Biryukov Nov 23 '21 at 11:14
  • Correct, the entire table is locked in the LOCKS transaction model. – fredt Nov 23 '21 at 14:02

0 Answers0