_dexter@FAKE>insert into tb_bitmap_test select level , 'F'from dual connect by level <= 3;

3 rowscreated.

_dexter@FAKE>insert into tb_bitmap_test select level , 'M'from dual connect by level <= 2;

2 rowscreated.

_dexter@FAKE>create bitmap index tb_bitmap_test_btidx1 on tb_bitmap_test(gender) ;

Indexcreated.

_dexter@FAKE>select * from tb_bitmap_test ;

ID GE

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

1 F

2 F

3 F

1 M

2 M

session1 session_id=144:

_dexter@FAKE>update tb_bitmap_test set gender='M' where id=1 and gender='F' ;

1 rowupdated.

session2 session_id=18:

_dexter@FAKE>delete tb_bitmap_test where gender='M' and id = 1;

waiting...

session3 session_id=9 :

_dexter@FAKE>insert into tb_bitmap_test values (1,'S') ;

1 rowcreated.

锁情况：

_sys@FAKE>@lock

SID TYPE ID1 LMODE REQUEST BLOCK

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

9 TM 82847 3 0 0

9 TX 196626 6 0 0

18 TX 327710 6 0 0

18 TM 82847 3 0 0

18 TX 589854 0 4 0

144 TX 589854 6 0 1

144 TM 82847 3 0 0

7 rowsselected.

不管是gender='M' 或者 'F' ,只要涉及到这两个字段的值的dml操作都将进入等待当中（包括insert）

因为第一个session 锁住了整个bitmap segment。但是只要gender的值不涉及M或者F即可顺利执行。所以session3 顺利的执行。

2.模拟RI锁定导致阻塞的场景。

初始化环境

_dexter@FAKE>create table tun2_p (id int primary key) ;

Tablecreated.

_dexter@FAKE>create table tun2_c (pid references tun2_p(id)) ;

Tablecreated.

_dexter@FAKE>insert into tun2_c values (1) ;

insert intotun2_c values (1)

*

ERROR atline 1:

ORA-02291:integrity constraint (DEXTER.SYS_C0014143) violated - parent key not found

这里因为有引用完整性约束，子表中的内容必须与父表中的内容匹配。因为父表中没有id=1的记录，所以这里报错

主表插入

_dexter@FAKE>insert into tun2_p values (2) ;

1 rowcreated.

lock status：

_sys@FAKE>@lock2

OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK

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

TUN2_P 9 TM 82949 3 0 0

9TX 589829 6 0 0

TUN2_C 9 TM 82952 3 0 0

主表更新（子表中没有引用的记录）

_dexter@FAKE>update tun2_p set id=3 where id=2 ;

1 rowupdated.

lock status：

_sys@FAKE>@lock2

OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK

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

9TX 262144 6 0 0

TUN2_P 9 TM 82949 3 0 0

主表删除（子表中没有引用的记录）

_dexter@FAKE>delete tun2_p where id=3 ;

1 rowdeleted.

lock status:

_sys@FAKE>@lock2

OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK

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

9TX 524294 6 0 0

TUN2_P 9 TM 82949 3 0 0

如果upadte和delete操作中不包含子表引用的记录，就不会对子表加锁。

而insert相对比较复杂一点，它会级联的将子表锁定。

如果在子表引用的记录上发生更改，则会报错。例如：

_dexter@FAKE>update tun2_p set id=3 where id=1 ;

updatetun2_p set id=3 where id=1

*

ERROR atline 1:

ORA-02292:integrity constraint (DEXTER.SYS_C0014143) violated - child record found

子表插入

_dexter@FAKE>insert into tun2_c values (2) ;

1 rowcreated.

lock:

_sys@FAKE>@lock2

OBJECT_NAME SID TYPE ID1 LMODE REQUEST BLOCK

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

TUN2_P 9 TM 82949 3 0 0

9TX 589825 6 0 0

TUN2_C 9 TM 82952 3 0 0

子表更新

_dexter@FAKE>update tun2_c set pid=1 where pid=2 ;

1 rowupdated.