0

I'm trying to work with pessimistic locks in Spring Data JPA and Postgresql but pessimistic lock is not working as expected.

Tried to concurrently update a record using pessimistic lock, since pessimistic lock should block until lock is available, was expecting concurrent transactions to block instead of getting error, but when I run the update via multiple threads get the following error:

ERROR 67017 --- [onPool-worker-9] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: could not serialize access due to concurrent update

What am I doing wrong?

Here is my repository implementation:

@Lock(LockModeType.PESSIMISTIC_WRITE)
@Override
public void addToSequence(Integer id) {
    FiveEntity fiveEntity=entityManager.find(FiveEntity.class,id);
    fiveEntity.setSequence(fiveEntity.getSequence()+1);
    log.debug("sequence: {}",fiveEntity.getSequence());
    entityManager.merge(fiveEntity);
}

And here is my service implementation:

@Transactional(isolation = Isolation.SERIALIZABLE)
@Override
public void processWithPessimisticLock() {
   fiveEntityRepository.addToSequence(lastId);
}
dubious cyborg
  • 33
  • 6
  • 1
    Related: https://stackoverflow.com/questions/7705273/what-are-the-conditions-for-encountering-a-serialization-failure-in-postgresql?noredirect=1&lq=1 – Jens Schauder Dec 07 '22 at 17:44

1 Answers1

1

That's how PostgreSQL works. SERIALIZABLE will not wait until the other transaction has finished but will throw an exception.

But as you are already using PESSIMISTIC_WRITE which will result in a SELECT ... FOR UPDATE you don't need to change the isolation level at all.

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
  • When two instances of the transactional method are executing concurrently, then without providing isolation both will read the initial data right? Because the second to read the row will read the uncommitted row – Nagulan S Jan 31 '23 at 06:39
  • no there is no uncommited read – Simon Martinelli Jan 31 '23 at 07:41
  • can I know how to handle those situations? I want the second transaction to wait for the first transaction to commit and release the lock. [ database: PostgreSQL] – Nagulan S Jan 31 '23 at 08:17
  • Yes it's described in the answer – Simon Martinelli Jan 31 '23 at 08:29
  • Got it. But its not working for me that's the problem. Both transactions start at once. T1 reads a row with Pessimistic_write lock and makes a change and starts executing other statements in the transaction. Now before T1 commits T2 reads the same row with the uncommitted value. How to restrict this from happening? – Nagulan S Jan 31 '23 at 08:36