ORA-00060: deadlock detected while waiting for resource

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.