Blocking/Waiting Locks

Hello,

We have a problem with Blocking and Waiting Locks. In Blocking/Waiting Locks chart, I am seeing a session which is locking other sessions. Around 4 to 5 users’ applications are hanging until the locked session user save his/her transaction. I know how to find out the locked session user. I can call him and request to save the transaction ASAP, but until I find out the problem, other users are unsatisfied with the application as it is in hanging mode for long time and they keep calling me to tell that the application is hanging.

The application is well designed and has been running since couple of years without problems and this problem has been happening since last 3 weeks.

Is there a solution to solve this problem automatically? Cannot oracle do this automatically?

Can anyone help me how to solve this issue?

Thanks

seems like a bad application which is holding locks on tables and no other users can use them until the locks are released.

Seems to me as if Oracle is doing its job

I'm not aware of a way of sorting this out automatically at the database level (you could of course do it at the application level). I have similar problems and have a unix shell script that runs every x minutes and checks for long enqueue waits and emails me. At least that way I can resolve the situation quickly. I can post the script if you like. Of course there is a danger that you miss things depending on the frequency with which the script runs. Let me know if you want the script.

Please post me your script.

I can make the script run every 5 minutes or every 10 minutes. But no user would appreciate if the application is hanging for more than 10 minutes and the problem happens frequently. If I know that a session is blocking since 10 minutes then I can call the user and ask him to save the transaction and if I cannot reach the user I can kill the session. But this won’t seem like a perfect solution. And it never happened like this before in last 3 years time to blame the application.

I would appreciate if anyone could help me with the best solution.

Thanks

hacketta1,
Send that script to me or post it I'd love to see it.


Vimala,
Just like davey said it's more than likely bad coding on the applications part. I'd call the application support and send them the sql statements that the user is running as well as tell them what the user was doing at the time. That way they can't blame Oracle.

Attached

Thanks! Hacketta1

Is your application using Pessimistic locking or Optimistic locking?

Tamil