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.
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