ORA-00060: deadlock detected while waiting for resource

I got ” ORA-00060: deadlock detected while waiting for resource ”  error in Oracle database.

ORA-00060: deadlock detected while waiting for resource

Details of error are as follows.

ORA-00060: deadlock detected while waiting for resource

Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary. 

deadlock detected while waiting for resource

This ORA-00060 error is related with the Transactions deadlocked one another while waiting for resources

Look at the trace file to see the transactions and resources involved. Retry if necessary.

What is a Deadlock?

A deadlock occurs when a session (A) wants a resource held by another session (B) , but that session also wants a resource held by the first session (A). There can be more than 2 sessions involved but the idea is the same.

Example of Deadlock

The following example demonstrates a deadlock scenario.

Setup

create table eg_60 ( num number, txt varchar2(10) );
insert into eg_60 values ( 1, 'First' );
insert into eg_60 values ( 2, 'Second' );

commit;
select rowid, num, txt from eg_60;

ROWID                     NUM TXT
------------------ ---------- ----------
AAASuCAAEAAAAinAAA          1 First
AAASuCAAEAAAAinAAB          2 Second

Session #1:

update eg_60 set txt='ses1' where num=1;

Session #2:

update eg_60 set txt='ses2' where num=2;
update eg_60 set txt='ses2' where num=1;

Session #2 is now waiting for the TX lock held by Session #1

Session #1:

update eg_60 set txt='ses1' where num=2;

Session #1 is now waiting  on the TX lock for this row.

The lock is held by Session #2.

However Session #2  is already waiting on Session #1

This causes a deadlock scenario so deadlock detection kicks in and one of the sessions signals an ORA-60.

Session #2:

Session #1 is still blocked until Session #2 commits or rolls back as ORA-60  only rolls back the current statement and not the entire transaction.

Diagnostic information produced by an ORA-60

ORA-60 error normally writes the error message in the alert.log together with the name of the trace file created. The exact format of this varies between Oracle releases. The trace file will be written to the directory indicated by the USER_DUMP_DEST or BACKGROUND_DUMP_DEST, depending on the type of process that creates the trace file.

The trace file will contain a deadlock graph and additional information similar to that shown below. This is the trace output from the above example which signaled an ORA-60 to Session #2:

 
DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock]
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-00050018-000004fa 22 132 X 19 191 X
TX-00070008-00000461 19 191 X 22 132 X

session 132: DID 0001-0016-00000005 session 191: DID 0001-0013-0000000C
session 191: DID 0001-0013-0000000C session 132: DID 0001-0016-00000005
Rows waited on:
Session 132: obj - rowid = 00012B82 - AAASuCAAEAAAAinAAA
(dictionary objn - 76674, file - 4, block - 2215, slot - 0)
Session 191: obj - rowid = 00012B82 - AAASuCAAEAAAAinAAB
(dictionary objn - 76674, file - 4, block - 2215, slot - 1)
----- Information for the OTHER waiting sessions -----
Session 191:
sid: 191 ser: 5 audsid: 340002 user: 88/USER1 flags: 0x45
pid: 19 O/S info: user: USER1, term: UNKNOWN, ospid: 3163
image: oracle@<NAME>.xx (TNS V1-V3)
client details:
O/S info: user: USER1, term: pts/3, ospid: 3097
machine: <Name>.xx program: sqlplus@<Name>.xx (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update eg_60 set txt='ses1' where num=2
Information for THIS session:
----- Current SQL Statement for this session (sql_id=13b96yk6y5zny) -----
update eg_60 set txt='ses2' where num=1
===================================================
PROCESS STATE
-------------
.....

What does the trace information mean ?

You can use the following document to help diagnose common causes of deadlocks:

The following article provides information on how to detect and identify different deadlock types:

Section 1: Deadlock Graph

  
Deadlock graph:

                      ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00050018-000004fa        22     132     X             19     191           X
TX-00070008-00000461        19     191     X             22     132           X

session 132: DID 0001-0016-00000005     session 191: DID 0001-0013-0000000C
session 191: DID 0001-0013-0000000C     session 132: DID 0001-0016-00000005

This shows which process was holding each lock, and which process was waiting for each lock.
For each resource there are 2 parts each giving information on the relevant process:

• Blocker(s)
• Waiters(s)

The columns in the graph indicate:

• Resource Name: Lock name being held / waited for.
  Resource Name consists of  3 parts: Lock Type-ID1-ID2 where the information contained in id1 and id2 are different depending on the lock type
  Using the example above : TX-00050018-000004fa:
  Lock Type: TX
  ID1 (00050018) and ID2 (000004fa) point to the rollback segment and transaction table entries for that transaction.
• process              V$PROCESS.PID of the Blocking / Waiting session
• session               V$SESSION.SID of the Blocking / Waiting session
• holds                  Mode the lock is held in
• waits                  Mode the lock is requested in (waiting for)

So in this example:
SID 132 (Process 22) is holding TX-00050018-000004fa in eXclusive mode and is requesting TX-00070008-00000461 in eXclusive mode.

SID 191 (Process 19) is holding TX-00070008-00000461  in eXclusive mode and is requesting  TX-00050018-000004fa in eXclusive 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.

Section 2: Rows waited on

Rows waited on: 
Session 132: obj - rowid = 00012B82 - AAASuCAAEAAAAinAAA (dictionary objn - 76674, file - 4, block - 2215, slot - 0) 
Session 191: obj - rowid = 00012B82 - AAASuCAAEAAAAinAAB (dictionary objn - 76674, file - 4, block - 2215, slot - 1)

If the deadlock is due to row-level locks being obtained in different orders then this section of the trace file indicates the exact rows that each session is waiting to lock for themselves. Ie: If the lock requests are TX mode X waits then the ‘Rows waited on’ may show useful information.
For any other lock type / mode the ‘Rows waited on’ is not relevant and usually shows as “no row”.

In the above example:

SID 132 was waiting for ROWID ‘AAASuCAAEAAAAinAAA’ of object 76674
SID 191 was waiting for ROWID ‘AAASuCAAEAAAAinAAB’ of object 76674

This can be decoded to show the exact row/s.
Eg: SID 132 can be shown to be waiting thus:

SELECT owner, object_name, object_type FROM dba_objects WHERE object_id = 76674;

OWNER      OBJECT_NAM OBJECT_TYP
---------- ---------- ----------
USER1       EG_60      TABLE

SELECT * FROM user1.eg_60 WHERE ROWID='AAASuCAAEAAAAinAAA';

      NUM TXT
---------- ----------
        1 ses1

Section 3: Information on OTHER waiting session(s)

----- Information for the OTHER waiting sessions ----- 
Session 191: sid: 191 ser: 5 audsid: 340002 user: 88/USER1 flags: 0x45 
pid: 19 O/S info: user: USER1, term: UNKNOWN, ospid: 3163 
image: oracle@xxxxx.xx (TNS V1-V3) 
client details: 
O/S info: user: USER1, term: pts/3, ospid: 3097 
machine: xxxxx.xx program: sqlplus@xxxxx.xx (TNS V1-V3) 
application name: SQL*Plus, hash value=3669949024 
current SQL: 
update eg_60 set txt='ses1' where num=2

This section displays information regarding the other sessions (apart from the session that produced the ORA-60 deadlock trace) that are involved in the deadlock. The information includes:

• session details
• client details
• Current SQL
  In this case: update eg_60 set txt=’ses1′ where num=2

Section 4: Information for this session

Information for THIS session: 
----- Current SQL Statement for this session (sql_id=13b96yk6y5zny) ----- 
update eg_60 set txt='ses2' where num=1 
=================================================== 
PROCESS STATE 
------------- 
.....

Displays the current sql for the session that creates the ORA-60 trace as well as a complete PROCESS STATE for the session.

Avoiding Deadlock

The above deadlock example occurs because 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.
Eg: If the above statements had been forced to update rows in ascending ‘num’ order then:

Session #1:          update eg_60 set txt='ses1' where num=1;

Session #2:          update eg_60 set txt='ses2' where num=1;

Session #1:          update eg_60 set txt='ses1' where num=2;

Session #2:          update eg_60 set txt='ses2' where num=2;
commit;

The strict ordering of the updates ensures that a deadly embrace cannot occur. This is the simplest deadlock scenario to identify and resolve. Note that the deadlock need not be between rows of the same table – it could be between rows in different tables. Hence it is important to place  rules on the order in which tables are updated as well as the order of the rows within each table.

Other deadlock scenarios are discussed below.

Different Lock Types and Modes

The most common lock types seen in deadlock graphs are TX and TM locks. These may appear held / requested in a number of modes. It is the lock type and modes which help determine what situation has caused the deadlock.

Although other deadlock scenarios can happen the above are the most common.
The following article provides common cause information for the various types of deadlocks most frequently encountered based upon deadlock graphs found in trace:

TM locks – which object ?

ID1 of a TM lock indicates which object is being locked. This makes it very simple to isolate the object involved in a deadlock when a TM lock is involved.

The TM lock id is in the form TM-00012B85-00000000  where 00012B85 is the object number in hexadecimal format.

1. Convert 00012B85 from hexadecimal to a decimal number
   Hexadecimal 00012B85 is  Decimal 76677
2. Locate the object using DBA_OBJECTS

   SELECT owner,object_name,object_type 
   FROM dba_objects 
   WHERE object_id= 76677; 
   
   OWNER      OBJECT_NAM OBJECT_TYP 
   ---------- ---------- ---------- 
   USER1      EMP        TABLE
   

This is the object id that the TM lock covers.
Note that with TM locks it is possible that the lock is already held in  some mode in which case the REQUEST is to escalate the lock mode.

How to obtain Additional Information

If you are still having problems identifying the cause of a deadlock Oracle Support may be able to help. Additional information can be collected by adding the following to the init.ora parameters:

or by setting events using alter system in which case the event will be set for the life of the Oracle instance and only for new sessions:

When this is set any session encountering an ORA-60 error will write information about all processes on the database at the time of the error.This may help show the cause of the deadlock as it can show information about both users involved in the deadlock. Oracle Support will need all the information you have collected in addition to the new trace file to help identify where in the application you should look for problems.

It may be necessary to run the offending jobs with SQL_TRACE  or 10046 event enabled to show the order in which each session issues its commands in order to get into a deadlock scenario.

Do you want to learn Oracle Database for Beginners, then read the following articles.