enq: TX – row lock contention的测试和案例分析

参考：http://www.killdb.com/2015/07/13/%E5%85%B3%E4%BA%8Eenq-tx-row-lock-contention%E7%9A%84%E6%B5%8B%E8%AF%95%E5%92%8C%E6%A1%88%E4%BE%8B%E5%88%86%E6%9E%90.html


关于enq: TX – row lock contention的测试和案例分析

1、主键或唯一index
session 1：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        74

SQL> create table t1_tx(id number primary key,name varchar2(20));

Table created.

SQL> insert into t1_tx values(1,'wang');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1_tx values(2,'xxoo');

1 row created.

未提交。。。。。。。。。。。。

session 2：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        45

SQL>  insert into t1_tx values(2,'xxoo');
 
hang。。。。。。。。。。。。。。。

session 3:
SQL> set lines 200
SQL> col event for a30
SQL> select inst_id,
  2         sid,
  3         chr(bitand(p1, -16777216) / 16777215) ||
  4         chr(bitand(p1, 16711680) / 65535) "Name",
  5         (bitand(p1, 65535)) "Mode",
  6         event,
  7         sql_id,
  8         blocking_session,
  9         FINAL_BLOCKING_SESSION
 10    from gv$session
 11   where event like 'enq%';

   INST_ID        SID Name       Mode EVENT                          SQL_ID        BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
         1         45 TX            4 enq: TX - row lock contention  4s99cmp3khb1b               74                     74

SQL>  
SQL> select sid,serial#,username,sql_id,status from v$session where sid=74;

       SID    SERIAL# USERNAME                       SQL_ID        STATUS
---------- ---------- ------------------------------ ------------- --------
        74         23 HR                                           INACTIVE

sql_id为空说明为非活动会话，会话等该提交或者回滚。

SQL> select * from v$Lock where block=1;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008E9E80C0 000000008E9E8138         74 TX     524298       9978          6          0        221          1

block为1,阻塞会话。
可以看出,对于表存在主键或者 unique index 时,一个会话操作主键不提交时,其他会话如果也操作相同的主键时，那么必须进行等待，而其持有的mode＝4；而阻塞blocker的持有mode＝6.

2、Bitmap INDEX
session 1：
SQL>  select * from t1_tx;

        ID NAME
---------- --------------------
         1 wang
         2 wang
         3 xxoo
         4 xxoo

SQL>
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        74

SQL> create bitmap index idx_bitmap_name on t1_tx(name);

Index created.

SQL> update t1_tx set name='tx' where id=3;  

1 row updated.

未提交。。。。。。。。。。。

session 2:
SQL> select sid from v$mystat where rownum=1;               

       SID
----------
        45

SQL> update t1_tx set name='bitmap' where id=4;

hang。。。。。。。。。。。。。

session 3：
SQL> col event for a30
SQL> select inst_id,
  2         sid,
  3         chr(bitand(p1, -16777216) / 16777215) ||
  4         chr(bitand(p1, 16711680) / 65535) "Name",
  5         (bitand(p1, 65535)) "Mode",
  6         event,
  7         sql_id,
  8         blocking_session,
  9         FINAL_BLOCKING_SESSION
 10    from gv$session
 11   where event like 'enq%';

   INST_ID        SID Name       Mode EVENT                          SQL_ID        BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
         1         45 TX            4 enq: TX - row lock contention  7wanaturqndn1               74                     74

SQL>   
SQL> set lines 200 pagesize 200
SQL> select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));
Enter value for amp: 7wanaturqndn1
old   1: select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'))
new   1: select * from table(dbms_xplan.display_cursor('7wanaturqndn1;sql_id', NULL, 'ALL'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  7wanaturqndn1, child number 0
-------------------------------------
update t1_tx set name='bitmap' where id=4
 
Plan hash value: 1842098942
 
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |              |       |       |     1 (100)|          |
|   1 |  UPDATE            | T1_TX        |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010951 |     1 |    25 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

SQL> select * from v$Lock where block=1;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000008E9E80C0 000000008E9E8138         74 TX     262171       2920          6          0        264          1

SQL>

SQL> select sid,serial#,username,sql_id,event from v$session where sid=74;

       SID    SERIAL# USERNAME                       SQL_ID        EVENT
---------- ---------- ------------------------------ ------------- ----------------------------------------------------------------
        74         23 HR                                           SQL*Net message from client

SQL> select owner,index_name,index_type from dba_indexes where table_name='T1_TX';

OWNER                          INDEX_NAME                     INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
HR                             IDX_BITMAP_NAME                BITMAP
HR                             SYS_C0012427                   NORMAL

我们可以看到，如果表上存在位图index，那么在update时,多个会话同时进行更新，必然出现tx 等待。
此时waiter申请持有的tx 锁mode＝4，而blocker持有的mode＝6,而且通过v$session试图还无法查询到blocker会话到sql_id.

3、数据位于同一block
session 3：
SQL> conn hr/hr;
Connected.
SQL> select dbms_rowid.rowid_object(rowid) obj#,
  2         dbms_rowid.rowid_relative_fno(rowid) rfile#,
  3         dbms_rowid.rowid_block_number(rowid) block#,
  4         dbms_rowid.rowid_row_number(rowid) row#
  5    from t1_tx
  6   order by 4;

      OBJ#     RFILE#     BLOCK#       ROW#
---------- ---------- ---------- ----------
     90536          4       4087          0
     90536          4       4087          1
     90536          4       4087          2
     90536          4       4087          3

SQL>     

session 1：
SQL>  select sid from v$mystat where rownum=1;

       SID
----------
        45

SQL> select * from t1_tx;

        ID NAME
---------- --------------------
         1 wang
         2 wang
         3 tx
         4 bitmap

SQL> update t1_tx set name='enmotech' where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL>  

session 2：
SQL>  select sid from v$mystat where rownum=1;

       SID
----------
        74

SQL> update t1_tx set name='xyz'where id=4;

1 row updated.

SQL> commit;

Commit complete.

SQL>

即使我分别开2个会话执行100w次，也不会出现tx锁
session 1:
SQL> declare
  2    c number;
  3  begin
  4    for i in 1 .. 1000000 loop
  5      update t1_tx set name = 'shit1' where id = 2;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>

session 2:
SQL> declare c number;
  2  begin
  3    for i in 1 .. 1000000 loop
  4      update t1_tx set name = 't-shit' where id = 3;
  5    end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.

SQL>

session 3:
SQL> set lines 200 pages 999
SQL> col event for a60
SQL> select inst_id,event,count(*) from gv$session where status='ACTIVE' and (wait_class<>'Idle' or event not like 'SQL*Net%') group by inst_id,event order by 1,3;

   INST_ID EVENT                                                          COUNT(*)
---------- ------------------------------------------------------------ ----------
         1 smon timer                                                            1
         1 Streams AQ: waiting for time management or cleanup tasks              1
         1 Streams AQ: qmn slave idle wait                                       1
         1 Space Manager: slave idle wait                                        1
         1 SQL*Net message to client                                             1
         1 VKTM Logical Idle Wait                                                1
         1 pmon timer                                                            1
         1 Streams AQ: qmn coordinator idle wait                                 1
         1 DIAG idle wait                                                        2
         1 rdbms ipc message                                                    17

10 rows selected.

SQL> /

   INST_ID EVENT                                                          COUNT(*)
---------- ------------------------------------------------------------ ----------
         1 log file parallel write                                               1
         1 smon timer                                                            1
         1 Streams AQ: waiting for time management or cleanup tasks              1
         1 Streams AQ: qmn slave idle wait                                       1
         1 buffer busy waits                                                     1
         1 Streams AQ: qmn coordinator idle wait                                 1
         1 SQL*Net message to client                                             1
         1 VKTM Logical Idle Wait                                                1
         1 pmon timer                                                            1
         1 log buffer space                                                      1
         1 Disk file operations I/O                                              1
         1 Space Manager: slave idle wait                                        1
         1 DIAG idle wait                                                        2
         1 rdbms ipc message                                                    15

14 rows selected.

SQL>  

我们可以看到,不同会话更新同一block中到不同行，不会存在等待，假设更新同一行，那么不提交到情况执行，必然存在等待,这里不再累述。


4、外键
session 1:
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        74

SQL>
SQL> create table t1 (id number ,name varchar2(20),product_id number);

Table created.

SQL> create table t2 (id number primary key,name varchar2(20));

Table created.

SQL> alter table t1  add constraint FK_PRODUCTID foreign key (PRODUCT_id)  references t2 (ID);

Table altered.

SQL> select index_name,table_name from user_indexes where table_name='T1';

no rows selected

SQL> insert into t2 values(1,'aa');

1 row created.

SQL> insert into t2 values(2,'dd');

1 row created.

SQL> insert into t2 values(3,'cc');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t2 values(5,'cc');

1 row created.

未提交。。。。。。。。。。。

session 2：
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        45

SQL>
SQL> insert into t1 values(1,'xx',5);

hang.......................子表操作会一直挂起

session 3:
SQL> l
  1  select inst_id,
  2         sid,
  3         chr(bitand(p1, -16777216) / 16777215) ||
  4         chr(bitand(p1, 16711680) / 65535) "Name",
  5         (bitand(p1, 65535)) "Mode",
  6         event,
  7         sql_id,
  8         blocking_session,
  9         FINAL_BLOCKING_SESSION
 10    from gv$session
 11*  where event like 'enq%'
SQL> /

   INST_ID        SID Name       Mode EVENT                          SQL_ID        BLOCKING_SESSION FINAL_BLOCKING_SESSION
---------- ---------- ---- ---------- ------------------------------ ------------- ---------------- ----------------------
         1         45 TX            4 enq: TX - row lock contention  btxh61ngubrv8               74                     74

SQL> select sql_text from v$sql where sql_id='btxh61ngubrv8';

SQL_TEXT
--------------------------------------------------------------------------------------------------
insert into t1 values(1,'xx',5)

SQL> select sid,serial#,username,sql_id,status from v$session where sid=74;

       SID    SERIAL# USERNAME                       SQL_ID        STATUS
---------- ---------- ------------------------------ ------------- --------
        74         23 HR                                           INACTIVE

实际上我们可以发现，无论子表有没有主键约束，都会存在这种情况，只有主表操作不提交.


1. 其原因一般有如下几种:
1) 表上存在主键或唯一性约束,多个会话操作同一条记录
2) 表存在主外键读情况,主表不提交,子表那么必须进行等待.
3) 表上存在位图Index,这跟uniqeue index中存在重复值是一样的道理,其中一个会话操作，其他会话必须等待.
4) 表进行自我外键关联,前面的事务不提交，那么会导致后面的会话一直等待.

2. 对于网上说的enq: TX – row lock contention也有可能是在等待index block分裂的情况,从理论上来讲,如果是在等待index block分裂，那么应该还伴有enq: TX – index contention等待事件产生.

3. 对于enq: TX – row lock contention,通过v$session视图查询时，等待会话带lock mode通常为4，而blocker会话带lock mode通常为6，并且一般查询blocker会话的sql_id都为空。这是正常现象，v$session显示是当前状态,而非历史数据.