Print Friendly, PDF & Email

 
QUESTIONS

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

 
SYMPTOMPS

You observe one or more ORA-00060 error in an 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 block each other by attempting to update a row, which is already updated by another session but has not been yet committed or rolled back. There can be more than 2 sessions involved, but the main idea is the same.

 
REFERENCES

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

 
TESTCASE

A user for this 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 this 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 more sessions under TESTCASE user

-- Session 1	
set sqlprompt "_user'@'SES_1 > "
SELECT DISTINCT SID FROM V$MYSTAT;

-- Session 2
set sqlprompt "_user'@'SES_2 > "	
SELECT DISTINCT SID FROM V$MYSTAT;


SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> set sqlprompt "_user'@'SES_1 > "
TESTCASE@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
        12
	   
SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> set sqlprompt "_user'@'SES_2 > "
TESTCASE@SES_2 > SELECT DISTINCT SID FROM V$MYSTAT;

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

Now update by each session 1 different row without ending transactions

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

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

By the first session update the row that is updated by the second session

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

The first session will hang because the row is locked by the second session. The second session in this case is A BLOCKING SESSION.

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

In the next step, by the second session attempt to update the row which is locked by first session and see what happens (create another blocking session)

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 error

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 second session? The second session will proceed as if the deadlock has never existed. But the second session begun to be A WAITING SESSION because first session is still not committed.

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

TESTCASE@SES_1 > COMMIT;

Commit complete.

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.

During raising ORA-00060 a 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

 
To find out exact rows by row_id

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

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) and How To Indentify The Row Which is Locked By an Other User’s Session (Doc ID 198150.1)

 
 

Version  : 11:15 15.01.2018
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0