ORA-00060: deadlock detected while waiting for resource
Error
ORA-00060: deadlock detected while waiting for resource
–Create the table for demo the deadlock error:
SQL> create table test1 (id number);
Table created.
SQL> create table test2(id number);
Table created.
SQL> insert into test1 values (1);
1 row created.
SQL> insert into test2 values(1);
1 row created.
SQL> commit;
Commit complete.
–Assign permission with SQL DBA USER:
GRANT EXECUTE ON DBMS_LOCK TO hr;
–Procedure to make the deadlock saturation for getting deadlock error in one session:
Session 1:
SQLPLUS HR/HR
Begin
update test1 set id = 2;
DBMS_LOCK.sleep(60);
update test2 set id =1;
Rollback;
End;
/
Session 2:
sqlplus HR/HR
Begin
update test2 set id =1;
DBMS_LOCK.sleep(60);
update test1 set id = 2;
Rollback;
End;
/
Session 1:
SQL> /
Begin
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 6
Cause:
One transaction is holding lock on resource which is needed by second transaction and second transaction hold lock on first transaction waiting resource.
Solution
1. Basic idea is to modified the code to prevent this error by proper using commit and rollback commands.
2. For modify, you need to identify the code, you can check the error messages in the alert log.
-- Error in Alert Log file:
2020-03-26T10:34:52.557135+05:30
XEPDB1(3):Errors in file C:\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_10032.trc:
2020-03-26T10:34:54.911617+05:30
XEPDB1(3):ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More info in file C:\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_10032.trc.
3. Check the relevant trace file(s) shown in alert log file.
Trace file C:\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_10032.trc
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Build label: RDBMS_18.4.0.0.0DBRU_WINDOWS.X64_190202.XE
Windows NT Version V6.2
ORACLE_HOME = C:\Oracle\dbhomeXE
Node name : ORACLEMC
CPU : 8 - type 8664, 4 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:9012M/16243M, Ph+PgF:7650M/18675M
Instance name: xe
Redo thread mounted by this instance: 1
Oracle process number: 50
Windows thread id: 10032, image: ORACLE.EXE (SHAD)
*** 2020-03-26T10:34:52.524218+05:30 (XEPDB1(3))
*** SESSION ID:(203.4750) 2020-03-26T10:34:52.525211+05:30
*** CLIENT ID:() 2020-03-26T10:34:52.525211+05:30
*** SERVICE NAME:(xepdb1) 2020-03-26T10:34:52.525211+05:30
*** MODULE NAME:(SQL*Plus) 2020-03-26T10:34:52.525211+05:30
*** ACTION NAME:() 2020-03-26T10:34:52.525211+05:30
*** CLIENT DRIVER:(SQL*PLUS) 2020-03-26T10:34:52.525211+05:30
*** CONTAINER ID:(3) 2020-03-26T10:34:52.525211+05:30
2020-03-26 10:34:52.501*:ksq.c@13053: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-000A0020-00000E20-0C9712BE-00000000 50 203 X 4750 51 292 X 63898
TX-0005001B-000007CB-0C9712BE-00000000 51 292 X 63898 50 203 X 4750
4. Identify the SQL statements in both the current session and the waiting session(s).
-- you get SQL information in this part of trace file:
----- Information for waiting sessions -----
Session 203:
sid: 203 ser: 4750 audsid: 3580012 user: 102/HR
pdb: 3/XEPDB1
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 50 O/S info: user: OracleServiceXE, term: ORACLEMC, ospid: 10032
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: oracleuser, term: ORACLEMC, ospid: 13508:1716
machine: ORCL\ORACLEMC program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
current SQL:
UPDATE TEST2 SET ID =1
Session 292:
sid: 292 ser: 63898 audsid: 3580011 user: 102/HR
pdb: 3/XEPDB1
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 51 O/S info: user: OracleServiceXE, term: ORACLEMC, ospid: 1940
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: oracleuser, term: ORACLEMC, ospid: 11664:10612
machine: ORCL\ORACLEMC program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
current SQL:
UPDATE TEST1 SET ID = 2
5. Use these SQL statements to identify the particular piece of code that is having problems. You can modify code with proper rollback or commit commands.
Smart way of Technology 