FAQ
Hi all,
I'm trying to solve an ora-4020 (deadlock) issue; the trace file (sorry if messed) shows:

ksqded1: deadlock detected via did
DEADLOCK DETECTED

Current SQL statement for this session:
Update set ='' where like '%'
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00040015-0000305b 13 11 X 10 14 X
TX-0007000b-0000309e 10 14 X 13 11 X
session 11: DID 0001-000D-00000001 session 14: DID 0001-000A-00000001
session 14: DID 0001-000A-00000001 session 11: DID 0001-000D-00000001

Rows waited on:
Session 14: obj - rowid = 0000147E - AAABR+AAKAAAAJIAAH
Session 11: obj - rowid = 0000147E - AAABR+AAKAAAzEeAAH

The trace file shows clearly that session 11 and 14 are blocking each other.

Note that can be very long, but it's almost certain that this is not causing the problem.

Database version is 8.0.5 on Solaris 8 sparc.

So, I'm looking at the piece of source containing the affected code (I'm not the developer neither a skilled one) and I have seen something strange, that is a sql package containing many procedures with insert/update statements and none of these insert/update was followed by a commit; I asked the developer for this matter and she said that a commit would prevent a possible rollback of database transaction.

Now, my questions are:

1. Is it correct an insert/update without a commit into a sql package? If yes, when are the inserted/updated data commited?
2. Would this the possible cause of the deadclock, as the table indexes could be locked by a large number of records inserted/updated?
3. Is this the correct way to get the choice of performing a rollback?

Thanks for you help,

Alessandro

Search Discussions

  • Sandeep Dubey at May 8, 2006 at 5:45 pm
    Alessandro,

    In a J2EE environment it is correct to let the app server control the
    transaction and stored proc to do all database DMLs. So you wont see
    any commit or rollback inside the stored proc.

    You need to get the SQL that are causing deadlock. One could be where
    you are updating a key column(s) refered by a child table. Other can
    be when you are not doing updates in the same order in two different
    stored procs. e.g. one procedure is updating parent first and then
    child table an dother one is updating child table first and then
    parent table on same set of parent and child table.

    Also make sure that you have indexes on the FKs if they are being updated.

    Sandeep
  • Bobak, Mark at May 8, 2006 at 5:46 pm
    Hi Alessandro,

    First off, this is an ORA-0060, not an ORA-4020. (ORA-60 is an enqueue
    deadlock, ORA-4020 is a library cache deadlock.)

    Second, it's a TX (transaction enqueue) deadlock.

    Third, the mode held by the blocker and the mode held by the waiter are
    'X' (exclusive).

    Fourth, the statement encountering the deadlock is an UPDATE.

    So, the combination of the above info tells me that this is a row-level
    application deadlock. You've got two concurrent sessions, one session
    updates row X and does not commit. Another session updates row Y and
    does not commit. Then, the first session tries updating row Y and
    starts waiting on the second session, and finally, the second session
    tries to update row X and starts waiting on the first session. In this
    state, the sessions would wait forever, so, Oracle detects a deadlock,
    raises ORA-0060, and statement level rollback occurs.

    I don't know anything about your application, however, the cleanest
    solution would be to examine the application logic, and alter as
    necessary to ensure that two concurrent sessions do not attempt to
    update the same set of rows. Also, another possiblility, which would
    still suffer from slowdowns due to TX enqueue waits, but not deadlocks,
    would be to ensure that the order that rows are updated is the same in
    all the concurrent sessions.

    Hope that helps,

    -Mark



    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    For a successful technology, reality must take precedence over public
    relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alessandro Vercelli
    Sent: Monday, May 08, 2006 1:34 PM
    To: Oracle Freelists.org
    Subject: [SPAM] Deadlock problem
    Importance: Low

    Hi all,
    I'm trying to solve an ora-4020 (deadlock) issue; the trace file (sorry
    if messed) shows:

    ksqded1: deadlock detected via did
    DEADLOCK DETECTED

    Current SQL statement for this session:
    Update set ='' where like '%'
    The following deadlock is not an ORACLE error. It is a deadlock due to
    user error in the design of an application or from issuing incorrect
    ad-hoc SQL. The following information may aid in determining the
    deadlock:
    Deadlock graph:

    ---------Blocker(s)--------
    ---------Waiter(s)---------
    Resource Name process session holds waits process session
    holds waits
    TX-00040015-0000305b 13 11 X 10 14
    X
    TX-0007000b-0000309e 10 14 X 13 11
    X
    session 11: DID 0001-000D-00000001 session 14: DID
    0001-000A-00000001
    session 14: DID 0001-000A-00000001 session 11: DID

    0001-000D-00000001
    Rows waited on:
    Session 14: obj - rowid = 0000147E - AAABR+AAKAAAAJIAAH Session 11: obj
    - rowid = 0000147E - AAABR+AAKAAAzEeAAH

    The trace file shows clearly that session 11 and 14 are blocking each
    other.

    Note that can be very long, but it's almost certain that this
    is not causing the problem.

    Database version is 8.0.5 on Solaris 8 sparc.

    So, I'm looking at the piece of source containing the affected code (I'm
    not the developer neither a skilled one) and I have seen something
    strange, that is a sql package containing many procedures with
    insert/update statements and none of these insert/update was followed by
    a commit; I asked the developer for this matter and she said that a
    commit would prevent a possible rollback of database transaction.

    Now, my questions are:
    1. Is it correct an insert/update without a commit into a sql package?
    If yes, when are the inserted/updated data commited?
    2. Would this the possible cause of the deadclock, as the table indexes
    could be locked by a large number of records inserted/updated?
    3. Is this the correct way to get the choice of performing a rollback?

    Thanks for you help,

    Alessandro

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at May 8, 2006 at 5:49 pm
    On 5/8/06, Alessandro Vercelli wrote:

    >
    1. Is it correct an insert/update without a commit into a sql package? If
    yes, when are the inserted/updated data commited?
    That really depends on the app.

    eg. A stored procedure is used to update the data, the user has to push a
    button to do the commit, or another button to abandon the transaction and
    rollback.

    Possible problems with that approach is that the user may start the
    transaction
    and then go to lunch, leave N row locked.

    This however does not cause a deadlock, just blocking, which is not really
    the same thing.

    An ORA-4020 occurs when 2 sessions each hold a resource that the other
    session wants to lock. Oracle breaks the tie.

    2. Would this the possible cause of the deadclock, as the table indexes
    could be locked by a large number of records inserted/updated?
    You should probably look on MetaLink for documents related to ORA-4020.
    It has been written about exhaustively, and well documented.
    Search on asktom as well.

    ORA-4020 is caused by inconsistently written SQL.
    Rather than try to explain it, it would be best if you
    just read what is already written about it.

    3. Is this the correct way to get the choice of performing a rollback?

    That really depends on the app requirements.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at May 8, 2006 at 5:52 pm
    That should teach me to check the error message before replying.

    I can never keep straight which is which.

    (ORA-60 vs. ORA-4020)

    It's an ORA-60, as Mark has stated.

    Different error number, same advice.
    On 5/8/06, Jared Still wrote:
    On 5/8/06, Alessandro Vercelli wrote:

    1. Is it correct an insert/update without a commit into a sql package?
    If yes, when are the inserted/updated data commited?

    That really depends on the app.

    eg. A stored procedure is used to update the data, the user has to push a
    button to do the commit, or another button to abandon the transaction and
    rollback.

    Possible problems with that approach is that the user may start the
    transaction
    and then go to lunch, leave N row locked.

    This however does not cause a deadlock, just blocking, which is not really
    the same thing.

    An ORA-4020 occurs when 2 sessions each hold a resource that the other
    session wants to lock. Oracle breaks the tie.

    2. Would this the possible cause of the deadclock, as the table indexes
    could be locked by a large number of records inserted/updated?

    You should probably look on MetaLink for documents related to ORA-4020.
    It has been written about exhaustively, and well documented.
    Search on asktom as well.

    ORA-4020 is caused by inconsistently written SQL.
    Rather than try to explain it, it would be best if you
    just read what is already written about it.


    3. Is this the correct way to get the choice of performing a rollback?


    That really depends on the app requirements.


    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Mercadante, Thomas F \(LABOR\) at May 8, 2006 at 5:50 pm
    Allesandro,

    There were two theories concerning where a commit statement should be
    issued from.

    The first theory I heard from Oracle when PL/SQL first came out was that
    packages should not have commit statements in them - that the
    application should issue the commit when all of the pieces of work were
    completed. It was thought that the application would better know when a
    commit should be issued.

    The other theory was to put all of the work in the PL/SQL packages and
    let it control everything and either report back success (commit) or
    failure (rollback) to the application.

    Today, either way works just fine in my view.

    As for your problem, dig a little deeper. Most deadlocks that I've seen
    are caused by foreign key constraints and missing indexes. So look at
    the tables involved and look for the table being updated being
    referenced by another table via a FK. Simply adding indexes to the
    foreign key columns solves this problem.

    And remember - this is an application problem. Somebody might have to
    fix some code!

    Good Luck.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alessandro Vercelli
    Sent: Monday, May 08, 2006 1:34 PM
    To: Oracle Freelists.org
    Subject: Deadlock problem

    Hi all,
    I'm trying to solve an ora-4020 (deadlock) issue; the trace file (sorry
    if messed) shows:

    ksqded1: deadlock detected via did
    DEADLOCK DETECTED

    Current SQL statement for this session:
    Update set ='' where like '%'
    The following deadlock is not an ORACLE error. It is a
    deadlock due to user error in the design of an application
    or from issuing incorrect ad-hoc SQL. The following
    information may aid in determining the deadlock:
    Deadlock graph:

    ---------Blocker(s)--------
    ---------Waiter(s)---------
    Resource Name process session holds waits process session
    holds waits
    TX-00040015-0000305b 13 11 X 10 14
    X
    TX-0007000b-0000309e 10 14 X 13 11
    X
    session 11: DID 0001-000D-00000001 session 14: DID
    0001-000A-00000001
    session 14: DID 0001-000A-00000001 session 11: DID

    0001-000D-00000001
    Rows waited on:
    Session 14: obj - rowid = 0000147E - AAABR+AAKAAAAJIAAH
    Session 11: obj - rowid = 0000147E - AAABR+AAKAAAzEeAAH

    The trace file shows clearly that session 11 and 14 are blocking each
    other.

    Note that can be very long, but it's almost certain that this
    is not causing the problem.

    Database version is 8.0.5 on Solaris 8 sparc.

    So, I'm looking at the piece of source containing the affected code (I'm
    not the developer neither a skilled one) and I have seen something
    strange, that is a sql package containing many procedures with
    insert/update statements and none of these insert/update was followed by
    a commit; I asked the developer for this matter and she said that a
    commit would prevent a possible rollback of database transaction.

    Now, my questions are:
    1. Is it correct an insert/update without a commit into a sql package?
    If yes, when are the inserted/updated data commited?
    2. Would this the possible cause of the deadclock, as the table indexes
    could be locked by a large number of records inserted/updated?
    3. Is this the correct way to get the choice of performing a rollback?

    Thanks for you help,

    Alessandro

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Oracle-l-bounce_at_freelists.org at May 8, 2006 at 5:52 pm
    If it were FKs that are missing indexes, waits would be on a TM enqueue,
    not a TX enqueue.

    -Mark

    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    For a successful technology, reality must take precedence over public
    relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Mercadante, Thomas F
    (LABOR)

    Sent: Monday, May 08, 2006 1:51 PM
    To: alever_at_libero.it; Oracle Freelists.org
    Subject: RE: Deadlock problem

    Allesandro,

    There were two theories concerning where a commit statement should be
    issued from.

    The first theory I heard from Oracle when PL/SQL first came out was that
    packages should not have commit statements in them - that the
    application should issue the commit when all of the pieces of work were
    completed. It was thought that the application would better know when a
    commit should be issued.

    The other theory was to put all of the work in the PL/SQL packages and
    let it control everything and either report back success (commit) or
    failure (rollback) to the application.

    Today, either way works just fine in my view.

    As for your problem, dig a little deeper. Most deadlocks that I've seen
    are caused by foreign key constraints and missing indexes. So look at
    the tables involved and look for the table being updated being
    referenced by another table via a FK. Simply adding indexes to the
    foreign key columns solves this problem.

    And remember - this is an application problem. Somebody might have to
    fix some code!

    Good Luck.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alessandro Vercelli
    Sent: Monday, May 08, 2006 1:34 PM
    To: Oracle Freelists.org
    Subject: Deadlock problem

    Hi all,
    I'm trying to solve an ora-4020 (deadlock) issue; the trace file (sorry
    if messed) shows:

    ksqded1: deadlock detected via did
    DEADLOCK DETECTED

    Current SQL statement for this session:
    Update set ='' where like '%'
    The following deadlock is not an ORACLE error. It is a deadlock due to
    user error in the design of an application or from issuing incorrect
    ad-hoc SQL. The following information may aid in determining the
    deadlock:
    Deadlock graph:

    ---------Blocker(s)--------
    ---------Waiter(s)---------
    Resource Name process session holds waits process session
    holds waits
    TX-00040015-0000305b 13 11 X 10 14
    X
    TX-0007000b-0000309e 10 14 X 13 11
    X
    session 11: DID 0001-000D-00000001 session 14: DID
    0001-000A-00000001
    session 14: DID 0001-000A-00000001 session 11: DID

    0001-000D-00000001
    Rows waited on:
    Session 14: obj - rowid = 0000147E - AAABR+AAKAAAAJIAAH Session 11: obj
    - rowid = 0000147E - AAABR+AAKAAAzEeAAH

    The trace file shows clearly that session 11 and 14 are blocking each
    other.

    Note that can be very long, but it's almost certain that this
    is not causing the problem.

    Database version is 8.0.5 on Solaris 8 sparc.

    So, I'm looking at the piece of source containing the affected code (I'm
    not the developer neither a skilled one) and I have seen something
    strange, that is a sql package containing many procedures with
    insert/update statements and none of these insert/update was followed by
    a commit; I asked the developer for this matter and she said that a
    commit would prevent a possible rollback of database transaction.

    Now, my questions are:
    1. Is it correct an insert/update without a commit into a sql package?
    If yes, when are the inserted/updated data commited?
    2. Would this the possible cause of the deadclock, as the table indexes
    could be locked by a large number of records inserted/updated?
    3. Is this the correct way to get the choice of performing a rollback?

    Thanks for you help,

    Alessandro

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

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at May 8, 2006 at 6:06 pm
    ... which also do not cause deadocks.

    On 5/8/06, oracle-l-bounce_at_freelists.org
    wrote:
    If it were FKs that are missing indexes, waits would be on a TM enqueue,
    not a TX enqueue.

    -Mark


    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    For a successful technology, reality must take precedence over public
    relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Mercadante, Thomas F
    (LABOR)
    Sent: Monday, May 08, 2006 1:51 PM
    To: alever_at_libero.it; Oracle Freelists.org
    Subject: RE: Deadlock problem

    Allesandro,

    There were two theories concerning where a commit statement should be
    issued from.

    The first theory I heard from Oracle when PL/SQL first came out was that
    packages should not have commit statements in them - that the
    application should issue the commit when all of the pieces of work were
    completed. It was thought that the application would better know when a
    commit should be issued.

    The other theory was to put all of the work in the PL/SQL packages and
    let it control everything and either report back success (commit) or
    failure (rollback) to the application.

    Today, either way works just fine in my view.

    As for your problem, dig a little deeper. Most deadlocks that I've seen
    are caused by foreign key constraints and missing indexes. So look at
    the tables involved and look for the table being updated being
    referenced by another table via a FK. Simply adding indexes to the
    foreign key columns solves this problem.

    And remember - this is an application problem. Somebody might have to
    fix some code!

    Good Luck.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alessandro Vercelli
    Sent: Monday, May 08, 2006 1:34 PM
    To: Oracle Freelists.org
    Subject: Deadlock problem

    Hi all,
    I'm trying to solve an ora-4020 (deadlock) issue; the trace file (sorry
    if messed) shows:

    ksqded1: deadlock detected via did
    DEADLOCK DETECTED
    Current SQL statement for this session:
    Update set ='' where like '%'
    The following deadlock is not an ORACLE error. It is a deadlock due to
    user error in the design of an application or from issuing incorrect
    ad-hoc SQL. The following information may aid in determining the
    deadlock:
    Deadlock graph:
    ---------Blocker(s)--------
    ---------Waiter(s)---------
    Resource Name process session holds waits process session
    holds waits
    TX-00040015-0000305b 13 11 X 10 14
    X
    TX-0007000b-0000309e 10 14 X 13 11
    X
    session 11: DID 0001-000D-00000001 session 14: DID
    0001-000A-00000001
    session 14: DID 0001-000A-00000001 session 11: DID
    0001-000D-00000001
    Rows waited on:
    Session 14: obj - rowid = 0000147E - AAABR+AAKAAAAJIAAH Session 11: obj
    - rowid = 0000147E - AAABR+AAKAAAzEeAAH

    The trace file shows clearly that session 11 and 14 are blocking each
    other.

    Note that can be very long, but it's almost certain that this
    is not causing the problem.

    Database version is 8.0.5 on Solaris 8 sparc.

    So, I'm looking at the piece of source containing the affected code (I'm
    not the developer neither a skilled one) and I have seen something
    strange, that is a sql package containing many procedures with
    insert/update statements and none of these insert/update was followed by
    a commit; I asked the developer for this matter and she said that a
    commit would prevent a possible rollback of database transaction.

    Now, my questions are:
    1. Is it correct an insert/update without a commit into a sql package?
    If yes, when are the inserted/updated data commited?
    2. Would this the possible cause of the deadclock, as the table indexes
    could be locked by a large number of records inserted/updated?
    3. Is this the correct way to get the choice of performing a rollback?


    Thanks for you help,

    Alessandro

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


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


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

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at May 8, 2006 at 6:08 pm
    Huh?


    Sure, missing indexes on FKs can cause deadlocks. Well, ok, not having
    an index on a FK column on the child table does not inherently cause a
    deadlock, but the absence of the index certainly increases the
    likelihood of an enqueue wait (and possibly a deadlock) duing concurrent
    DML.


    -Mark


    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    For a successful technology, reality must take precedence over public
    relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

    ________________________________

    From: Jared Still
    Sent: Monday, May 08, 2006 2:06 PM
    To: Bobak, Mark
    Cc: Thomas.Mercadante_at_labor.state.ny.us; alever_at_libero.it; Oracle
    Freelists.org
    Subject: Re: Deadlock problem

    ... which also do not cause deadocks.

    On 5/8/06, oracle-l-bounce_at_freelists.org < oracle-l-bounce_at_freelists.org
    wrote:
    If it were FKs that are missing indexes, waits would be on a TM
    enqueue,
    not a TX enqueue.

    -Mark

    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    For a successful technology, reality must take precedence over
    public
    relations, for Nature cannot be fooled. --Richard P. Feynman,
    1918-1988

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Mercadante,
    Thomas F
    (LABOR)
    Sent: Monday, May 08, 2006 1:51 PM
    To: alever_at_libero.it; Oracle Freelists.org
    Subject: RE: Deadlock problem

    Allesandro,

    There were two theories concerning where a commit statement
    should be
    issued from.

    The first theory I heard from Oracle when PL/SQL first came out
    was that
    packages should not have commit statements in them - that the
    application should issue the commit when all of the pieces of
    work were
    completed. It was thought that the application would better
    know when a
    commit should be issued.

    The other theory was to put all of the work in the PL/SQL
    packages and
    let it control everything and either report back success
    (commit) or
    failure (rollback) to the application.

    Today, either way works just fine in my view.

    As for your problem, dig a little deeper. Most deadlocks that
    I've seen
    are caused by foreign key constraints and missing indexes. So
    look at
    the tables involved and look for the table being updated being
    referenced by another table via a FK. Simply adding indexes to
    the
    foreign key columns solves this problem.

    And remember - this is an application problem. Somebody might
    have to
    fix some code!

    Good Luck.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alessandro
    Vercelli
    Sent: Monday, May 08, 2006 1:34 PM
    To: Oracle Freelists.org
    Subject: Deadlock problem

    Hi all,
    I'm trying to solve an ora-4020 (deadlock) issue; the trace file
    (sorry
    if messed) shows:

    ksqded1: deadlock detected via did
    DEADLOCK DETECTED
    Current SQL statement for this session:
    Update set ='' where like
    '%'
    The following deadlock is not an ORACLE error. It is a deadlock
    due to
    user error in the design of an application or from issuing
    incorrect
    ad-hoc SQL. The following information may aid in determining the
    deadlock:
    Deadlock graph:
    ---------Blocker(s)--------
    ---------Waiter(s)---------
    Resource Name process session holds waits process
    session
    holds waits
    TX-00040015-0000305b 13 11 X 10
    14
    X
    TX-0007000b-0000309e 10 14 X 13
    11
    X
    session 11: DID 0001-000D-00000001 session 14: DID
    0001-000A-00000001
    session 14: DID 0001-000A-00000001 session 11: DID
    0001-000D-00000001
    Rows waited on:
    Session 14: obj - rowid = 0000147E - AAABR+AAKAAAAJIAAH Session
    11: obj
    - rowid = 0000147E - AAABR+AAKAAAzEeAAH

    The trace file shows clearly that session 11 and 14 are blocking
    each
    other.

    Note that can be very long, but it's almost certain
    that this
    is not causing the problem.

    Database version is 8.0.5 on Solaris 8 sparc.

    So, I'm looking at the piece of source containing the affected
    code (I'm
    not the developer neither a skilled one) and I have seen
    something
    strange, that is a sql package containing many procedures with
    insert/update statements and none of these insert/update was
    followed by
    a commit; I asked the developer for this matter and she said
    that a
    commit would prevent a possible rollback of database
    transaction.

    Now, my questions are:
    1. Is it correct an insert/update without a commit into a sql
    package?
    If yes, when are the inserted/updated data commited?
    2. Would this the possible cause of the deadclock, as the table
    indexes
    could be locked by a large number of records inserted/updated?
    3. Is this the correct way to get the choice of performing a
    rollback?

    Thanks for you help,

    Alessandro

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

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

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

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Alessandro Vercelli at May 10, 2006 at 1:00 pm
    Sorry for the wrong trace file: here is the correct one:

    SESSION ID:(29.650) 2006.05.10.03.58.18.000

    A deadlock among DDL and parse locks is detected.
    This deadlock is usually due to user errors in
    the design of an application or from issuing a set
    of concurrent statements which can cause a deadlock.
    This should not be reported to Oracle Support.
    The following information may aid in finding
    the errors which cause the deadlock:
    ORA-04020: deadlock detected while trying to lock object SYSUTIL_OWNER.ADFDATASETDESCRIPTOR

    object waiting waiting blocking blocking
    handle session lock mode session lock mode
    -------- -------- -------- ---- -------- -------- ----
    9bd59b80 9c774d98 9e0422bc X 9c774d98 9e042e74 S

    DUMP OF WAITING AND BLOCKING LOCKS ----------

    WAITING LOCK -------------

    SO: 9e0422bc, type: 23, owner: 9dfbe6b8, flag: INIT/-/-/0x00
    LIBRARY OBJECT LOCK: lock=9e0422bc handle=9bd59b80 request=X
    call pin=0 session pin=0
    user=9c774d98 session=9c774d98 count=0 flags=[00] savepoint=17051
    LIBRARY OBJECT HANDLE: handle=9bd59b80
    name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR
    hash=c41c8eb9 timestamp=06-16-2003 08:48:33
    namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
    kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3
    lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0]
    pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08]
    ref=9bd59b88[9a25b544,9bd5b670]
    LIBRARY OBJECT: object=9bd22008
    type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
    DATA BLOCKS:

    data# heap pointer status pins change

    -------- -------- ------ ---- ------
    0 9ba8e9b4 9bd2214c I/P/A 0 NONE
    2 9bd22198 9a251648 I/P/A 1 NONE
    3 9a2553e8 9a254fc8 I/-/A 0 NONE
    8 9bd2209c 9bb43f78 I/-/A 0 NONE
    10 9bd220fc 9bb3ebec I/-/A 0 NONE

    BLOCKING LOCK ------------

    SO: 9e042e74, type: 23, owner: 9c7da4d0, flag: INIT/-/-/0x00
    LIBRARY OBJECT LOCK: lock=9e042e74 handle=9bd59b80 mode=S
    call pin=9e036e90 session pin=0
    user=9c774d98 session=9c774d98 count=2 flags=PNC/[04] savepoint=248
    LIBRARY OBJECT HANDLE: handle=9bd59b80
    name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR
    hash=c41c8eb9 timestamp=06-16-2003 08:48:33
    namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
    kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3
    lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0]
    pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08]
    ref=9bd59b88[9a25b544,9bd5b670]
    LIBRARY OBJECT: object=9bd22008
    type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
    DATA BLOCKS:

    data# heap pointer status pins change

    -------- -------- ------ ---- ------
    0 9ba8e9b4 9bd2214c I/P/A 0 NONE
    2 9bd22198 9a251648 I/P/A 1 NONE
    3 9a2553e8 9a254fc8 I/-/A 0 NONE
    8 9bd2209c 9bb43f78 I/-/A 0 NONE
    10 9bd220fc 9bb3ebec I/-/A 0 NONE

    This lock request was aborted.

    The indexes are periodically rebuilded.

    Thanks for your help,

    Alessandro
    Hi Alessandro,

    First off, this is an ORA-0060, not an ORA-4020. (ORA-60 is an enqueue
    deadlock, ORA-4020 is a library cache deadlock.)

    Second, it's a TX (transaction enqueue) deadlock.

    Third, the mode held by the blocker and the mode held by the waiter are
    'X' (exclusive).

    Fourth, the statement encountering the deadlock is an UPDATE.

    So, the combination of the above info tells me that this is a row-level
    application deadlock. You've got two concurrent sessions, one session
    updates row X and does not commit. Another session updates row Y and
    does not commit. Then, the first session tries updating row Y and
    starts waiting on the second session, and finally, the second session
    tries to update row X and starts waiting on the first session. In this
    state, the sessions would wait forever, so, Oracle detects a deadlock,
    raises ORA-0060, and statement level rollback occurs.

    I don't know anything about your application, however, the cleanest
    solution would be to examine the application logic, and alter as
    necessary to ensure that two concurrent sessions do not attempt to
    update the same set of rows. Also, another possiblility, which would
    still suffer from slowdowns due to TX enqueue waits, but not deadlocks,
    would be to ensure that the order that rows are updated is the same in
    all the concurrent sessions.

    Hope that helps,

    -Mark




    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    For a successful technology, reality must take precedence over public
    relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alessandro Vercelli
    Sent: Monday, May 08, 2006 1:34 PM
    To: Oracle Freelists.org
    Subject: [SPAM] Deadlock problem
    Importance: Low

    Hi all,
    I'm trying to solve an ora-4020 (deadlock) issue; the trace file (sorry
    if messed) shows:

    ksqded1: deadlock detected via did
    DEADLOCK DETECTED
    Current SQL statement for this session:
    Update set ='' where like '%'
    The following deadlock is not an ORACLE error. It is a deadlock due to
    --
    http://www.freelists.org/webpage/oracle-l
  • Paula Stankus at May 10, 2006 at 1:56 pm
    My system admin. accidentally wrote over a production database file with a backup of the production database file. Will an export to /dev/null verify the file?


    Thanks,
    Paula

    Alessandro Vercelli wrote:
    Sorry for the wrong trace file: here is the correct one:

    SESSION ID:(29.650) 2006.05.10.03.58.18.000

    A deadlock among DDL and parse locks is detected.
    This deadlock is usually due to user errors in
    the design of an application or from issuing a set
    of concurrent statements which can cause a deadlock.
    This should not be reported to Oracle Support.
    The following information may aid in finding
    the errors which cause the deadlock:
    ORA-04020: deadlock detected while trying to lock object SYSUTIL_OWNER.ADFDATASETDESCRIPTOR

    object waiting waiting blocking blocking
    handle session lock mode session lock mode
    -------- -------- -------- ---- -------- -------- ----
    9bd59b80 9c774d98 9e0422bc X 9c774d98 9e042e74 S

    DUMP OF WAITING AND BLOCKING LOCKS ----------

    WAITING LOCK -------------

    SO: 9e0422bc, type: 23, owner: 9dfbe6b8, flag: INIT/-/-/0x00
    LIBRARY OBJECT LOCK: lock=9e0422bc handle=9bd59b80 request=X
    call pin=0 session pin=0
    user=9c774d98 session=9c774d98 count=0 flags=[00] savepoint=17051
    LIBRARY OBJECT HANDLE: handle=9bd59b80
    name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR
    hash=c41c8eb9 timestamp=06-16-2003 08:48:33
    namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
    kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3
    lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0]
    pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08]
    ref=9bd59b88[9a25b544,9bd5b670]
    LIBRARY OBJECT: object=9bd22008
    type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
    DATA BLOCKS:

    data# heap pointer status pins change
    ----- -------- -------- ------ ---- ------
    0 9ba8e9b4 9bd2214c I/P/A 0 NONE
    2 9bd22198 9a251648 I/P/A 1 NONE
    3 9a2553e8 9a254fc8 I/-/A 0 NONE
    8 9bd2209c 9bb43f78 I/-/A 0 NONE
    10 9bd220fc 9bb3ebec I/-/A 0 NONE
    BLOCKING LOCK ------------

    SO: 9e042e74, type: 23, owner: 9c7da4d0, flag: INIT/-/-/0x00
    LIBRARY OBJECT LOCK: lock=9e042e74 handle=9bd59b80 mode=S
    call pin=9e036e90 session pin=0
    user=9c774d98 session=9c774d98 count=2 flags=PNC/[04] savepoint=248
    LIBRARY OBJECT HANDLE: handle=9bd59b80
    name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR
    hash=c41c8eb9 timestamp=06-16-2003 08:48:33
    namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
    kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3
    lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0]
    pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08]
    ref=9bd59b88[9a25b544,9bd5b670]
    LIBRARY OBJECT: object=9bd22008
    type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
    DATA BLOCKS:

    data# heap pointer status pins change
    ----- -------- -------- ------ ---- ------
    0 9ba8e9b4 9bd2214c I/P/A 0 NONE
    2 9bd22198 9a251648 I/P/A 1 NONE
    3 9a2553e8 9a254fc8 I/-/A 0 NONE
    8 9bd2209c 9bb43f78 I/-/A 0 NONE
    10 9bd220fc 9bb3ebec I/-/A 0 NONE

    This lock request was aborted.

    The indexes are periodically rebuilded.

    Thanks for your help,

    Alessandro
    Hi Alessandro,

    First off, this is an ORA-0060, not an ORA-4020. (ORA-60 is an enqueue
    deadlock, ORA-4020 is a library cache deadlock.)

    Second, it's a TX (transaction enqueue) deadlock.

    Third, the mode held by the blocker and the mode held by the waiter are
    'X' (exclusive).

    Fourth, the statement encountering the deadlock is an UPDATE.

    So, the combination of the above info tells me that this is a row-level
    application deadlock. You've got two concurrent sessions, one session
    updates row X and does not commit. Another session updates row Y and
    does not commit. Then, the first session tries updating row Y and
    starts waiting on the second session, and finally, the second session
    tries to update row X and starts waiting on the first session. In this
    state, the sessions would wait forever, so, Oracle detects a deadlock,
    raises ORA-0060, and statement level rollback occurs.

    I don't know anything about your application, however, the cleanest
    solution would be to examine the application logic, and alter as
    necessary to ensure that two concurrent sessions do not attempt to
    update the same set of rows. Also, another possiblility, which would
    still suffer from slowdowns due to TX enqueue waits, but not deadlocks,
    would be to ensure that the order that rows are updated is the same in
    all the concurrent sessions.

    Hope that helps,

    -Mark




    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    For a successful technology, reality must take precedence over public
    relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Alessandro Vercelli
    Sent: Monday, May 08, 2006 1:34 PM
    To: Oracle Freelists.org
    Subject: [SPAM] Deadlock problem
    Importance: Low

    Hi all,
    I'm trying to solve an ora-4020 (deadlock) issue; the trace file (sorry
    if messed) shows:

    ksqded1: deadlock detected via did
    DEADLOCK DETECTED
    Current SQL statement for this session:
    Update set ='' where like '%'
    The following deadlock is not an ORACLE error. It is a deadlock due to
    --
    http://www.freelists.org/webpage/oracle-l

    ---------------------------------
    Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1&cent;/min.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 8, '06 at 5:33p
activeMay 10, '06 at 1:56p
posts11
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase