Bitmap indexes: ORA-00060: deadlock detected while waiting for resource

Bitmap indexes can generate frequent deadlock on insert, because of internal locking procedures that involves all the rows containing one index key.

SQL> desc my_table_btm_index;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER

 STATUS                                             VARCHAR2(1)

SQL> select i.table_name,

  2  i.index_name,

  3  i.index_type,

  4  c.column_name

  5  from user_indexes i

  6  join user_ind_columns c

  7  on i.index_name=c.index_name

  8  where i.index_name='IDX_BTM_MY_TABLE';

TABLE_NAME                     INDEX_NAME

------------------------------ ------------------------------

INDEX_TYPE

---------------------------

COLUMN_NAME

--------------------------------------------------------------------------------

MY_TABLE_BTM_INDEX             IDX_BTM_MY_TABLE

BITMAP

STATUS

Session 1:

SQL> insert into MY_TABLE_BTM_INDEX values (1,'a');

1 row created.

Session 2:

SQL> insert into MY_TABLE_BTM_INDEX values (1,'b');

1 row created.

Session 1:

SQL> insert into MY_TABLE_BTM_INDEX values (2,'b');

Session 2:

SQL> insert into MY_TABLE_BTM_INDEX values (2,'a');

1 row created.

Session 1:

insert into MY_TABLE_BTM_INDEX values (2,'b')

            *

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource