Blocking/Waiting Locks
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Blocking/Waiting Locks

  1. #1
    Join Date
    Aug 2000
    Posts
    32

    Question 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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  3. #3
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    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.

  4. #4
    Join Date
    Aug 2000
    Posts
    32

    Thumbs down

    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

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Attached
    Attached Files Attached Files

  7. #7
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Thanks! Hacketta1
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Is your application using Pessimistic locking or Optimistic locking?

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width