Optimistic lock in Postgres makes another Concurrent Update to Wait

Question

I am using Postgres in my spring boot application , I am selecting a record from table and the same record can also be modified by another process.

I was using PESSIMISTIC lock as below,

public interface EntityRepository extends JpaRepository<Entity, String> {
  @Lock(LockModeType.PESSIMISTIC_WRITE)
  findByIDwithLock()
}

But due it's locking performance impact I am trying to move to OPTIMISTIC lock. I have introduced a Long field with @Version in Entity class and it works fine in the below scenario,

If multiple threads in the same process updates the same record - the versions are incremented and values getting updated as expected

From OPTIMISTIC lock's articles, it does not put a lock on the record. But when I try to update the same record from another spring boot application or even manually running a update query the update is waiting for the other process to commit the updates. If it is not locking why the update from other process to same record has to wait?

You refer to "articles", but don't identify them. – jjanes Dec 09 '20 at 15:41 — jjanes, Dec 09 '20 at 15:41

score 0 · Answer 1 · answered Dec 09 '20 at 15:47

0

The database locks rows when they are updated until the update commits. Spring/boot/hibernate/whatever does not get to override this. If everyone is using optimistic locking, then this period of lock holding should be very brief (generally too brief for a human to notice). In your case, it appears that not everyone is using optimistic locking.

answered Dec 09 '20 at 15:47

jjanes

37,812
5
27
34

To simulate optimistic lock from different processes(instances of same application) I made a sleep after select and tried updating the record manually in DB. This manual update was waiting till the update in application commited. So it means the record is locked.. – Sel_va Dec 10 '20 at 04:30
Yes. But why did "waiting till the update in application commited" take any noticable amount of time? If your application goes out for lunch after updating but before committing, then it is doing it wrong. – jjanes Dec 10 '20 at 14:09
means i have put a sleep in between(findByID() --> Thread.Sleep(30000) --> Update() ) and till it updates (during the sleep time) I couldn't update the record from other application or even from psql command line – Sel_va Dec 10 '20 at 18:44
Right, the sleep between the select and the update should not cause blocking under optimistic locking. I would set log_statement to all, make sure timestamps and pids are included in log messages, and then inspect the database log file to see what is actually going on. Or, look in pg_locks view to see what lock is being blocked on. – jjanes Dec 11 '20 at 16:09

Optimistic lock in Postgres makes another Concurrent Update to Wait

1 Answers1