FAQ
Hello:
I have an Oracle 10.2.0.4 database running on Windows Server 2003.
Deadlocks are being reported in the alert log. These deadlocks are
being generated by a 3rd party application. The two sessions involved
in the deadlocks are staying locked for over 24 hours. The Oracle
documentation states that Oracle will detect the deadlock and will
automatically resolve it by rolling back the transaction that detected
the deadlock, thereby allowing the first transaction to complete.

Even though my database is detecting the deadlock, it is not
automatically rolling back the blocked transaction. I'm having to do
that manually.

Has anyone experienced this behavior of the database not automatically
resolving the deadlock?

I, of course, have no access to the 3rd party app. code to see what
types of explicit locks are being called, nor can I change the code even
if I find out the faulty logic in the code.

--

*****************************************
Julio Aguilar-Chang
Los Alamos National Laboratory
jachang_at_lanl.gov
*****************************************

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Alex Fatkulin at Sep 29, 2009 at 7:22 pm
    "The two sessions involved in the deadlocks are staying locked for
    over 24 hours. "

    The sessions may stay blocked for 24h because it is not a deadlock yet.

    "The Oracle documentation states that Oracle will detect the deadlock
    and will automatically resolve it by rolling back the transaction that
    detected the deadlock..."

    I'm not sure where this came from but this is not how deadlock
    resolution works. What's getting rolled back is the statement which
    caused a deadlock, not the transaction. This called a statement-level
    rollback.
    On Tue, Sep 29, 2009 at 3:06 PM, Julio Aguilar-Chang wrote:
    Hello:
    I have an Oracle 10.2.0.4 database running on Windows Server 2003.
    Deadlocks are being reported in the alert log.  These deadlocks are being
    generated by a 3rd party application.  The two sessions involved in the
    deadlocks are staying locked for over 24 hours.  The Oracle documentation
    states that Oracle will detect the deadlock and will automatically resolve
    it by rolling back the transaction that detected the deadlock, thereby
    allowing the first transaction to complete.

    Even though my database is detecting the deadlock, it is not automatically
    rolling back the blocked transaction.  I'm having to do that manually.

    Has anyone experienced this behavior of the database not automatically
    resolving the deadlock?
    I, of course, have no access to the 3rd party app. code to see what types of
    explicit locks are being called, nor can I change the code even if I find
    out the faulty logic in the code.

    --

    *****************************************
    Julio Aguilar-Chang
    Los Alamos National Laboratory
    jachang_at_lanl.gov
    *****************************************

    --
    http://www.freelists.org/webpage/oracle-l

    --
    Alex Fatkulin,
    http://afatkulin.blogspot.com
    http://www.linkedin.com/in/alexfatkulin
    --
    http://www.freelists.org/webpage/oracle-l
  • Guillermo Alan Bort at Sep 29, 2009 at 8:03 pm
    If I recall correctly, a deadlock gives an ora-60 error in the alert log and
    kills one of the two sessions (which one has always been a mistery to me,
    the doc says it's the one that initiates the deadlock... but since deadlock
    is essentialy a circular block, this makes little sense). Now, when Oracle
    kills a session, the ENTIRE transaction is rolled back.

    hth
    Alan Bort
    Oracle Certified Professional
  • Alex Fatkulin at Sep 29, 2009 at 8:12 pm
    Oracle never kills the session as a result of ora-60. The session
    which received ora-60 can decide whether commit the work which was
    already done or repeat/modify/skip the statement.

    On Tue, Sep 29, 2009 at 4:03 PM, Guillermo Alan Bort
    wrote:
    If I recall correctly, a deadlock gives an ora-60 error in the alert log and
    kills one of the two sessions...
    hth
    Alan Bort
    Oracle Certified Professional
    --
    Alex Fatkulin,
    http://afatkulin.blogspot.com
    http://www.linkedin.com/in/alexfatkulin
    --
    http://www.freelists.org/webpage/oracle-l
  • Chet justice at Sep 30, 2009 at 1:49 am
    Miladin Modrakovic (aka Oraclue <http://oraclue.com>), has 2 pretty good
    articles on it.

    Part 1: http://oraclue.com/2009/04/20/detecting-deadlock-source/

    Part 2: http://oraclue.com/2009/04/23/detecting-deadlock-source-part-2/

    I worked with him when we began receiving those deadlocks (me in a
    junior/dev dba role). My contention was that it was due to a terrible
    design (cascading triggers and such) but, I believe, he ultimately solved
    the problem by indexing unindexed foreign keys. He did some pretty in-depth
    research on it and those articles are just a bit of what he found.

    chet
    On Tue, Sep 29, 2009 at 4:12 PM, Alex Fatkulin wrote:

    Oracle never kills the session as a result of ora-60. The session
    which received ora-60 can decide whether commit the work which was
    already done or repeat/modify/skip the statement.

    On Tue, Sep 29, 2009 at 4:03 PM, Guillermo Alan Bort
    wrote:
    If I recall correctly, a deadlock gives an ora-60 error in the alert log and
    kills one of the two sessions...
    hth
    Alan Bort
    Oracle Certified Professional


    --
    Alex Fatkulin,
    http://afatkulin.blogspot.com
    http://www.linkedin.com/in/alexfatkulin
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 29, '09 at 7:06p
activeSep 30, '09 at 1:49a
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase