Bitmap Indexes and Deadlocks: Deadlocks on DML's and DDL's [ID 171795.1]_insert into passage (wno,uno) values('00000002','0-优快云博客

本文探讨了在使用Bitmap索引时可能出现的死锁问题，尤其是在仅执行插入操作的情况下。通过对Bitmap索引锁定机制的深入分析，揭示了死锁发生的根本原因，并提供了诊断死锁的步骤及一个复现该问题的测试案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

修改时间:2012-3-9类型:TROUBLESHOOTING状态:PUBLISHED优先级:3

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 for earlier versions than 10g:

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.

However, for 10g and above, there is more information which describes the object:

                      ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name          process session holds waits process session holds waits
TX-0031001e-000048a3       532    2588     X             97    2890           S
TX-0098001c-0000028e        97    2890     X            391    1452           S
TX-00830022-000005f6       391    1452     X            532    2588           S
session 2588: DID 0001-0058-00001411 session 2890: DID 0001-0061-000000D3
session 2890: DID 0001-0061-000000D3 session 1452: DID 0001-003C-00002F88
session 1452: DID 0001-003C-00002F88 session 2588: DID 0001-0058-00001411
Rows waited on:
Session 2890: obj - rowid = 0000E533 - AAAOxhAAAAAAAAAAAA
(dictionary objn - 58675, file - 0, block - 0, slot - 0)
Session 1452: obj - rowid = 0000E533 - AAAOxhAAFAABzAyAAA
(dictionary objn - 58675, file - 5, block - 471090, slot - 0)
Session 2588: obj - rowid = 0000E533 - AAAOxhAAnAABGYIAAA
(dictionary objn - 58675, file - 39, block - 288264, slot - 0)

Object 58675 is INDEX "CS_IDX"

Metadata for INDEX "CS_IDX" :

....

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');