索引的row lock wait

一个session 无法产生索引的row lock wait
 Insert 也不会产生表的row lock wait，因为表块不会分裂

在索引块分裂的同时往索引块里插入数据，会发生index的row lock wait

比如session a的第10个块正在进行块分裂的时候， session b也要分裂这个块
（假如都是5:5 分，则50% 后的下一行会等待。我自己的想法）
就会发生row lock wait

 创建测试表
13:58:24 SQL> create table jiebin_test1(id int,id2 char(18));
 
Table created
 
Executed in 0.062 seconds
 
13:59:00 SQL> create index ind_jiebin_test1 on jiebin_test1(id);
 
Index created
 
Executed in 0.094 seconds

Session A
14:03:51 SQL>
begin
for i in 1 ..1000000 loop
insert into jiebin_test1 values(i,'AAAAAAAAAAAAAAAA');
end loop;
end;
/
 
Session B
begin
for i in 1 ..1000000 loop
insert into jiebin_test1 values(i,'AAAAAAAAAAAAAAAA');
end loop;
end;
/

Session c中查看row lock wait

14:02:11 SQL>
select object_name, STATISTIC_NAME, value
  from v$segment_statistics
 where object_name in ('JIEBIN_TEST1', 'IND_JIEBIN_TEST1')
   and STATISTIC_NAME = 'row lock waits'
 order by 1;
OBJECT_NAME                    STATISTIC_NAME                                                        VALUE
------------------------------ ---------------------------------------------------------------- ----------
IND_JIEBIN_TEST1               row lock waits                                                            0
JIEBIN_TEST1                   row lock waits                                                            0

2 rows selected
 
Executed in 0.313 seconds
 
14:05:00 SQL> /
 
OBJECT_NAME                    STATISTIC_NAME                                                        VALUE
------------------------------ ---------------------------------------------------------------- ----------
IND_JIEBIN_TEST1               row lock waits                                                            1
JIEBIN_TEST1                   row lock waits                                                            0
 
2 rows selected
 
Executed in 0.328 seconds
 
14:05:05 SQL> /
 
OBJECT_NAME                    STATISTIC_NAME                                                        VALUE
------------------------------ ---------------------------------------------------------------- ----------
IND_JIEBIN_TEST1               row lock waits                                                            2
JIEBIN_TEST1                   row lock waits                                                            0
 
2 rows selected
 
Executed in 0.296 seconds
 
14:05:06 SQL> /
 
OBJECT_NAME                    STATISTIC_NAME                                                        VALUE
------------------------------ ---------------------------------------------------------------- ----------
IND_JIEBIN_TEST1               row lock waits                                                            4
JIEBIN_TEST1                   row lock waits                                                            0
 
2 rows selected
 
Executed in 0.297 seconds
 
14:05:15 SQL> /
 
OBJECT_NAME                    STATISTIC_NAME                                                        VALUE
------------------------------ ---------------------------------------------------------------- ----------
IND_JIEBIN_TEST1               row lock waits                                                            7
JIEBIN_TEST1                   row lock waits                                                            0
 
2 rows selected
 
Executed in 0.312 seconds
 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8135069/viewspace-676182/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8135069/viewspace-676182/