Sunday, July 22, 2012

ORA-0060 – DEADLOCK

We all know deadlock is the situation where DBA cannot do anything and directly can be blamed on application saying – this is an application issue. Oracle writes the same in its trace file of 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.

I agree to above but we, DBA can help application developer in determining cause of the deadlock. Here are some samples to check the clause of the deadlock.

It is very important to analyze the deadlock graph especially blocker and waiter waits. In normal situations when it is purely an application coding issue, the waits would be row exclusive “X” when other cases this may change. Let’s see in the following examples.

Traditional Deadlock


create table u (id number, name varchar2(20));
insert into u values (1, 'OPERATION1');
insert into u values (2, 'OPERATION2');
commit;

SRSESSION 1SESSION 2
1update u set name='OPERATION_1' where id=1;
2update u set name='OPERATION_2' where id=2;
3update u set name='OPERATION_2' where id=2;
4update u set name='OPERATION_1' where id=1;
5DEADLOCK ERROR

Deadlock graph:


                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-00080009-00002c6b       21     133     X            24      14           X
TX-00020005-00002c70       24      14     X            21      133          X

session 133: DID 0001-0015-00000015 session 14: DID 0001-0018-00000018
session 14: DID 0001-0018-00000018 session 133: DID 0001-0015-00000015


Rows waited on:
  Session 133: obj - rowid = 000036A3 - AAADajAAEAAAAC0AAB
  (dictionary objn - 13987, file - 4, block - 180, slot - 1)
  Session 14: obj - rowid = 000036A3 - AAADajAAEAAAAC0AAA
  (dictionary objn - 13987, file - 4, block - 180, slot - 0)

----- Information for the OTHER waiting sessions -----


Session 14:
  sid: 14 ser: 35 audsid: 150059 user: 34/SANDY flags: 0x45
  pid: 24 O/S info: user: SYSTEM, term: LVNTW03526D3BS, ospid: 4268
    image: ORACLE.EXE (SHAD)
  client details:
    O/S info: user: ASIAPAC\N228937, term: LVNTW03526D3BS, ospid: 2700:1872
    machine: ASIAPAC\LVNTW03526D3BS program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  current SQL:
    update u set name='OPERATION_1' where id=1


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

Information for THIS session:


----- Current SQL Statement for this session (sql_id=4r2c68v92xnn2) -----
update u set name='OPERATION_2' where id=2


Deadlock trace file also gives you all waiters and holder sessions’ information. In above trace file section, the first highlighted query is of session that had caused the deadlock. The next highlighted query is of the session which was terminated by oracle and got “DEADLOCK DETECTED” error.

Case 1 – Insert in Primary key column

create table primary (id number primary key, name varchar2(20));
SRSESSION 1SESSION 2
1insert into primary values (1, 'Sandeep');
2insert into primary values (2, 'Redkar');
3insert into primary values (2, 'Redkar');
4insert into primary values (1, 'Sandeep');
5DEADLOCK ERROR

Deadlock graph:
                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-00030007-00002c81       24      14     X            21     133           S
TX-00060005-00002c9e       21     133     X            24      14           S

session  14: DID 0001-0018-00000013 session 133: DID 0001-0015-00000010
session 133: DID 0001-0015-00000010 session 14: DID 0001-0018-00000013

Rows waited on:
  Session 14: no row
  Session 133: no row

Note – This deadlock situation can be possible when using unique index also.


Solution – To avoid this deadlock, you can make use of sequences.

Case 2 – Insert in Primary key/Foreign key.

create table foreign (id number, addr varchar2(30), phone number);


alter table foreign add constraint fk_id foreign key (id) references primary(id);

insert into primary
select level, dbms_random.string('A',10)
from dual connect by level <= 100;
commit;

insert into foreign
select round(dbms_random.value(1,100)),
dbms_random.string('A',10),
round(dbms_random.value(1000000,9999999))
from dual connect by level<=1000;


commit;
SRSESSION 1SESSION 2
1update foreign set phone=9999999 where id=20;
2update foreign set phone=9999999 where id=10;
3update primary set id=50, name='Sandeep' where id=50;
4update primary set id=60, name='Sandy' where id=60;
5DEADLOCK ERROR

Deadlock graph:

                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TM-0000369e-00000000       21     133    SX   SSX      24      14    SX   SSX
TM-0000369e-00000000       24      14    SX   SSX      21     133    SX   SSX

session 133: DID 0001-0015-00000011 session 14: DID 0001-0018-00000014
session 14: DID 0001-0018-00000014 session 133: DID 0001-0015-00000011

Rows waited on:
  Session 133: no row
  Session 14: no row

Solution – In this situation, oracle needs TM lock when inserting into foreign key column. In earlier oracle releases (8i and prior) even when inserting into primary column, you have to have TM lock on foreign key table. This has been taken care in release 9i onwards. But issue with foreign key is still there.


To avoid this situation there are two solutions –
1. Create index on foreign key (Oracle by default creates index when defining primary key constraint. But oracle will never creates index when defining foreign key constraint)
2. Do not update primary key column, though with same/other value.

Case 3– Bitmap index – Part I

create table t (id number, gender varchar2(1));
create bitmap index bit_t_ind on t(gender);
SRSESSION 1SESSION 2
1insert into t values (1,'M');
2insert into t values (2,'F');
3insert into t values (4,'F');
4insert into t values (3,'M');
5DEADLOCK ERROR

Deadlock graph:

                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-00060021-00002c9f       24      14     X            21     133           S
TX-00050005-00002c7c       21     133     X            24      14           S


session 14: DID 0001-0018-00000015 session 133: DID 0001-0015-00000012
session 133: DID 0001-0015-00000012 session 14: DID 0001-0018-00000015


Rows waited on:
  Session 14: no row
  Session 133: no row

Solution – In OLTP system we should avoid bitmap index when table is highly used by the application for DML operations. This deadlock situation can be avoided by creating b-tree (normal) index based on the index selectivity. In the deadlock trace file, you can see “no rows” on “Rows waiting on” section. Most of the cases, this can be found when using bitmap index.

If you remember, we got “no rows” information when inserting into primary key columns. It is difficult to distinguish which had caused the deadlock (bitmap or primary key). Best way to check for which type of index exists on the table.

Case 4– Bitmap index – Part II


insert into t
select level, decode(mod(level,2),0,'M','F')
from dual connect by level<=100;

commit;


SRSESSION 1SESSION 2
1Insert into t values (1,'M');
2insert into t values (2,'F');
3insert into t values (4,'F');
4insert into t values (3,'M');
5DEADLOCK ERROR

Deadlock graph:

                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-0006001b-00002ca0       24      14     X            21     133           S
TX-00090021-00002c6c       21     133     X            24      14           S

session 14: DID 0001-0018-00000016 session 133: DID 0001-0015-00000013
session 133: DID 0001-0015-00000013 session 14: DID 0001-0018-00000016

Note – This is very similar situation, as previous case (Part I). If you observe, the trace file, in previous example, we got “no rows” and here we got the rows information even test case is same. Why is this difference? The difference between these two test cases is, in previous case the table was empty and not in the above case. Now you may think, how to distinguish whether this has caused because of bitmap issue? In Rows waited on section, below rowid information, there is object number given. If you check the object number you can find this is of bitmap index.

SQL> select owner, object_name, object_type from dba_objects where object_id=13984; 
 


OWNER  OBJECT_NAME          OBJECT_TYPE
------ -------------------- -------------------
SANDY  BIT_T_IND            INDEX


Case 5– Autonomous Transaction


create table d (v varchar2(20));
insert into d values ('S');
commit;

CREATE OR REPLACE PROCEDURE proc_del
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM D;
COMMIT;
END;
/
SRSESSION 1
1delete from d;
2exec proc_del
3DEADLOCK ERROR

Deadlock graph:
                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name         process session holds waits process session holds waits
TX-00060005-00002c9f       21     133     X            21     133           X


session 133: DID 0001-0015-00000014 session 133: DID 0001-0015-00000014


Rows waited on:
Session 133: obj - rowid = 000036A1 - AAADahAAEAAAACsAAB
(dictionary objn - 13985, file - 4, block - 172, slot - 1)

Solution – This is example of autonomous transaction. Triggers are also form of autonomous transactions. This issue may also seen with update operation or combination of update and delete operation.


The solutions above given are just sample situations wherein Deadlock can happen. There are many other situations wherein Deadlock can occur. The deadlock graph gives the hint to identify the deadlock cause. Application desing needs to be changed for Transaction Level Deadlocks but other than this, there could be solutions to Deadlock. 

To sum up, though the deadlock is an issue of application, we can identify the cause and solution can provided to get rid of deadlock issue.

1 comment:

Anonymous said...

Thanks. Very informative.
Regards
GregG