ORA-00060: DEADLOCK DETECTED WHILE WAITING FOR RESOURCE
QUESTIONS
How to identify sessions involved in a deadlock?
How to eliminate a deadlock in a database?
SYMPTOMPS
CAUSE
REFERENCES
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=’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)
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0