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.
--Create table TABLE1
SQL> create table t1.table1 (name varchar(20),id number(10),primary key (id));

Table created.
--Create table TABLE2
SQL> create table t2.table2 (name varchar(20),id number(10),primary key (id));

Table created.


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.

-Add foreign key with reference to id column in TABLE1

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

Table altered.

-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.
--Insert rows to TABLE1

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

3 rows created.

SQL> commit;

Commit complete.

--Insert rows to TABLE2

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

3 rows created.

SQL> commit;

Commit complete.

--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-000A0011-000001CF-00000000-00000000         39      54     X              6       1           X
TX-0004000B-000001D8-00000000-00000000          6       1     X             39      54           X

Rows waited on:
  Session 54: obj - rowid = 000050B1 - AAAFCxAAEAAAACUAAA
  (dictionary objn - 20657, file - 4, block - 148, slot - 0)
  Session 1: obj - rowid = 000050AF - AAAFCvAAEAAAACEAAA
  (dictionary objn - 20655, file - 4, block - 132, slot - 0)

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

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

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