转载

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

本站文章除注明转载外，均为本站原创：转载自 love wife & love life —Roger 的Oracle技术博客

本文链接地址: 关于enq: TX – row lock contention的测试和案例分析

1、主键或唯一index

---session 1 SQL> select sid from v$mystat where rownum=1;  SID ----------  130 SQL> create table t1_tx(id number primary key,name varchar2(20)); Table created. SQL> insert into t1_tx values(1,'roger'); 1 row created. SQL> commit; Commit complete. SQL> insert into t1_tx values(2,'xxoo'); 1 row created. ---session 2 SQL> conn roger/roger Connected. SQL> select sid from v$mystat where rownum=1;  SID ----------   69 SQL> insert into t1_tx values(2,'xxoo'); ---一直处于等待状态 ---session 3 SQL> select sid,   2   chr(bitand(p1, -16777216) / 16777215) ||   3   chr(bitand(p1, 16711680) / 65535) "Name",   4   (bitand(p1, 65535)) "Mode",event,sql_id,FINAL_BLOCKING_SESSION   5     from v$session   6   where event like 'enq%';  SID Name  Mode EVENT        SQL_ID  FINAL_BLOCKING_SESSION ---------- ---- ----- ------------------------------ ------------- ----------------------   69 TX 4 enq: TX - row lock contention  b775wqk86zc6k        130 SQL> select sid,serial#,username,sql_id from v$session where sid=130;  SID    SERIAL# USERNAME     SQL_ID ---------- ---------- ------------------------------ -------------  130  185 ROGER SQL> select * from v$Lock where block=1; ADDR     KADDR     SID TY  ID1  ID2      LMODE    REQUEST      CTIME      BLOCK -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 2A4F2AAC 2A4F2AEC  130 TX     196612  895    6    0  736    1

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

2、Bitmap INDEX

--session 1 SQL> select * from t1_tx;  ID NAME ---------- --------------------   1 roger   2 roger   3 aa   4 aa SQL> create bitmap index idx_bitmap_name on t1_tx(name); Index created. SQL> select sid from v$mystat where rownum=1;        SID ----------        130        SQL> update t1_tx set name='tx' where id=3;   1 row updated.           SQL> ---session 2 SQL> select sid from v$mystat where rownum=1;                SID ----------  69          SQL> update t1_tx set name='bitmap' where id=4;  ---一直处于等待状态 ---session 3 SQL>  select sid,   2  chr(bitand(p1, -16777216) / 16777215) ||   3  chr(bitand(p1, 16711680) / 65535) "Name",   4  (bitand(p1, 65535)) "Mode",event,sql_id,FINAL_BLOCKING_SESSION   5    from v$session   6   where event like 'enq%';                       SID Name       Mode EVENT      SQL_ID FINAL_BLOCKING_SESSION ---------- ---- ---------- ------------------------------- ------------- ----------------------  69 TX     4 enq: TX - row lock contention   7wanaturqndn1      130 SQL> SQL> set lines 200 pagesize 200 SQL> select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL')); Enter value for sql_id: 7wanaturqndn1 old   1: select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL')) new   1: select * from table(dbms_xplan.display_cursor('7wanaturqndn1', 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 -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 2A4F3100 2A4F3140 130 TX     262144 563   6   0 209   1 SQL> l   1* select * from v$Lock where block=1 SQL> / ADDR     KADDR    SID TY ID1 ID2      LMODE    REQUEST      CTIME      BLOCK -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 2A4F3100 2A4F3140 130 TX     262144 563   6   0 215   1 SQL> select sid,serial#,username,sql_id,event from v$session where sid=130;        SID    SERIAL# USERNAME         SQL_ID EVENT ---------- ---------- ------------------------------ ------------- -----------------------------------------------------------------        130 185 ROGER     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 ------------------------------ ------------------------------ --------------------------- ROGER     IDX_BITMAP_NAME  BITMAP ROGER     SYS_C0010951     NORMAL

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

3、数据位于同一block

---session 1 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 order by 4;        OBJ#     RFILE#     BLOCK#       ROW# ---------- ---------- ---------- ----------      74762          4      30141          0      74762          4      30141          1      74762          4      30141          2      74762          4      30141          3  SQL> update t1_tx set name='enmotech' where id=2;   1 row updated.  SQL> commit;  Commit complete.  ---session 2  SQL>  update t1_tx set name='zhenxu' 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.  Elapsed: 00:00:26.58 SQL>   ---session 2 SQL>  declare   2     c number;   3   begin   4     for i in 1 .. 1000000 loop   5       update  t1_tx set name='t-shit' where id=3;   6     end loop;   7   end;   8   /  PL/SQL procedure successfully completed.  --session 3 SQL> select inst_id,event,count(1) from gv$session where wait_class#<>6 group by inst_id,event order by 1,3;      INST_ID EVENT                                                               COUNT(1) ---------- ----------------------------------------------------------------- ----------          1 asynch descriptor resize                                                   1          1 Log archive I/O                                                            1          1 buffer busy waits                                                          2

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

4、外键

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> SQL> select index_name,table_name from user_indexes where table_name='T1';  no rows selected  SQL> 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.  SQL>   ---session 2 SQL> insert into t1 values(1,'xx',5);   --子表操作会一直挂起

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

实际上还有一种更特殊到情况，也会出现，当然原理上来讲，也上主外键的问题，如下测试：

---session 1 SQL> conn roger/roger Connected. SQL> create table t3_ref (id number primary key,name varchar2(20),obj_id NUMBER);  Table created.  SQL> alter table  t3_ref  add constraint fk_id foreign key (obj_id)  references t3_ref (id);  Table altered.  SQL> insert into t3_ref values(1,'roger',1);  1 row created.  SQL> insert into t3_ref values(2,'roger',1);  1 row created.  ---session 2  SQL> conn roger/roger Connected. SQL> insert into t3_ref values(3,'roger',2); ---一直处于等待  ---session 3 SQL> l   1   select sid,   2         chr(bitand(p1, -16777216) / 16777215) ||   3         chr(bitand(p1, 16711680) / 65535) "Name",   4         (bitand(p1, 65535)) "Mode",event,sql_id,blocking_session,FINAL_BLOCKING_SESSION   5           from v$session   6*  where event like 'enq%' SQL> /         SID Name       Mode EVENT                            SQL_ID        BLOCKING_SESSION FINAL_BLOCKING_SESSION ---------- ---- ---------- -------------------------------- ------------- ---------------- ----------------------        199 TX            4 enq: TX - row lock contention    8cj5awv9djrby              139                    139

所以，对于enq: TX – row lock contention 我们可以进行如下简单总结：

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显示是当前状态, 而非历史数据.

如下是某客户的真实例子的分析过程，如下：

SQL> select inst_id,event,count(1) from gv$session where wait_class#<>6 group by inst_id,event order by 1,3;     INST_ID EVENT         COUNT(1) ---------- ----------------------------------------------------------------- ----------    1 SQL*Net message to client        1    1 SQL*Net message from dblink            2    1 db file sequential read          4    1 library cache: mutex X           4    1 enq: TX - row lock contention         18    2 library cache: mutex X           1    2 db file sequential read          1     7 rows selected.      SQL>  select sid,  chr(bitand(p1, -16777216) / 16777215) ||   2    3   chr(bitand(p1, 16711680) / 65535) "Name",   4   (bitand(p1, 65535)) "Mode",event,sql_id,FINAL_BLOCKING_SESSION   5     from v$session   6   where event like 'enq%';                            SID Name Mode EVENT       SQL_ID  FINAL_BLOCKING_SESSION ---------- ---- ---------- ----------------------------------- ------------- ----------------------  207 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       1008 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       1168 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       1451 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       5286       1652 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       5286       2129 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       2207 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       5286       2723 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       5286       3095 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       4807 TX      6 enq: TX - row lock contention djbvcr351s0mh       1690       5015 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       5047 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       5213 TX      6 enq: TX - row lock contention djbvcr351s0mh       1690       5372 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       5286       5374 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       5732 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       6721 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       7608 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810       7609 TX      4 enq: TX - row lock contention 4fpb7rfm3fb3b       2810            19 rows selected.

这里通过dump 这几个process，然后过滤insert into 并没有发现针对party表的insert 操作。
于是尝试换一种思路，通过logminer 来分析进程的操作进程，如下：

SQL> select member from v$logfile where group#=1; MEMBER -------------------------------------------------- /crm/oradata01/redo01a.log /crm/oradata02/redo01b.log SQL>  select sid, username,   2   chr(bitand(p1, -16777216) / 16777215) ||   3   chr(bitand(p1, 16711680) / 65535) "Name",   4   (bitand(p1, 65535)) "Mode",event,sql_id,blocking_session,FINAL_BLOCKING_SESSION   5     from v$session   6   where event like 'enq%';  SID USERNAME   Name Mode EVENT    SQL_ID  BLOCKING_SESSION FINAL_BLOCKING_SESSION ---------- ---------- ---- ---------- -------------------------------- ------------- ---------------- ----------------------  123 CRM_APP    TX      4 enq: TX - row lock contention    4fpb7rfm3fb3b       2802       2802  689 CRM_APP    TX      4 enq: TX - row lock contention    4fpb7rfm3fb3b       2802       2802  934 CRM_APP    TX      4 enq: TX - row lock contention    4fpb7rfm3fb3b       3128       3128       4128 CRM_APP    TX      4 enq: TX - row lock contention    4fpb7rfm3fb3b       2802       2802       4449 CRM_APP    TX      4 enq: TX - row lock contention    4fpb7rfm3fb3b       2802       2802       6324 CRM_APP    TX      4 enq: TX - row lock contention    4fpb7rfm3fb3b       2802       2802 6 rows selected.

当前6个waiter 会话的事务信息如下：

SQL> SELECT t.xidusn, t.xidslot, t.xidsqn, t.start_time, t.start_scn   2  FROM v$transaction t JOIN v$session s ON t.addr = s.taddr   3  WHERE s.sid  in (123,689,934,4128,4449,6324)   4  /     XIDUSN    XIDSLOT     XIDSQN START_TIME      START_SCN ---------- ---------- ---------- -------------------- ----------  743   28      61461 07/11/15 22:09:12    1.4484E+13  828   26      61559 07/11/15 22:07:22    1.4484E+13  918    5      57068 07/11/15 22:08:12    1.4484E+13  820    1      64176 07/11/15 22:07:11    1.4484E+13       1060   16      54417 07/11/15 22:08:12    1.4484E+13  816    3      62830 07/11/15 22:07:11    1.4484E+13 6 rows selected.

当前2个blocker会话的事务信息如下：

SQL> SELECT t.xidusn, t.xidslot, t.xidsqn, t.start_time, t.start_scn   2  FROM v$transaction t JOIN v$session s ON t.addr = s.taddr   3  WHERE s.sid  in (2802,3128)   4  /      XIDUSN    XIDSLOT     XIDSQN START_TIME            START_SCN ---------- ---------- ---------- -------------------- ----------        949         31      79938 07/11/15 22:06:22    1.4484E+13       1061         20      57965 07/11/15 22:06:11    1.4484E+13

利用logminer 来分析waiter和blocker 会话的操作信息：

SQL> EXECUTE dbms_logmnr.add_logfile(logfilename=>'/crm/oradata01/redo01a.log');  PL/SQL procedure successfully completed.  SQL> EXECUTE dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);  PL/SQL procedure successfully completed.  SQL> create table tmp_logmnr_contents as select * from v$logmnr_contents;  Table created.  SQL> EXECUTE dbms_logmnr.end_logmnr ;  PL/SQL procedure successfully completed.

最后查询发现blocker和waiter执行的SQL都类似，因此这就很容易说明问题了. 由于logminer抓取的SQL涉及到客户信息，因此这里不便贴出来。这里只是给大家提供一种思路，对于TX锁的分析，也是可以利用logminer来做的。

最后分析发现，本质上来讲，就是因为前后会话操作相同的数据导致，而表上有存在主键，这必然导致出现TX锁等待。

PS：或许有人会说，为什么不直接查v$试图抓取sql的绑定变量，实际上我这里已经查过，没有查到，而且通过dump processstate也没有发现，因此才想到利用logminer来分析问题，找到根本原因。

正文到此结束