2010-12-21

ORA-00060: Deadlock detected

In an OLTP environment, when you encounter this error check if you have bitmap indexes on you transactional tables.

This type of index may be a cause for ORA-60 .See also : http://www.oratechinfo.co.uk/deadlocks.html


Bitmap Indexes and Deadlocks: Deadlocks on Insert Statements [ID 171795.1]

Modified 28-SEP-2010     Type TROUBLESHOOTING     Status PUBLISHED


PURPOSE
-------

The purpose of this article is to explain the occurrence of deadlocks
when the only DML activity is insert statements against a table with 
a bitmap index.  This applies from version 8 to 11.

SCOPE & APPLICATION
-------------------

Database administrators and Application developers involved in 
application design.


BITMAP INDEXES: THE HIDDEN DEADLOCK THREAT
------------------------------------------


The limitations of bitmap indexes as this:

Extract of documentation:

"DML and DDL statements, such as UPDATE, DELETE, DROP TABLE, affect bitmap 
indexes the same way they do traditional indexes: the consistency model is 
the same. A compressed bitmap for a key value is made up of one or more bitmap 
segments, each of which is at most half a block in size (but may be smaller). 
The locking granularity is one such bitmap segment. This may affect performance 
in environments where many transactions make simultaneous updates. If numerous
DML operations have caused increased index size and decreasing performance for 
queries, then you can use the ALTER INDEX ... REBUILD statement to compact the 
index and restore efficient performance. 

A B*-tree index entry contains a single rowid. Therefore, when the index entry 
is locked, a single row is locked. With bitmap indexes, an entry can potentially
contain a range of rowids. When a bitmap index entry is locked, the entire range
of rowids is locked. The number of rowids in this range affects concurrency.
As the number of rowids increases in a bitmap segment, concurrency decreases. 

Locking issues affect DML operations, and may affect heavy OLTP environments. 
Locking issues do not, however, affect query performance. As with other types 
of indexes, updating bitmap indexes is a costly operation. Nonetheless, for 
bulk inserts and updates where many rows are inserted or many updates are made
in a single statement, performance with bitmap indexes can be better than with
regular B*-tree indexes."

                         **************

What is not mentioned is the fact that the same architectural feature that 
locks a range of rowid's also means that its possible to get a deadlock within
the bitmap when updating rows in the underlying table. This deadlock is not in
the table itself, as one might suspect, but rather in the bitmap index blocks. 
This kind of deadlock is easily diagnosable by the deadlock trace file, which 
has an entry that looks like the example below:

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. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00080027-0000d2a1        12      37     X             15      35           S
TX-000a0016-0000d6d2        15      35     X             12      37           S
session 37: DID 0001-000C-00000002 session 35: DID 0001-000F-00000002
session 35: DID 0001-000F-00000002 session 37: DID 0001-000C-00000002
Rows waited on:
Session 35: no row
Session 37: no row

The piece of information that leads us to a bitmap deadlock is the "no row" 
value in the session information.  If we had encountered a deadlock in the 
underlying table, the Session line would give us row information so that we 
could track down the exact point of failure. Without a row, it would seem that
we are at a dead end. Even more mysterious is when we get this deadlock on
inserts, where we are inserting only new rows and therefore it would seem 
impossible to get a deadlock. No one should be requesting a row that someone 
else holds locked. 

There are no solutions to this kind of problems, except not using bitmap indexes
when having an application where you can't control when the DML are issued against
the tables with bitmap indexes. Bitmaps are normally intended for datawarehouse 
applications that are loading data via batches and that users are only querying.

The following testcase can be used to see the results of this type of problem. 
We will create a table called CAR_TYPE, which holds information about cars,
including the car's color. We will build a bitmap index on the COLOR column. 
After doing so, we will populate the table with data. After the initial insert,
we will open two sessions of the same user, and run simultaneous inserts into 
the CAR_TYPE table. 

TESTCASE:

=====================================
=====================================

create table car_type (
make varchar2(20),
model varchar2(20),
color varchar2(20),
VIN number(15) primary key,
year number(4)); 

create bitmap index car_type_bm_idx on car_type(color); 

create sequence car_type_seq
start with 35001
increment by 1
nocache
nocycle; 

declare
v_CarMake varchar2(20) := 'Audi'; 
v_CarModel varchar(20) := 'Quattro'; 
v_CarColor varchar(20) := 'Gold'; 
v_CarVin binary_integer :=1; 
begin
loop
insert into car_type (make,model,color,VIN,year)
values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002'); 
v_CarVin := v_CarVin + 1; 
exit when v_CarVin > 5000;
end loop;
end;
/
commit;

declare
v_CarMake varchar2(20) := 'Toyota';
v_CarModel varchar(20) := 'Camry';
v_CarColor varchar(20) := 'Red';
v_CarVin binary_integer :=5001;
begin
loop
insert into car_type (make,model,color,VIN,year)
values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
v_CarVin := v_CarVin + 1;
exit when v_CarVin > 10000;
end loop;
end;
/
commit;

declare
v_CarMake varchar2(20) := 'Audi';
v_CarModel varchar(20) := 'Quattro';
v_CarColor varchar(20) := 'Blue';
v_CarVin binary_integer :=10001;
begin
loop
insert into car_type (make,model,color,VIN,year)
values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
v_CarVin := v_CarVin + 1;
exit when v_CarVin > 15000;
end loop;
end;
/
commit;

declare
v_CarMake varchar2(20) := 'Toyota';
v_CarModel varchar(20) := 'Camry';
v_CarColor varchar(20) := 'Silver';
v_CarVin binary_integer :=15001;
begin
loop
insert into car_type (make,model,color,VIN,year)
values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
v_CarVin := v_CarVin + 1;
exit when v_CarVin > 20000;
end loop;
end;
/
commit;

declare
v_CarMake varchar2(20) := 'Audi';
v_CarModel varchar(20) := 'Quattro';
v_CarColor varchar(20) := 'Green';
v_CarVin binary_integer :=20001;
begin
loop
insert into car_type (make,model,color,VIN,year)
values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
v_CarVin := v_CarVin + 1;
exit when v_CarVin > 25000;
end loop;
end;
/
commit;

declare
v_CarMake varchar2(20) := 'Audi';
v_CarModel varchar(20) := 'Quattro';
v_CarColor varchar(20) := 'Black';
v_CarVin binary_integer :=25001;
begin
loop
insert into car_type (make,model,color,VIN,year)
values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
v_CarVin := v_CarVin + 1;
exit when v_CarVin > 30000;
end loop;
end;
/
commit;

declare
v_CarMake varchar2(20) := 'Toyota';
v_CarModel varchar(20) := 'Camry';
v_CarColor varchar(20) := 'White';
v_CarVin binary_integer :=30001;
begin
loop
insert into car_type (make,model,color,VIN,year)
values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
v_CarVin := v_CarVin + 1;
exit when v_CarVin > 35000;
end loop;
end;
/
commit;

===============================
===============================

After this initial creation, cut the following script into a .sql file, and 
then execute it simultaneously from two sessions:

===============================
===============================

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002');  

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 
 
insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002');

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002');
 commit;

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 
commit;

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 
commit;

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002');

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002');

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 
commit;
insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002');

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002');
 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002');

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 


insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002');

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002');

insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002');

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002'); 
commit;
insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','White',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Red',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Toyota','Camry','Silver',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Black',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
 
insert into car_type values (
'Audi','Quatro','Blue',car_type_seq.nextval,'2002'); 

insert into car_type values (
'Audi','Quatro','Green',car_type_seq.nextval,'2002'); 
commit;

========================================
========================================

The result will be occasional deadlock errors:

insert into car_type values (
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

The trace file will show the tell-tale 'No Row' message:

Rows waited on:
Session 11: no row
Session 10: no row





Show Related Information Related


Products

  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
Keywords

DEADLOCK
Errors

ORA-60

Back to topBack to top

Niciun comentariu:

Trimiteți un comentariu