ORA-00060: DEADLOCK DETECTED WHILE WAITING FOR RESOURCE

QUESTIONS
What is a deadlock?
How to identify sessions involved in a deadlock?
How to eliminate a deadlock in a database?

SYMPTOMS
You observe one or more ORA-00060 error in your alert log

ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors.
More info in file /oracle/diag/rdbms/dbpilot/DBPILOT/trace/DBPILOT_ora_62735.trc.

CAUSE
A deadlock occurs when 2 sessions have blocked each other by attempting to update a row, which is already updated by another session but has not been yet committed or rolled back by that session. There can be more than 2 sessions involved, but the main idea is the same.

RELATED
A BLOCKING SESSION

TESTCASE

A user for the testcase

CREATE USER TESTCASE IDENTIFIED BY TESTCASE DEFAULT TABLESPACE USERS;
GRANT CONNECT, RESOURCE TO TESTCASE;
GRANT SELECT ON V_$MYSTAT TO TESTCASE;
GRANT SELECT ON V_$SESSION TO TESTCASE;
GRANT SELECT ON DBA_OBJECTS TO TESTCASE;
GRANT UNLIMITED TABLESPACE TO TESTCASE;

SQL> CONNECT TESTCASE/TESTCASE
Connected.

SQL> SHO USER
USER is "TESTCASE"

A table for the testcase

CREATE TABLE D_LOCK (DIGIT NUMBER, STRING VARCHAR(10));
INSERT INTO D_LOCK VALUES (1, 'ONE');
INSERT INTO D_LOCK VALUES (2, 'TWO');
COMMIT;

SQL> SELECT * FROM D_LOCK;

     DIGIT STRING
---------- ----------
         1 ONE
         2 TWO

Establish 2 new sessions by the TESTCASE user from 2 diffrent terminals

-- Set a sqlprompt to easily recognize 2 different sessions

set sqlprompt "_user'@'SES_id > "

-- To get a session ID 

SELECT DISTINCT SID FROM V$MYSTAT;

For instance

-- Session 1

SQL> CONNECT TESTCASE/TESTCASE
Connected.

SQL> set sqlprompt "_user'@'SES_1 > "

TESTCASE@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
        12

-- Session 2	
   
SQL> CONNECT TESTCASE/TESTCASE
Connected.

SQL> set sqlprompt "_user'@'SES_2 > "

TESTCASE@SES_2 > SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
         8

As next step, in each established session update different rows without ending their transactions

-- Session 1

TESTCASE@SES_1 > UPDATE D_LOCK SET STRING='001' WHERE DIGIT=1;

1 row updated.

TESTCASE@SES_1 > SELECT * FROM D_LOCK;

     DIGIT STRING
---------- ----------
         1 001
         2 TWO

-- Session 2

TESTCASE@SES_2 > UPDATE D_LOCK SET STRING='002' WHERE DIGIT=2;

1 row updated.

TESTCASE@SES_2 > SELECT * FROM D_LOCK;

     DIGIT STRING
---------- ----------
         1 ONE
         2 002

Further by the first session update the row that’s been updated by the second session

-- Session 1

TESTCASE@SES_1 > UPDATE D_LOCK SET STRING='two' WHERE DIGIT=2;

On this step the first session has hung because the updated row is locked by the second session. The second session in this case is A BLOCKING SESSION.

-- To find any blocking session in the database

SET LINES 300 
SET PAGES 999
COL BLOCKING FOR A20 
COL WAITING FOR A20
COL ON_OBJECT FOR A15
COL WAITING_SQLID FOR A15

SELECT SES1.SID || ',' || SES1.SERIAL# || '(' || SES1.USERNAME || ')' "BLOCKING",
       SES2.SID || ',' || SES2.SERIAL# || '(' || SES2.USERNAME || ')' "WAITING", 
       SES2.SECONDS_IN_WAIT,
       DO.OWNER || '.' || DO.OBJECT_NAME "ON_OBJECT",
       DBMS_ROWID.ROWID_CREATE(1, SES2.ROW_WAIT_OBJ#, SES2.ROW_WAIT_FILE#, 
       SES2.ROW_WAIT_BLOCK#, SES2.ROW_WAIT_ROW#) "LOCKED_ROWID",
       SES2.SQL_ID "WAITING_SQLID"
FROM   V$SESSION SES1, V$SESSION SES2, DBA_OBJECTS DO 
WHERE  SES1.SID=SES2.BLOCKING_SESSION AND SES2.ROW_WAIT_OBJ#=DO.OBJECT_ID AND 
       SES2.BLOCKING_SESSION IS NOT NULL
ORDER BY 1,3;

BLOCKING             WAITING              SECONDS_IN_WAIT ON_OBJECT       LOCKED_ROWID       WAITING_SQLID
-------------------- -------------------- --------------- --------------- ------------------ ---------------
8,23463(TESTCASE)    12,55976(TESTCASE)                31 TESTCASE.D_LOCK AAAUFkAAHAAAACGAAB 6c86fhw5btvby

Now, by the second session attempt to update the row which is locked by first session and see what will happen (create another blocking session)

-- Session 2

TESTCASE@SES_2 > UPDATE D_LOCK SET STRING='one' WHERE DIGIT=1;

By the second update you have created a deadlock. Oracle automatically detects deadlocks and rollbacks the statement that caused the deadlock allowing another session to proceed. An aborted statement will error with the ORA-00060 message

-- Session 1

TESTCASE@SES_1 > UPDATE D_LOCK SET STRING='two' WHERE DIGIT=2;
UPDATE D_LOCK SET STRING='two' WHERE DIGIT=2
                  *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

TESTCASE@SES_1 >  SELECT * FROM D_LOCK;

     DIGIT STRING
---------- ----------
         1 001
         2 TWO

What about the second session? The second session will proceed as if the deadlock has never existed. But the second session after database automatically resolved the deadlock turned to be A WAITING SESSION because the first session hasn’t yet committed its update.

BLOCKING             WAITING              SECONDS_IN_WAIT ON_OBJECT       LOCKED_ROWID       WAITING_SQLID
-------------------- -------------------- --------------- --------------- ------------------ ---------------
12,55976(TESTCASE)   8,23463(TESTCASE)                107 TESTCASE.D_LOCK AAAUFkAAHAAAACGAAA 2733dnjgunryh

After the first session is committed, the second session will proceed

-- Session 1

TESTCASE@SES_1 > COMMIT;

Commit complete.

-- Session 2

TESTCASE@SES_2 > UPDATE D_LOCK SET STRING='one' WHERE DIGIT=1;

1 row updated.

TESTCASE@SES_2 > SELECT * FROM D_LOCK;

     DIGIT STRING
---------- ----------
         1 one
         2 002

TESTCASE@SES_2 > COMMIT;

Commit complete.

After raising the ORA-00060 the database writes the following error in an alert log

2018-01-15T10:30:53.833282+03:00
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors.
 More info in file /oracle/diag/rdbms/dbpilot/DBPILOT/trace/DBPILOT_ora_62735.trc.

Let’s dig out more information from the trace file to know which sessions were involved and what they did.

oracle@dbpilot.net: head -40 /oracle/diag/rdbms/dbpilot/DBPILOT/trace/DBPILOT_ora_62735.trc
Trace file /oracle/diag/rdbms/dbpilot/DBPILOT/trace/DBPILOT_ora_62735.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME:    /oracle/product/12.2.0.1
System name:    Linux
Node name:      dbpilot.net
Release:        4.1.12-61.1.18.el7uek.x86_64
Version:        #2 SMP Fri Nov 4 15:48:30 PDT 2016
Machine:        x86_64
Instance name: DBPILOT
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 62735, image: oracle@dbpilot.net (TNS V1-V3)


*** 2018-01-15T10:30:53.182802+03:00
*** SESSION ID:(12.55976) 2018-01-15T10:30:53.182819+03:00
*** CLIENT ID:() 2018-01-15T10:30:53.182824+03:00
*** SERVICE NAME:(SYS$USERS) 2018-01-15T10:30:53.182829+03:00
*** MODULE NAME:(SQL*Plus) 2018-01-15T10:30:53.182834+03:00
*** ACTION NAME:() 2018-01-15T10:30:53.182838+03:00
*** CLIENT DRIVER:(SQL*PLUS) 2018-01-15T10:30:53.182842+03:00

2018-01-15 10:30:53.181*:ksq.c@12954:ksqdld_hdr_dump():
DEADLOCK DETECTED ( ORA-00060 )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors

[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 serial  process session holds waits serial
TX-0006000B-00000AF3-00000000-00000000          8      12     X        55976      80       8           X  23463
TX-000A000E-00001292-00000000-00000000         80       8     X        23463       8      12           X  55976

According to Resource Name we can see that 2 sessions (12 and 8) blocked each other. And looking into Rows waited on makes clear what statements were issued

oracle@dbpilot.net: egrep "Rows waited on" -A 25 /oracle/diag/rdbms/dbpilot/DBPILOT/trace/DBPILOT_ora_62735.trc
Rows waited on:
  Session 12: obj - rowid = 00014164 - AAAUFkAAHAAAACGAAB
  (dictionary objn - 82276, file - 7, block - 134, slot - 1)
  Session 8: obj - rowid = 00014164 - AAAUFkAAHAAAACGAAA
  (dictionary objn - 82276, file - 7, block - 134, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 8:
  sid: 8 ser: 23463 audsid: 24864 user: 115/TESTCASE
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 80 O/S info: user: oracle, term: UNKNOWN, ospid: 62738
    image: dbpilot.net (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/1, ospid: 62736
    machine: dbpilot.net program: sqlplus@dbpilot.net (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  UPDATE D_LOCK SET STRING='one' WHERE DIGIT=1

----- End of information for the OTHER waiting sessions -----

2018-01-15 10:30:53.240*:ksq.c@13181:ksqdldnl(): Information for THIS session:

----- Current SQL Statement for this session (sql_id=6c86fhw5btvby) -----
UPDATE D_LOCK SET STRING='two' WHERE DIGIT=2

The trace file has all data needed to identify sessions and dml statements that were involved in the deadlock. According to the trace file, the session 12 was waiting the session 8 on rowid AAAUFkAAHAAAACGAAB, and the session 8 was waiting the session 12 on rowid AAAUFkAAHAAAACGAAA.
 
Statements that were involved in the deadlock :

UPDATE D_LOCK SET STRING='one' WHERE DIGIT=1
UPDATE D_LOCK SET STRING='two' WHERE DIGIT=2

Knowing the exact rowid involved in the deadlock find out rows values

SELECT DBMS_ROWID.ROWID_OBJECT('AAAUFkAAHAAAACGAAB') OBJECT_ID FROM dual;

 OBJECT_ID
----------
     82276

COL OWNER FOR A10 
COL OBJECT_NAME FOR A12 	 
SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID='82276';

OWNER      OBJECT_NAME
---------- ------------
TESTCASE   D_LOCK

SQL> SELECT ROWID,T.* FROM TESTCASE.D_LOCK T WHERE ROWID in ('AAAUFkAAHAAAACGAAB','AAAUFkAAHAAAACGAAA');

ROWID                   DIGIT STRING
------------------ ---------- ----------
AAAUFkAAHAAAACGAAA          1 one
AAAUFkAAHAAAACGAAB          2 002

The testcase is completed. Drop user testcase

SQL> DROP USER TESTCASE CASCADE;

User dropped.

 
NOTE:
It’s impossible to get a list of all rows being locked in a table by a specific transaction.
Details in the following notes:
How to Get a ROWID From a Locked Row in a Table (Doc ID 1082439.6)
How To Indentify The Row Which is Locked By an Other User’s Session (Doc ID 198150.1)

 
REFERENCES
OERR: ORA-60 “deadlock detected while waiting for resource” (Doc ID 18251.1)