Oracle Deadlock When Using Bitmap Index

When using bitmap indexes in Oracle database one must watch out for deadlocks, see for example Tom Kyte‘s remark on Bitmap indexes and locking. This is by design of bitmap indexes. Below example is given in Oracle Bitmap Indexes Limitations/Restrictions.

Problem occurs on “Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production”.

create table X (a char(1));
create bitmap index Xix on X(a);
insert into X select decode(mod(rownum,2),0,'A','B') from All_Objects;

Open two session, i.e., open SQL*Plus two times.

Session 1 Session 2
insert into X values(‘A’)
insert into X values(‘B’)
insert into X values(‘B’)
deadlock insert into X values(‘A’)
Advertisements