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.

1

This doesn't solve my problem because the first one simply throws an error

That is part of the solution. You can catch the error/exception then display the appropriate message to the client ("you are already logged in", "connection limit exceeded", etc.).

This is not something we can solve in the database.

I have seen a similar extreme case, where transactions were blocked with the event enq: TX - allocate ITL entry, that is something that can be managed in the database.

But I have yet to see a case where enq: TX - row lock contention is a database problem.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.