DEADLOCK ORA-00060 ORACLE

Error:

ORA-00060: deadlock detected while waiting for resource

Cause:

Deadlock is not an oracle error but an application design issue which happen due to two way block also known as mutual embrace

Here is a small example which may help understand deadlock

Either we need to break the barrier or construct the design of the road correctly. Similarly in technical terms, either the application sessions which cause the deadlock should be identified and killed (or) the application design should be properly constructed.

Deadlock illustration

Another simple diagram which can help understand deadlock

                 exclusive lock X
SID 1 ==============>TABLE1
                                                       ◥  (blocked)
(1 need to                              ╱
acquire lock  ╲             ╱                         
on TABLE1)        ╲  ╱
                            ╱   ╲  Deadlock
                      ╱                ╲
 (54 need to                           ╲
acquire lock                                 ◢
on TABLE2 )                               (blocked)
SID 54 ==============> TABLE2
                   exclusive lock X

Symptoms:

Whenever a deadlock occur, there occur enqueues in AWR report

A simple simulation to cause a deadlock

--Create user t1

SQL> create user t1 identified by password;

User created.

--Create another user t2

SQL> create user t2 identified by password;

User created.

--Grant privileges to t1

SQL> grant connect,resource,unlimited tablespace,create session to t1;

Grant succeeded.

--Grant privileges to t2

SQL> grant connect,resource,unlimited tablespace,create session to t2;

Grant succeeded.
SQL> conn t1
Enter password:
Connected.
--create table t1
SQL> create table t1.table1 (name varchar(20),id number(10),primary key (id));

Table created.

SQL> conn t2
Enter password:
Connected.
--create table t2
SQL> create table t2.table2 (name varchar(20),id number(10),primary key (id));

Table created.

SQL> conn / as sysdba
Connected.
SQL> grant select,insert,update,delete,references on t2.table2 to t1;

Grant succeeded.

SQL> grant select,insert,update,delete,references on t1.table1 to t2;

Grant succeeded.

--Insert rows to TABLE1

SQL> insert all into t1.table1 values('abc',1)
into t1.table1 values('bcd',2)
into t1.table1 values('cde',3)
  select * from dual;

3 rows created.

SQL> commit;

Commit complete.

--Insert rows to TABLE2

SQL> insert all into t2.table2 values('abc',1)
into t2.table2 values('bcd',2)
into t2.table2 values('cde',3)
select * from dual;

3 rows created.

SQL> commit;

Commit complete.

-Add foreign key with reference to id column in TABLE2

SQL> alter table t2.table2 add constraint fk_id1 foreign key(id) references t1.table1(id);

Table altered.
--Gather stats on TABLE1 and TABLE2

SQL> exec dbms_stats.gather_table_stats('T1','TABLE1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('T2','TABLE2');

PL/SQL procedure successfully completed.

--Connect to t1 and update a row in TABLE1

SQL> connect t1
Enter password:
Connected.
SQL>
SQL> update t1.table1 set name='opn' where id=1;

1 row updated.

--Connect to t2 and update a row in TABLE2

SQL> connect t2
Enter password:
Connected.
SQL> update t2.table2 set name='xyz' where id=1;

1 row updated.

--Check the sid and serial# of the two sessions t1 and t2
SQL> connect / as sysdba
SQL> select sid,serial#,program,module from v$session where program like '%sqlplus%';

       SID    SERIAL# PROGRAM              MODULE
---------- ---------- -------------------- --------------------
         1      48611 sqlplus@dptest (TNS  SQL*Plus
                      V1-V3)

        54      36555 sqlplus@dptest (TNS  SQL*Plus
                      V1-V3)


--Here both TABLE1 and TABLE2 have exclusive locks by session t1 and t2

SQL> select OBJECT_ID,SESSION_ID,PROCESS,LOCKED_MODE from v$locked_object where SESSION_ID in ('1','54');

 OBJECT_ID SESSION_ID PROCESS                  LOCKED_MODE
---------- ---------- ------------------------ -----------
     20657          1 14965                              3
     20655         54 15378                              3

--Using object_id, get the object name

SQL> select owner,object_id,object_name,object_type from dba_objects where object_id in ('20657','20655');

OWNER                 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
-------------------- ---------- -------------------- -----------------------
T1                        20655 TABLE1               TABLE
T2                        20657 TABLE2               TABLE

--Again connect to t1 and update TABLE2 from session t1
--Here we can see the message deadlock detected
SQL> connect t1
Enter password:
Connected.
SQL> update t2.table2 set name='xyz' where id=1;
update t2.table2 set name='xyz' where id=1
                                      *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

connect to t2 and update TABLE1 from session t2

SQL> connect t2
Enter password:
Connected.
SQL> update t1.table1 set name='opn' where id=1;

Interpretation of deadlock:

Always look for the trace file from alertlog by searching with the error ‘ora-00060’

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/mtdb/mtdb/trace/mtdb_ora_15561.trc.

Almost all trace file generated during deadlock contains deadlock graph which has the information such as blocking and waiting sessions involved , SQL statement, process id from OS etc..

It is important to note the “Rows waited on:” session which contains useful information on locked rows during deadlock which can be useful for application developers for investigation

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00070010-000002f2        25     161     X             34      19           X
TX-0006000a-0001ebb4        34      19     X             25     161           X

session 161: DID 0001-0019-0000009F     session 19: DID 0001-0022-00000108
session 19: DID 0001-0022-00000108      session 161: DID 0001-0019-0000009F

Rows waited on:
  Session 161: obj - rowid = 00015A6C - AAAVpsAAEAAAAILAAA
  (dictionary objn - 88684, file - 4, block - 523, slot - 0)
  Session 19: obj - rowid = 00015A6E - AAAVpuAAEAAAAIbAAA
  (dictionary objn - 88686, file - 4, block - 539, slot - 0)

Identify the rowid and block number from the victim tables with the help of above deadlock trace information.

SQL> select object_name,object_type from dba_objects where object_id in ('88686','88684');

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
TABLE1               TABLE
TABLE2               TABLE

SQL> select rowid,dbms_rowid.rowid_block_number(rowid) "BLOCK" from t2.table2 where dbms_rowid.rowid_block_number(rowid)=539;

ROWID                   BLOCK
------------------ ----------
AAAVpuAAEAAAAIbAAA        539
AAAVpuAAEAAAAIbAAB        539
AAAVpuAAEAAAAIbAAC        539

SQL> select rowid,dbms_rowid.rowid_block_number(rowid) "block" from t1.table1 where dbms_rowid.rowid_block_number(rowid)=523;

ROWID                   BLOCK
------------------ ----------
AAAVpsAAEAAAAILAAA        523
AAAVpsAAEAAAAILAAB        523
AAAVpsAAEAAAAILAAC        523

Below are information related to the deadlock sessions and the SQL

----- Information for the OTHER waiting sessions -----
Session 1:
  sid: 1 ser: 48611 audsid: 320008 user: 66/T2
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 6 O/S info: user: oracle, term: UNKNOWN, ospid: 15503
    image: oracle@dptest (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/0, ospid: 14965
    machine: dptest program: sqlplus@dptest (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  update t1.table1 set name='opn' where id=1

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=ft7mktbm8vs76) -----
update t2.table2 set name='xyz' where id=1
===================================================
PROCESS STATE
-------------
Process global information:
     process: 0xb9a5f458, call: 0xb9fb9ac0, xact: 0xb7e35360, curses: 0xb9e942a8, usrses: 0xb9e942a8
     in_exception_handler: no
  ----------------------------------------
  SO: 0xb9a5f458, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0xb9a5f458, name=process, file=ksu.h LINE:13949, pg=0 conuid=0
  (process) Oracle pid:39, ser:176, calls cur/top: 0xb9fb9ac0/0xb9fb9ac0
            flags : (0x0) -  icon_uid:0

Traces can be manually generated for deadlock using 10027 code which is an undocumented event

SQL> update t2.table2 set name='xyz' where id=1;

1 row updated.

SQL> alter session set tracefile_identifier='deadlock60';

Session altered.

SQL> alter session set events '10027 trace name context forever,level 12';

Session altered.

SQL> update t1.table1 set name='opn' where id=1;
update t1.table1 set name='opn' where id=1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


SQL> alter session set events '10027 trace name context off';

Session altered.

SQL> exit

Check the type of enqueue which the session wait on. Whenever a deadlock is observed, always there should be an enqueue waits behind error. It is DBA’s responsibility to diagnose the enqueue type and proceed accordingly

Below wait stack from trace file shows enq: TX – row lock contention which is a common enqueue during multiple update operations to safeguard the transactions from concurrency. This TX lock is an application code issue or overlapping job schedule at same time.

    Current Wait Stack:
     0: waiting for 'enq: TX - row lock contention'     <<<<<=====
        name|mode=0x54580006, usn<<16 | slot=0x6000a, sequence=0x1ebb4
        wait_id=20 seq_num=23 snap_id=2
        wait times: snap=0.016490 sec, exc=3.013926 sec, total=3.089331 sec
        wait times: max=infinite, heur=3.089331 sec
        wait counts: calls=1 os=1
        in_wait=1 iflags=0x15a0

Identify the enqueue activity using ASH tables

col FAILED_REQ# for 9999999
col TOTAL_REQ# for 99999999
col TOTAL_WAIT# for 9999999
col CUM_WAIT_TIME for 9999999
col EVENT# for 9999999
col EQ_TYPE for a10 justify center
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
select to_char(dhs.BEGIN_INTERVAL_TIME,'dd-mm-yy hh24:mi') as STIME,
       to_char(dhs.END_INTERVAL_TIME,'dd-mm-yy hh24:mi') as ETIME,
kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^>kish@x3z6zx9<^>       dhes.EQ_TYPE,
       dhes.REQ_REASON,
           dhes.TOTAL_REQ#,
           dhes.TOTAL_WAIT#,
           dhes.SUCC_REQ#,
           dhes.FAILED_REQ#,
           dhes.CUM_WAIT_TIME,
           dhes.EVENT#
from dba_hist_enqueue_stat dhes
inner join dba_hist_snapshot dhs on ( dhes.snap_id = dhs.snap_id )
where EQ_TYPE='TX';
kish@x3z6zx9<^>  2    3    4    5    6    7    8    9   10   11   12   13
STIME          ETIME           EQ_TYPE   REQ_REASON           TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME   EVENT#
-------------- -------------- ---------- -------------------- ---------- ----------- --------- ----------- ------------- --------
02-05-22 21:30 02-05-22 22:30 TX         contention              1259347           0   1259347           0             0      778
04-05-22 22:11 04-05-22 22:22 TX         contention                 1771           0      1771           0             0      778
05-05-22 18:34 05-05-22 18:45 TX         contention                  113           0       113           0             0      778
05-05-22 18:45 05-05-22 19:30 TX         contention              1175829           0   1175829           0             0      778
05-05-22 19:30 05-05-22 20:30 TX         contention              2406298           0   2406298           0             0      778
05-05-22 20:30 05-05-22 21:30 TX         contention              2758804           0   2758804           0             0      778
06-05-22 16:30 06-05-22 17:30 TX         contention              3566368           0   3566368           0             0      778
08-05-22 09:04 08-05-22 10:30 TX         row lock contention           1           1         1           0             0      241
08-05-22 09:04 08-05-22 10:30 TX         contention                 7286           0      7286           0             0      778
08-05-22 09:04 08-05-22 10:30 TX         allocate ITL entry            6           6         6           0           460      242
07-05-22 17:41 07-05-22 17:52 TX         contention               138219           0    138219           0             0      778
05-05-22 21:30 05-05-22 22:30 TX         contention              3956617           0   3956617           0             0      778
06-05-22 14:53 06-05-22 15:04 TX         contention               225435           0    225435           0             0      778
06-05-22 15:04 06-05-22 16:30 TX         contention              2709339           0   2709339           0             0      778
06-05-22 17:30 06-05-22 18:30 TX         contention              3915564           0   3915564           0             0      778
02-05-22 20:29 02-05-22 21:30 TX         contention                52936           0     52936           0             0      778
08-05-22 08:53 08-05-22 09:04 TX         contention                 2796           0      2796           0             0      778
08-05-22 08:53 08-05-22 09:04 TX         allocate ITL entry            6           6         6           0           460      242
08-05-22 10:30 08-05-22 11:30 TX         row lock contention           9           9         3           0        147020      241
08-05-22 10:30 08-05-22 11:30 TX         contention                 7974           0      7974           0             0      778
08-05-22 10:30 08-05-22 11:30 TX         allocate ITL entry            6           6         6           0           460      242
08-05-22 11:30 08-05-22 12:30 TX         row lock contention           9           9         3           0        147020      241
08-05-22 11:30 08-05-22 12:30 TX         contention                 8257           0      8257           0             0      778
08-05-22 11:30 08-05-22 12:30 TX         allocate ITL entry            6           6         6           0           460      242

24 rows selected.

Solution:

1)There is no oracle specified solution for deadlock issues. As a workaround, the blocker session can be identified and killed

2)Schedule the application jobs at a different time so that the jobs will not overlap each other

Leave a Reply