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.
Traditional Deadlock
create table u (id number, name varchar2(20));
insert into u values (1, 'OPERATION1');
insert into u values (2, 'OPERATION2');
commit;
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));
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;
| SR | SESSION 1 | SESSION 2 |
|---|---|---|
| 1 | update u set name='OPERATION_1' where id=1; | |
| 2 | update u set name='OPERATION_2' where id=2; | |
| 3 | update u set name='OPERATION_2' where id=2; | |
| 4 | update u set name='OPERATION_1' where id=1; | |
| 5 | DEADLOCK 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));
| SR | SESSION 1 | SESSION 2 |
|---|---|---|
| 1 | insert into primary values (1, 'Sandeep'); | |
| 2 | insert into primary values (2, 'Redkar'); | |
| 3 | insert into primary values (2, 'Redkar'); | |
| 4 | insert into primary values (1, 'Sandeep'); | |
| 5 | DEADLOCK 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;
| SR | SESSION 1 | SESSION 2 |
|---|---|---|
| 1 | update foreign set phone=9999999 where id=20; | |
| 2 | update foreign set phone=9999999 where id=10; | |
| 3 | update primary set id=50, name='Sandeep' where id=50; | |
| 4 | update primary set id=60, name='Sandy' where id=60; | |
| 5 | DEADLOCK 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);
| SR | SESSION 1 | SESSION 2 |
|---|---|---|
| 1 | insert into t values (1,'M'); | |
| 2 | insert into t values (2,'F'); | |
| 3 | insert into t values (4,'F'); | |
| 4 | insert into t values (3,'M'); | |
| 5 | DEADLOCK 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;
| SR | SESSION 1 | SESSION 2 |
|---|---|---|
| 1 | Insert into t values (1,'M'); | |
| 2 | insert into t values (2,'F'); | |
| 3 | insert into t values (4,'F'); | |
| 4 | insert into t values (3,'M'); | |
| 5 | DEADLOCK 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;
/
| SR | SESSION 1 |
|---|---|
| 1 | delete from d; |
| 2 | exec proc_del |
| 3 | DEADLOCK 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.
