Row lock contention issue with large transactions

lockingoracle

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.

Best Answer

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.

Related Solutions

Tracing, debugging and fixing Row Lock Contentions

Would it be possible to find out which line of a stored procedure is causing these row lock contentions?

Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

What would be the general guideline to reduce/avoid/eliminate such problems with coding?

The Oracle Concepts Guide section on locks says, "A row is locked only when modified by a writer." Another session updating the same row will then wait for the first session to COMMIT or ROLLBACK before it can continue. To eliminate the problem you could serialize the users, but here are some things that can reduce the problem perhaps to the level of it not being an issue.

COMMIT more frequently. Every COMMIT releases locks, so if you can do the updates in batches the likelihood of another session needing the same row is reduced.
Make sure you aren't updating any rows without changing their values. For example, UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1); should be rewritten as the more selective (read fewer locks) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;. Of course if the changing the statement will still lock the majority of rows in the table then the change will only have a readability benefit.
Make sure you are using sequences rather than locking a table to add one to the highest current value.
Make sure you aren’t using a function that is causing an index to not be used. If the function is necessary consider making it a function based index.
Think in sets. Consider whether a loop running a block of PL/SQL doing updates could be rewritten as a single update statement. If not then perhaps bulk processing could be used with BULK COLLECT ... FORALL.
Reduce the work that gets done between the first UPDATE and the COMMIT. For example, if the code sends an email after each update, consider queuing the emails and sending them after committing the updates.
Design the application to handle waiting by doing a SELECT ... FOR UPDATE NOWAIT or WAIT 2. You can then catch the inability to lock the row and inform the user that another session is modifying the same data.

Sql-server – All-or-none exclusive lock on 2 SQL tables

No, locks are acquired one-after-the-other because the number of locks is generally unpredictable. It might change with row count. This could work differently, but it doesn't.

Your locking technique basically doesn't work. The best workaround I can think of is to write a retry-loop which tries to acquire both locks and retries on deadlock.

Best Answer

Related Solutions

Tracing, debugging and fixing Row Lock Contentions

Sql-server – All-or-none exclusive lock on 2 SQL tables

Related Question