What are the probable root causes for the oracle row level locks

[Hemanth Kumar Simhadri]

Hi,

We have a severe issue in our web application that the server (tomcat) goes down and the login page itself will not be accessable. The root cause is same each time. We identified that there were table row level locks happened in the oracle database. This situation is happening once in a while in our production application. We request the DB team to kill the sessions and some times they fail to do so. We restart the application by restarting all the apache and tomcat servers.

We are operating our application in cluster mode with four Apache & Tomcat servers. In our tomcat server logs we will have all the HTTP requests of every user. We observed that the HTTP requests of one server will be kept pending and the request are successfully completed in rest 3 servers. This number of pending HTTP requests will be increased gradually in all the servers one after the other and the application will be down. Users can't access the login page also.

From the Oracle DB team, we came to know that some of the sessions are aquiring locks on the rows of the oracle tables while doing a Read/Write operations and are not releasing the locks for other sessions. And due to this the HTTP requests are kept pending for longer time.

We also identified some sideeffects of this situations. When the applications doesnot respond to the users operation, they again retry. Due to that another HTTP request is getting generated for the same operation and these HTTP requests will multiply when he clicks on the same HTML buttons like "Done" "Next" "Submit" e.t.c. The previous requests for the same operation are not getting killed when user retrys.

We are not getting any ways or means to find out which HTTP request or which users session or which server has lead to the DB locks.

Can anyone help us on this issue.

We ran a script to find out which tables contains the locks and the ouput is atatched here

[Martin Vashko]

According to the documentation, the only source of a row lock is either a DML operation which modified the row (that is INSERT, UPDATE or DELETE), or a SELECT FOR UPDATE statement which explicitly locks a row.

[William P O'Sullivan]

I don't know if Oracle has this feature, but DB2 allows UNCOMMITTED READS now (with ur).

This allows applications to read technically "locked" rows that have been changed.
The data may be committed at some future point so the application should prepare for that.

This works well for read-only type operations and dramatically improves performance.

In your case, if the row is exclusively locked, is it the same user logging in again?
What is your transaction isolation etc.?

WP

[Martin Vashko]

No, Oracle does not have READ UNCOMMITTED transaction isolation level. On the other hand, in Oracle readers never block writers and writers never block readers, since Oracle is using multiversioning to ensure consistency. So only a writer can block another writer in Oracle. The only exception is the SELECT FOR UPDATE, which is theoretically a read operation, but it locks the rows it selects per the explicit request (and technically it is a write operation, since in Oracle locks are stored with data).

Edit: added the SELECT FOR UPDATE clarification.

[William P O'Sullivan]

Thanks Martin,

This leads me to believe that maybe the tables being locked contain some form of last login time data?

So if it's locked awaiting commit, and the user(s) log in again, the prior writer will block the current writer.

WP

[Martin Vashko]

Hard to guess the details actually, but it certainly means that an uncommitted transaction is hanging around somewhere there. It should be possible to nail that down with extensive logging (log where transactions start and end, and then search the log for transactions that were not ended).

Also, if it is always one table (or a limited set of tables) where the locked rows are, the code dealing with such table(s) should be inspected closely for possible bugs.