row lock waits的两种情况

 

以下测试基于版本9207。

情形一：并发更新非唯一索引的索引字段引起的row lock waits在表上，不在索引上

SQL> create table test as select object_id,object_name from dba_objects;

Table created.

SQL> create index test_idx on test(object_id);

Index created.

SQL> update test set object_id=12345 where object_id=99998;

1 row updated.

SQL> select statistic_name,value from v$segment_statistics where owner=’TEST’ and object_name=’TEST’ and statistic_name=’row lock waits’;

STATISTIC_NAME                                                        VALUE
—————————————————————- ———-
row lock waits                                                                     0

SQL> select statistic_name,value from v$segment_statistics where owner=’TEST’ and object_name=’TEST_IDX’ and statistic_name=’row lock waits’;

STATISTIC_NAME                                                        VALUE
—————————————————————- ———-
row lock waits                                                                     0

另一session;

SQL> update test set object_id=12345 where object_id=99998;   hang住，此时除了有enqueue等待外，还有表上的row lock waits 等待，这个等待发生在表上，而不是索引上。

第三个session:

SQL> select statistic_name,value from v$segment_statistics where owner=’TEST’ and object_name=’TEST’;

STATISTIC_NAME                                                        VALUE
—————————————————————- ———-
logical reads                                                         61424
buffer busy waits                                                         0
db block changes                                                      29088
physical reads                                                          337
physical writes                                                         337
physical reads direct                                                     0
physical writes direct                                                  337
global cache cr blocks served                                             0
global cache current blocks served                                        0
ITL waits                                                                 0
row lock waits                                                            1

SQL> select statistic_name,value from v$segment_statistics where owner=’TEST’ and object_name=’TEST_IDX’;

STATISTIC_NAME                                                        VALUE
—————————————————————- ———-
logical reads                                                            96
buffer busy waits                                                         0
db block changes                                                         64
physical reads                                                            2
physical writes                                                         129
physical reads direct                                                     0
physical writes direct                                                  129
global cache cr blocks served                                             0
global cache current blocks served                                        0
ITL waits                                                                 0
row lock waits                                                            0

情形二：索引块分裂引起的row lock waits 等待在索引上，而不在表上

session 1:

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_block_size                        integer     4096

SQL> create table test(x number,y char(20),z date,q varchar2(2000));

Table created.

SQL> create index test_idx on test(q,z);

Index created.

SQL>  begin
2   for i in 1..5000
3   loop
4   insert into test values(1162,’1060000abcdefg’, sysdate, rpad(‘x’,1000,’x'));
5   end loop;
6   end;
7  /

PL/SQL procedure successfully completed.

session 2:

与session 并发插入

SQL>  begin
2   for i in 1..5000
3   loop
4   insert into test values(1162,’1060000abcdefg’, sysdate, rpad(‘x’,1000,’x'));
5   end loop;
6   end;
7  /

PL/SQL procedure successfully completed.

session 3:

两个session 运行插入之前:

OBJECT_NAME                    STATISTIC_NAME                                                        VALUE
—————————— ———————————————— ———-
TEST_IDX                       row lock waits                                                            0
TEST                           row lock waits                                                            0

运行插入过程中：

SQL> /

OBJECT_NAME                    STATISTIC_NAME                                                        VALUE
—————————— ———————————————— ———-
TEST_IDX                       row lock waits                                                            7
TEST                           row lock waits                                                            0

SQL> /

OBJECT_NAME                    STATISTIC_NAME                                                        VALUE
—————————— ——————————————– ———-
TEST_IDX                       row lock waits                                                           20
TEST                           row lock waits                                                            0

SQL> /

OBJECT_NAME                    STATISTIC_NAME                                                     VALUE
—————————— ——————————————– ———-
TEST_IDX                    row lock waits                                                        481
TEST                           row lock waits                                                            0

SQL> /

OBJECT_NAME                    STATISTIC_NAME                                                      VALUE
—————————— ——————————————— ———-
TEST_IDX                  row lock waits                                                          906
TEST                           row lock waits                                                            0

可以看到此时的row lock waits等待有多达906个，这个row lock waits统计经常伴随着enq: TX – index contention等待事件的发生。

基于以上两种情况，所以我们有时候看到row lock waits在表上，有时候在索引上，所以要具体原因具体分析，然后才能对症解决。

 

不错，以实际演示问题！

 

原文地址：  http://www.dbasky.com/oracle/update_index_row_lock_waits.html