Troubleshoot Fix Resolve ORA-00060: deadlock detected while waiting for resource

This error is due to the resource contention between two sessions. It indicates that a dead lock happened due to the resource contention with other session. Oracles itself does a rollback on your current session and resolve the error. Other session can proceed as usual. The alert.log will record the error with all details. Current session will be rolled back and need to resubmitted once the required resources are available.
Deadlock detected while waiting for resource. In most of the cases the deadlock are caused by application errors.
Dead lock
Dead lock happens when a session (sess1) wants resource locked by another session (sess2), But that session also wants the resource which is locked by sess1. These can be happened between more than two sessions also with same kind of scenario.
Dead Lock Example
create table temp ( num number,  txt varchar2(10) );
                insert into temp values ( 1, 'First' );
                insert into temp values ( 2, 'Second' );
                select rowid, num, txt from temp;

                                ROWID                     NUM TXT
                                ------------------ ---------- ----------
                                AAAAv2AAEAAAAqKABC          1 First
                                AAAAv2AAEAAAAqKABD          2 Second

    Sess1:               update temp set txt='ses1' where num=1;

    Sess2:               update temp set txt='ses2' where num=2;
                                update temp set txt='ses2' where num=1;
                                > Sess2 is now waiting for the TX lock held by Sess1

    Sess1:               update temp set txt='sess1' where num=2;

This update would cause Sess1 to wait on the TX lock held by Sess2, but Sess2 is already waiting on this session. This causes a deadlock scenario so one of the sessions signals an ORA-60.

    Sess2:               ORA-60 error

Sess1:               Still blocked until Sess2 completes the transaction (either commits or rolls back) as ORA-60 only rolls back the current statement and not the entire transaction.

How to check the error details
Ora-00060 error normally records the details in alert.log and also in the trace file. The trace file will be created in USER_DUMP_DEST and sometimes in background_dump_dest. Trace file will contain a deadlock graph and additional information.
   Current SQL statement for this session:
                    update temp set txt='ses2' where num=1

   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-00020012-0000025e     12      11     X             11      10           X
   TX-00050013-0000003b     11      10     X             12      11           X
   session 11: DID 0001-000C-00000001      session 10: DID 0001-000B-00000001
   session 10: DID 0001-000B-00000001      session 11: DID 0001-000C-00000001
   Rows waited on:
   Session 10: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKABC
   Session 11: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKABD

  So in this example:

                SID 11    holds TX-00020012-0000025e in X mode
                    and wants TX-00050013-0000003b in X mode

                SID 10  holds TX-00050013-0000003b in X mode
                    and wants TX-00020012-0000025e in X mode

The important things to note here are the LOCK TYPE, the MODE HELD and the MODE REQUESTED for each resource as these give a clue as to the reason for the deadlock.
   Rows waited on:
   Session 10: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKABC
   Session 11: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKABD
In the above example:

                SID 10  was waiting for ROWID 'AAAAv2AAEAAAAqKAAB' of object 0xBF6
                                (which is 3062 in decimal)
                SID 11  was waiting for ROWID 'AAAAv2AAEAAAAqKAAA' of object 0xBF6

How to avoid DeadLock
In the above example the error occurs because of the application which issues the update statements has no strict ordering of the rows it updates. Applications can avoid row-level lock deadlocks by enforcing some ordering of row updates. This is purely an application design issue. Strict ordering of the updates avoids the deadlock situation. Dead lock need not be between the rows between the same tables.
In case the deadlock cannot be identified after checking the application thoroughly, you can enable tracing with event="60 trace name errorstack level 3;name systemstate level 266"
DBA Tips Data Pump Reference


Post a Comment


dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google