What is a deadlock? How to identify sessions associated with a deadlock?
QUESTIONS

What is a deadlock?
How to identify sessions associated with a deadlock?

RELATED

HOW TO IDENTIFY BLOCKED AND BLOCKING SESSIONS IN ORACLE DATABASE?

SYMPTOMS

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

CAUSE

A deadlock occurs when two or more sessions are blocked, each waiting on a row held by the other session.
Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock.

Deadlock Detected

DEMO

By this demo, a deadlock is to be created by two sessions.

Create a user for the demo

-- Create user and provide necessary grants

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

Connect as the DEMO user and create the following table

-- Connect as the DEMO user 

SQL> CONNECT DEMO/DEMO
Connected.
SQL> SHO USER
USER is "DEMO"


-- Create a table for the demo

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

-- List content of the table 

SQL> SELECT * FROM D_LOCK;

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

Establish 2 new sessions by the DEMO user from 2 different terminals

NOTE

If you are using the SQL PLUS you can set the SQL PROMPT for each created session so you can easily distinguish between them while performing the demo.

-- To set a SQL Prompt 

set sqlprompt "_user'@'SES_id > "

-- To get a session ID 

SELECT DISTINCT SID FROM V$MYSTAT;

For instance

---------------
-- Session 1
---------------

SQL> CONNECT DEMO/DEMO
Connected.

-- Set the SQL Prompt 

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

-- Get a session SID

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

       SID
----------
        17
---------------
-- Session 2
---------------

SQL> CONNECT DEMO/DEMO
Connected.

-- Set the SQL Prompt 

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

-- Get a session SID

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

       SID
----------
        269

As the next step, update one different row of the table in each session.

-- Session 1

DEMO@SES_1 >  SELECT * FROM D_LOCK;

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

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

1 row updated.

DEMO@SES_1 >  SELECT * FROM D_LOCK;

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

-- As there was no COMMIT in the first session, 
-- the second session sees original data.

DEMO@SES_2 > SELECT * FROM D_LOCK;

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

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

1 row updated.

DEMO@SES_2 >  SELECT * FROM D_LOCK;

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

Go further and update in the first session the row that is being held by the second session

-- First session 

DEMO@SES_1 > SELECT * FROM D_LOCK;

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

-- As there was no COMMIT in the second session, 
-- the UPDATE statement freezes 

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

At this point, you created a situation when one session with SID 269 is blocking another session with SID 17. The UPDATE statement freezes as the first session can't acquire a lock on the row because the second session is already holding it. See HOW TO IDENTIFY BLOCKED AND BLOCKING SESSIONS IN ORACLE DATABASE?

SET PAGES 100
SET LINES 200 
COL BLOCKING FOR A20 
COL BLOCKED FOR A20
COL ON_OBJECT FOR A40
COL ROWID_FOR_LOCKED_ROW FOR A20
COL LOCKED_SQLID FOR A15

SELECT * FROM DBT_BLOCKERS;
BLOCKING             BLOCKED              SECONDS_IN_WAIT MINUTES_IN_WAIT ON_OBJECT       ROWID_FOR_LOCKED_ROW LOCKED_SQLID
-------------------- -------------------- --------------- --------------- --------------- -------------------- ---------------
269,44506(DEMO)      17,38105(DEMO)                  1332              22 DEMO.D_LOCK     AAASfxAAHAAAAFsAAB   6c86fhw5btvby

By the following step, in the second session, update the row which is being locked by the first session and see what will happen (create another blocking session).

-- The seccond session 

DEMO@SES_2 > SELECT * FROM D_LOCK;

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

-- This update creates a deadlock. After the Oracle automatically resolves the deadlock, 
-- this session freezes.

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

By the last update in the second session, you have created the DEADLOCK. Oracle automatically detects deadlocks and rollbacks the statement that caused the deadlock allowing another transactions to continue. An aborted statement will error with the ORA-00060 message

-- The Oracle automatically detected the deadlock and 
-- rollbacked the last update statement in the first session

DEMO@SES_1 > SELECT * FROM D_LOCK;

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

DEMO@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

DEMO@SES_1 > SELECT * FROM D_LOCK;

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

What is about the second session? As no deadlock exists anymore, the second session continues its work. The second session became the WAITING SESSION. See HOW TO IDENTIFY BLOCKED AND BLOCKING SESSIONS IN ORACLE DATABASE?

SET PAGES 100
SET LINES 200 
COL BLOCKING FOR A20 
COL BLOCKED FOR A20
COL ON_OBJECT FOR A40
COL ROWID_FOR_LOCKED_ROW FOR A20
COL LOCKED_SQLID FOR A15

SELECT * FROM DBT_BLOCKERS;
BLOCKING             BLOCKED              SECONDS_IN_WAIT MINUTES_IN_WAIT ON_OBJECT       ROWID_FOR_LOCKED_ROW LOCKED_SQLID
-------------------- -------------------- --------------- --------------- --------------- -------------------- ---------------
17,38105(DEMO)       269,44506(DEMO)                 1465              24 DEMO.D_LOCK     AAASfxAAHAAAAFsAAA   2733dnjgunryh

After the first session commits its work the second session will continue

-- The first session 

DEMO@SES_1 > SELECT * FROM D_LOCK;

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

DEMO@SES_1 > COMMIT;

Commit complete.
-- The second session

DEMO@SES_2 > SELECT * FROM D_LOCK;

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

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

1 row updated.

DEMO@SES_2 > COMMIT;

Commit complete.

In addition to the deadlock error reported to the session, a message is placed in the alert log.

2021-11-05T17:43:05.360694+03:00
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. 
More info in file /u01/app/oracle/diag/rdbms/tst19c/tst19c/trace/tst19c_ora_3379.trc.

The error message points to a trace file containing details of the sessions and their SQL statements associated with the deadlock.

Trace file /u01/app/oracle/diag/rdbms/tst19c/tst19c/trace/tst19c_ora_3379.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Build label:    RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
ORACLE_HOME:    /u01/app/oracle/product/19c
System name:    Linux
Node name:      19c.vmware.local
Release:        5.4.17-2102.205.7.3.el7uek.x86_64
Version:        #2 SMP Fri Sep 17 16:52:13 PDT 2021
Machine:        x86_64
Instance name: tst19c
Redo thread mounted by this instance: 1
Oracle process number: 36
Unix process pid: 3379, image: oracle@19c.vmware.local (TNS V1-V3)


*** 2021-11-05T17:43:04.813682+03:00
*** SESSION ID:(17.38105) 2021-11-05T17:43:04.813696+03:00
*** CLIENT ID:() 2021-11-05T17:43:04.813700+03:00
*** SERVICE NAME:(SYS$USERS) 2021-11-05T17:43:04.813705+03:00
*** MODULE NAME:(SQL*Plus) 2021-11-05T17:43:04.813709+03:00
*** ACTION NAME:() 2021-11-05T17:43:04.813713+03:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-11-05T17:43:04.813716+03:00

2021-11-05 17:43:04.813*:ksq.c@13169: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-00080021-00000432-00000000-00000000         36      17     X        38105      49     269           X  44506
TX-000A0010-000003CB-00000000-00000000         49     269     X        44506      36      17           X  38105

----- Information for waiting sessions -----
Session 17:
  sid: 17 ser: 38105 audsid: 40082 user: 112/DEMO
    flags: (0x8000041) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 36 O/S info: user: oracle, term: UNKNOWN, ospid: 3379
    image: oracle@19c.vmware.local (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/1, ospid: 3331
    machine: 19c.vmware.local program: sqlplus@19c.vmware.local (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  UPDATE D_LOCK SET STRING='two' WHERE DIGIT=2

Session 269:
  sid: 269 ser: 44506 audsid: 40083 user: 112/DEMO
    flags: (0x41) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 49 O/S info: user: oracle, term: UNKNOWN, ospid: 3472
    image: oracle@19c.vmware.local (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/0, ospid: 3469
    machine: 19c.vmware.local program: sqlplus@19c.vmware.local (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 waiting sessions -----


*** 2021-11-05T17:43:04.814338+03:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=6c86fhw5btvby) -----
UPDATE D_LOCK SET STRING='two' WHERE DIGIT=2

----- Call Stack Trace -----
Rows waited on:
  Session 17: obj - rowid = 000127F1 - AAASfxAAHAAAAFsAAB
  (dictionary objn - 75761, file - 7, block - 364, slot - 1)
  Session 269: obj - rowid = 000127F1 - AAASfxAAHAAAAFsAAA
  (dictionary objn - 75761, file - 7, block - 364, slot - 0)

The details listed in the trace file should allow you to identify the application code that caused the problem. For instance, according to the tst19c_ora_3379.trc trace file the session with SID 17 was waiting for a session with SID 269 on ROWID AAASfxAAHAAAAFsAAB, and the session with SID 269 was waiting for a session with SID 17 on ROWID AAASfxAAHAAAAFsAAA.

Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-00080021-00000432-00000000-00000000         36      17     X        38105      49     269           X  44506
TX-000A0010-000003CB-00000000-00000000         49     269     X        44506      36      17           X  38105

...

Rows waited on:
  Session 17: obj - rowid = 000127F1 - AAASfxAAHAAAAFsAAB
  (dictionary objn - 75761, file - 7, block - 364, slot - 1)
  Session 269: obj - rowid = 000127F1 - AAASfxAAHAAAAFsAAA
  (dictionary objn - 75761, file - 7, block - 364, slot - 0)

Statements associated with the DEADLOCK

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

The session with SID 17 and SERIAL# 38105 detected the deadlock and aborted its last statement.

*** 2021-11-05T17:43:04.813682+03:00
*** SESSION ID:(17.38105) 2021-11-05T17:43:04.813696+03:00

...

*** 2021-11-05T17:43:04.814338+03:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=6c86fhw5btvby) -----
UPDATE D_LOCK SET STRING='two' WHERE DIGIT=2

By knowing the exact ROWIDs of rows associated with the DEADLOCK, let’s find out their values.

SQL> SELECT DBMS_ROWID.ROWID_OBJECT('AAASfxAAHAAAAFsAAB') OBJECT_ID FROM dual;

 OBJECT_ID
----------
     75761


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

OWNER      OBJECT_NAME
---------- ------------
DEMO       D_LOCK


SQL> SELECT ROWID,T.* FROM DEMO.D_LOCK T WHERE ROWID in ('AAASfxAAHAAAAFsAAB','AAASfxAAHAAAAFsAAA');

ROWID                   DIGIT STRING
------------------ ---------- ----------
AAASfxAAHAAAAFsAAA          1 one
AAASfxAAHAAAAFsAAB          2 002

The demo is completed. Drop the DEMO user

SQL> SHO USER
USER is "SYS"

SQL> DROP USER DEMO CASCADE;

User dropped.
REFERENCES

How to Get a ROWID From a Locked Row in a Table (Doc ID 1082439.6)
OERR: ORA-60 "deadlock detected while waiting for resource" (Doc ID 18251.1)
How To Indentify The Row Which is Locked By an Other User's Session (Doc ID 198150.1)

Written At
05 NOV 202120:30
OEL Release
7.9 x64
Database Release
19.12.0.0.0