DEMO: ORA-00060: DEADLOCK DETECTED WHILE WAITING FOR RESOURCE
What is a deadlock?
How to identify sessions associated with a deadlock?
HOW TO IDENTIFY BLOCKED AND BLOCKING SESSIONS IN ORACLE DATABASE?
You observe one or more ORA-00060 errors in your alert log
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.
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 TWOEstablish 2 new sessions by the DEMO user from 2 different terminals
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
----------
269As 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 002Go 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 6c86fhw5btvbyBy 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 TWOWhat is about the second session? As no deadlock exists anymore, the second session continues its work. The second session became the WAITING SESSION. The DBT_BLOCKERS displays the all necessary information about current blocking and waiting sessions. For instance
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 2733dnjgunryhAfter 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=1The 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 002The demo is completed. Drop the DEMO user
SQL> SHO USER
USER is "SYS"
SQL> DROP USER DEMO CASCADE;
User dropped.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)
