Bitmap Indexes and Deadlocks: Deadlocks on Insert Statements

文档 ID: 注释:171795.1 类型: TROUBLESHOOTING
上次修订日期: 18-MAY-2004 状态: 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

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

Database administrators and Application developers involved in
application design.


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

The "Oracle8i Designing and Tuning for Performance" guide explains
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


RELATED DOCUMENTS
-----------------

Oracle8i Designing and Tuning for Performance
Release 2 (8.1.6)
Part Number A76992-01

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50291/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50291/