本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
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来分析问题，找到根本原因。
Related posts:
