deadlocks
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: deadlocks

  1. #1
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857

    deadlocks

    hello guys,
    I have this one prod DB, that have been experiencing deadlocks for quite a while.
    I already made a proposition to use an explicit locking of tables like:
    LOCK TABLE tab_name IN ROW SHARE MODE;

    I know this can be very helpful, but it seems to me that developers don't want to touch the
    codes(politics plays).
    Is there INIT parameters that I can set, to overcome this locking problem without touching the codes?
    I always have to monitor locks before an ora-02049 error will occur.
    I read params like enqueue_resources, dml_locks, row_locking, initrans.
    but I'm not sure if this can be much of a help or otherwise.

    any input guys?

    thanks in advance.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    is that 8.x version?.. and do you have too many parent child relationship? and no index on FK cols?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    You dont have to do that in Oracle..or in many other databases of todays generation for that matter.The deadlock will produce a trace file just dig into that tarce file for analysis.

    regards
    Hrishy

  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    abhaysk,
    yes this is 8.1.7 version. And it has only few parent-child rel. It has index on FKs too. The problem with regards to the application is
    that the table is so huge that it takes time before a user can complete one record. And before it insert/update data it acquire a row exclusive mode. So the select.. statement doesn't even have a FOR UPDATE included when updating at least.

    hrisky,
    actually, I did trace this from trace file. And it shows that it is an application error. I am aware that ORACLE RDBMS has a LOCK management itself, but it can be greatly(poorly) influenced by poor programming. And since the DEVELOPER TEAM doesn't want to take word from their DBA, I can only try to find another way to overcome such scenario. And that is why I did come up with this INQUIRY.

    thanks..

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Is is always advisable to let Oracle hadle the locks itself.
    Is it distributed environment? If yes, try this parameter _distributed_lock_timeout.

    also check out following doc from metalink.

    http://metalink.oracle.com/metalink/...id=1018919.102
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Originally posted by SANJAY_G
    Is is always advisable to let Oracle hadle the locks itself.
    Absolutely.

    Deadlocks = bad system design and poor coders who developed it and allowed it to progress. A deadlock isn't an 'error' as such. its Oracle - quite sensibly - telling you that something is wrong with the system design.

    Get the developers to change the code and the design otherwise you're setting yourself up for big trouble. Let Oracle handle the locks - thats what you pay big bucks to Larry Ellison for.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think i read advice in an Oracle doc that developers should follow a pattern of always locking master reccords before child, or vice versa (it doesn't matter as long as it's consistent everywhere), to reduce the probability of deadlocking. Seems like good advice.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    thanks for the input guys, but the fact remains that this system still experiencing ora-02049 and ora-00060 at least 15 times a day.
    There must be a problem somewhere else. I did suggest to use a LOCK using ROW SHARE mode, because most of those sessions that involve in locks is either have a status of EXCLUSIVE or ROW EXCLUSIVE locks. I'm not sure yet of what the actual codes of those program. But for all those sessions that has a ROW SHARE lock status doesn't have a deadlock problem in other database.

    Anyway, thanks all. I have to further investigate this case. My only nemesis is that, we are in separate company maintaining their infrastructure(network and DB,email,etc.), so any proposition that involves changes to their system is an issue.

  9. #9
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    In the attached file,

    You will observe that a bunch of table have a row exclusive lock in a single session. And another session is currently waiting for the lock to be release.

    Usually, I can only observe few tables being locked by a single session, and more often it is a ROW SHARE lock at row level.

    And one thing, I can remember the default dml locking that oracle implicitly assign when a user perform a DML is a SHARE LOCK at the table level, and a ROW EXCLUSIVE LOCK in row level. But why in this case if you can observe at the buttom in the same session the acquired lock for table is EXCLUSIVE.

    That is why when a session issues a command SELECT .... FOR UPDATE statement, the row level locking changes from ROW EXCLUSIVE to ROW SHARE.

    Any interpretation, or just idea of what could cause the problem?

    thanks.
    Attached Images Attached Images

  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by slimdave
    I think i read advice in an Oracle doc that developers should follow a pattern of always locking master reccords before child, or vice versa (it doesn't matter as long as it's consistent everywhere), to reduce the probability of deadlocking. Seems like good advice.
    While I'll agree with HRISKY, SANJAY_G, and JMAC about letting the ORACLE handle the LOCK management, I can only bear with this, only if the design of the application is good or better. Otherwise if it is bad, like what I'm in right now and the developers doesn't have any intension of re-designing the structure or re-program(major changes).
    I must find a way if there is, to alleviate the situation.

    Slimdave,
    Thanks, I think you're leading me to what I believe I need.

    abhaysk,
    I did try to dig in, and I was wrong, there was a lot of dependencies with regards to tables involved.

    SANJAY_G,
    Is is always advisable to let Oracle hadle the locks itself.
    Is it distributed environment? If yes, try this parameter _distributed_lock_timeout.
    even if I can set _distributed_lock_timeout, it will only prolong the process of the waiters because the lockers is still yet to commit/rollback.

    In my part it leaves me no choice but to use the LOCK explicitly. Let the developer insert the LOCK command before DML.
    I think that when a user save/rollback its process, the LOCK that was set to a table will be released automatically, therefore no need to worry about LOCK mismanagement.

    I hope that some of the DBAs here have the same situation with mine, and can share ideas of how they overcome such situation.

    many thanks,
    rey

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