0

I have a situation where we are acquiring lock on an object from database using SELECT FOR UPDATE. This is necessary for us to insert and delete records from multiple tables in an orderly fashion. The functionality works something like this.

Login -> Acquire lock on unique lock object and insert records to multiple tables and release lock -> Logout -> Acquire lock on same unique lock object and delete records from multiple tables and release lock.

We have synchronization enabled to track users have logged in before logging him out. It is taken care in Java code. However we obtain another lock at database level to make sure the database transactions are synchronized when large number of users are logging in.

Problem: The whole system works perfectly in multi-clustered servers and singleton servers. However, when the number of concurrent users reaches 4000+, we are facing row lock contention (Mode 6) in the database. And few users are not able to login.

Objective: To fix the locking mechanism to enable users to login and logout successfully.

Things tried so far: Added NOWAIT and SKIP LOCKED to SELECT FOR UPDATE query. This doesn't solve my problem because the first one simply throws an error and the second one basically skips the lock which would affect synchronization.

Need suggestions and opinions from Database experts to resolve this issue. TIA.

UPDATE: Just adding one more information. We do not update or do anything with the locked row. It is just used as a mechanism to synchronize other database tasks we do.

NaveenBharadwaj
  • 1,212
  • 5
  • 19
  • 42

1 Answers1

0

Instead of relying on pessimistic locking(your current approach)- use optimistic locking possibly using some ORM.

Alok Sinha
  • 171
  • 1
  • 3
  • Can you provide a basic explanation? I have read about optimistic locking. It basically talks about using UPDATE instead of SELECT FOR UPDATE. Also we need to do some more steps for handling concurrency separately. Perhaps, you can provide a link to some blog post? Thanks for replying! – NaveenBharadwaj Oct 31 '17 at 09:33
  • Look at this one - https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking and also look at the one using hibernate https://docs.jboss.org/hibernate/orm/4.0/devguide/en-US/html/ch05.html – Alok Sinha Oct 31 '17 at 09:48
  • Please check UPDATE in the question. We dont lock the object to update it. We lock an object and then execute several INSERT, DELETE, UPDATE statements. Once we complete all of them, we release the lock. Optimistic locks works based on version number of the updated records. I feel its complicated and may not be possible in my case – NaveenBharadwaj Oct 31 '17 at 10:40