演示DML操作导致位图索引锁定位图段示例
Bitmap锁定特点:
(1)当我们操作同一个“键值行”时,会产生锁定整个键值行
(2)所以不建议频繁进行DML操作
(3)适合OLAP系统 例如 报表生成 位运算 统计分析
实验
LEO1@LEO1> select distinct sid from v$mystat; 显示当前会话id,用于区别其他会话操作
SID
----------------------
133
会话133
LEO1@LEO1> create table leo6 (id int,name varchar2(20)); 创建leo6表,2个字段
Table created.
LEO1@LEO1> create bitmap index idx_leo6 on leo6(id); 在id字段上创建位图索引
Index created.
LEO1@LEO1> insert into leo6 values(1,'leo'); 插入记录
1 row created.
LEO1@LEO1> insert into leo6 values(1,'sun'); 插入记录
1 row created.
LEO1@LEO1> commit; 提交
Commit complete.
LEO1@LEO1> select * from leo6; 显示数据
ID NAME
----------------------------
1 leo
1 sun
Id列的值相同,从Bitmap索引结构上看,这两条记录都是在同一个键值行上,我们如果操作其中的一条记录那么就是对这个键值行操作
LEO1@LEO1> update leo6 set id=2 where name='leo'; 更新一条记录
1 row updated.
会话157,重新打开一个会话
LEO1@LEO1> update leo6 set id=2 where name='sun';
这时你会惊讶的发现光标不动了,命令提示符也不能显示出来了,怎么回事,想一想你是不是在哪里碰到过这种情况,对这就是传说中的“阻塞”现象,是被第133会话给阻塞了,而我们原来碰到的是当2个会话同时修改同一条记录时会发生阻塞,而现在我们更新的是2条不同记录为什么也会发生阻塞呢,一位伟人说过“存在即合理”,那么既然发生了,就必然会有关系,只是这种关系与记录无关,而是发生在索引键值行上。
这就是Bitmap索引的一个特性:DML操作会锁定整个位图段(键值行)导致阻塞现象,这是因为oracle为了保证数据一致性和完整性,必须将索引键值行锁定,防止其他会话对其修改,归根结底这都是由于位图索引的构造原理造成的,一个键值行对应多条记录,当其中任意记录值被修改时,oracle会锁定整个键值行信息,此时另一个会话如果要修改这个键值行中的记录时,这个操作就会被之前的操作所阻塞。
解决方案:要么commit/rollback 133会话,要么终止157会话
我们从v$lock视图中看一下锁定情况
LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ------------ -- ---------- ---------- ---------- ----------
133 AE 100 0 4 0 0
157 AE 100 0 4 0 0
133 TO 65927 1 3 0 0
133 TO 5003 1 3 0 0
157 TM 73837 0 3 0 0
133 TM 73837 0 3 0 0
157 TX 589827 1307 6 0 0
133 TX 131088 1284 6 0 1
157 TX 131088 1284 0 4 0
SID:会话id
ID1+ID2:修改数据块上记录地址
LMODE:持有的锁类型
REQUEST:正在请求的锁
BLOCK:阻塞会话个数
说明:133会话正在持有一个6级TX事务锁(排他锁)并且正好阻塞另一个会话,从ID1+ID2地址上看133会话恰恰正在阻塞157会话,而157会话目前没有锁正在请求一个4级TX事务锁,只有133会话提交后才会释放6级TX锁
133会话
LEO1@LEO1> commit;
Commit complete.
LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
133 AE 100 0 4 0 0
157 AE 100 0 4 0 0
133 TO 65927 1 3 0 0
133 TO 5003 1 3 0 0
157 TM 73837 0 3 0 0
157 TX 589827 1307 6 0 0
此时133会话的6级TX锁已经被释放了,157会话阻塞解除可以继续操作并获得一个6级锁
Bitmap锁定特点:
(1)当我们操作同一个“键值行”时,会产生锁定整个键值行
(2)所以不建议频繁进行DML操作
(3)适合OLAP系统 例如 报表生成 位运算 统计分析
实验
LEO1@LEO1> select distinct sid from v$mystat; 显示当前会话id,用于区别其他会话操作
SID
----------------------
133
会话133
LEO1@LEO1> create table leo6 (id int,name varchar2(20)); 创建leo6表,2个字段
Table created.
LEO1@LEO1> create bitmap index idx_leo6 on leo6(id); 在id字段上创建位图索引
Index created.
LEO1@LEO1> insert into leo6 values(1,'leo'); 插入记录
1 row created.
LEO1@LEO1> insert into leo6 values(1,'sun'); 插入记录
1 row created.
LEO1@LEO1> commit; 提交
Commit complete.
LEO1@LEO1> select * from leo6; 显示数据
ID NAME
----------------------------
1 leo
1 sun
Id列的值相同,从Bitmap索引结构上看,这两条记录都是在同一个键值行上,我们如果操作其中的一条记录那么就是对这个键值行操作
LEO1@LEO1> update leo6 set id=2 where name='leo'; 更新一条记录
1 row updated.
会话157,重新打开一个会话
LEO1@LEO1> update leo6 set id=2 where name='sun';
这时你会惊讶的发现光标不动了,命令提示符也不能显示出来了,怎么回事,想一想你是不是在哪里碰到过这种情况,对这就是传说中的“阻塞”现象,是被第133会话给阻塞了,而我们原来碰到的是当2个会话同时修改同一条记录时会发生阻塞,而现在我们更新的是2条不同记录为什么也会发生阻塞呢,一位伟人说过“存在即合理”,那么既然发生了,就必然会有关系,只是这种关系与记录无关,而是发生在索引键值行上。
这就是Bitmap索引的一个特性:DML操作会锁定整个位图段(键值行)导致阻塞现象,这是因为oracle为了保证数据一致性和完整性,必须将索引键值行锁定,防止其他会话对其修改,归根结底这都是由于位图索引的构造原理造成的,一个键值行对应多条记录,当其中任意记录值被修改时,oracle会锁定整个键值行信息,此时另一个会话如果要修改这个键值行中的记录时,这个操作就会被之前的操作所阻塞。
解决方案:要么commit/rollback 133会话,要么终止157会话
我们从v$lock视图中看一下锁定情况
LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ------------ -- ---------- ---------- ---------- ----------
133 AE 100 0 4 0 0
157 AE 100 0 4 0 0
133 TO 65927 1 3 0 0
133 TO 5003 1 3 0 0
157 TM 73837 0 3 0 0
133 TM 73837 0 3 0 0
157 TX 589827 1307 6 0 0
133 TX 131088 1284 6 0 1
157 TX 131088 1284 0 4 0
SID:会话id
ID1+ID2:修改数据块上记录地址
LMODE:持有的锁类型
REQUEST:正在请求的锁
BLOCK:阻塞会话个数
说明:133会话正在持有一个6级TX事务锁(排他锁)并且正好阻塞另一个会话,从ID1+ID2地址上看133会话恰恰正在阻塞157会话,而157会话目前没有锁正在请求一个4级TX事务锁,只有133会话提交后才会释放6级TX锁
133会话
LEO1@LEO1> commit;
Commit complete.
LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
133 AE 100 0 4 0 0
157 AE 100 0 4 0 0
133 TO 65927 1 3 0 0
133 TO 5003 1 3 0 0
157 TM 73837 0 3 0 0
157 TX 589827 1307 6 0 0
此时133会话的6级TX锁已经被释放了,157会话阻塞解除可以继续操作并获得一个6级锁