如何进行等待事件enqTXrowlockcontention的分析

本篇文章为大家展示了如何进行等待事件enq TX row lock contention的分析，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

创新互联建站网站建设公司，提供成都网站设计、成都网站建设，网页设计，建网站，PHP网站建设等专业做网站服务;可快速的进行网站开发网页制作和功能扩展；专业做搜索引擎喜爱的网站，是专业的做网站团队，希望更多企业前来合作!

通常情况下，Oracle数据库的等待事件enq: TX - row lock contention会在下列三种情况下会出现。
第一种情况，是真正的业务逻辑上的行锁冲突，如一条记录被多个人同时修改。这种锁对应的请求模式是6。
第二种情况，是唯一键冲突，如主键字段相同的多条记录同时插入。这种锁对应的请求模式是4。这也是应用逻辑问题。
第三种情况，是bitmap索引的更新冲突，就是多个会话同时更新bitmap索引的同一个数据块。此时会话请求锁的对应的请求模式是4。

bitmap索引的物理结构和普通索引一样，也是 B-tree 结构。但它存储的数据记录的逻辑结构为"key_value,start_rowid,end_rowid,bitmap"。
其内容类似这样：
"‘8088’,00000000000,10000034441,1001000100001111000"
Bitmap是一个二进制，表示 START_ROWID 到 END_ROWID 的记录， 1 表示等于 key_value 即‘8088’的 ROWID 记录， 0 则表示不是这个记录。
在了解bitmap索引的结构之后，我们就能理解同时插入多条记录到拥有bitmap索引的表时，就会同时更新bitmap索引中一个块中的记录，等于某一个记录被同时更新，自然就会出现行锁等待。插入并发量越大，等待越严重。

等待事件enq: TX - row lock contention中的enq是enquence的简写。enquence是协调访问数据库资源的内部锁。
所有以“enq:”打头的等待事件都表示这个会话正在等待另一个会话持有的内部锁释放，它的名称格式是enq:enqueue_type - related_details。这里的enqueue_type是TX，related_details是row lock contention。数据库动态性能视图v$event_name提供所有以“enq:”开头的等待事件的列表。
虽然在awrrpt中看到大量enq: TX - row lock contention的等待，但这些是事后看到的信息。根据AWRRPT，我们无法只能该等待事件的请求模式是什么，是6还是4。
如果数据库一出现enq: TX - row lock contention等待，可以去看v$session和v$session_wait等视图。

在v$session和v$session_wait中，如果看到的event列是enq: TX - row lock contention的，就表示这个会话正处于行锁等待。该等待事件的请求模式可以从v$session和v$session_wait的p1列中得到。
select sid,
       chr(bitand(p1, -16777216) / 16777215) ||
       chr(bitand(p1, 16711680) / 65535) "Name",
       (bitand(p1, 65535)) "Mode"
  from v$session_wait
 where event like 'enq%';
通过这个SQL可以将p1转换为易阅读的文字。

针对这三种情况，分别进行测试：

首先，我准备一下测试表和数据。
--创建测试表和数据
SQL> create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;

Table created.

SQL> alter table T_ALL_OBJS  add constraint pk_t_all_objs primary key (OBJECT_ID);

Table altered.

SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011701,'test1');

1 row created.

SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011702,'test2');

1 row created.

SQL> commit;

Commit complete.


第一种情况，不同会话同时更新同一条记录
session1:
SQL> select sid from v$mystat where rownum<2;

       SID
----------
        46

SQL> select * from t_all_objs;                                           

OWNER                           OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST                           2013011701 test1
TEST                           2013011702 test2

SQL> update t_all_objs set object_name='test11' where object_id=2013011701;

1 row updated.

未commit

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

       SID
----------
        52

SQL> update t_all_objs set object_name='test101' where object_id=2013011701;

一直等待。。。。。。。。。   


session 3：查询
SQL> select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (46, 52);

  SID SQL_ID        BLOCKING_SESSION EVENT                          P1TEXT             P1 P2TEXT                  P2 P3TEXT             P3 WAIT_CLASS      STATE
----- ------------- ---------------- ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- --------------- ----------
   46                                SQL*Net message from client    driver id  1650815232 #bytes                   1                     0 Idle            WAITING
   52 c53uad8st2u8t               46 enq: TX - row lock contention  name|mode  1415053318 usn<<16 | slot       65556 sequence        13548 Application     WAITING


--得到sql_id值，查询出SQL信息
SQL> select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'c53uad8st2u8t';

SQL_TEXT                                                               EXECUTIONS S.ELAPSED_TIME/1000000
---------------------------------------------------------------------- ---------- ----------------------
update t_all_objs set object_name='test101' where object_id=2013011701          0             501.178747

--再查询锁信息：
SQL> select * from v$lock where sid in (46,52) order by sid, type;

ADDR             KADDR              SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009398FE58 000000009398FEB0    46 AE        100          0          4          0       3706          0
00007FABEA622FC0 00007FABEA623020    46 TM      87875          0          3          0        597          0
0000000091E37248 0000000091E372C0    46 TX      65556      13548          6          0        597          1
000000009398F820 000000009398F878    52 AE        100          0          4          0        573          0
00007FABEA622FC0 00007FABEA623020    52 TM      87875          0          3          0        543          0
000000009398FBB8 000000009398FC10    52 TX      65556      13548          0          6        543          0

6 rows selected.

查询得到未46的session最终阻塞了会话，是根源。

第二种情况，不同会话中同时插入主键字段相同的记录
session 1；
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        43

SQL> select * from t_all_objs;

OWNER                           OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST                           2013011701 test1
TEST                           2013011702 test2

SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test1');

1 row created.
未commit

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

       SID
----------
        55

SQL>  insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');

一直等待。。。。。。。

session 3：查询  
SQL> select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (43, 55);

  SID SQL_ID        BLOCKING_SESSION EVENT                          P1TEXT             P1 P2TEXT                  P2 P3TEXT             P3 WAIT_CLASS      STATE
----- ------------- ---------------- ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- --------------- ----------
   43                                SQL*Net message from client    driver id  1650815232 #bytes                   1                     0 Idle            WAITING
   55 bsddu35jkskbz               43 enq: TX - row lock contention  name|mode  1415053316 usn<<16 | slot      262149 sequence        13576 Application     WAITING

SQL> select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'bsddu35jkskbz';

SQL_TEXT                                                                                             EXECUTIONS S.ELAPSED_TIME/1000000
---------------------------------------------------------------------------------------------------- ---------- ----------------------
 insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11')                       0             213.079739

SQL> select * from v$lock where sid in (43, 55) order by sid, type;

ADDR             KADDR              SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009398F660 000000009398F6B8    43 AE        100          0          4          0        376          0
00007FABEA621F88 00007FABEA621FE8    43 TM      87875          0          3          0        266          0
0000000091E292E0 0000000091E29358    43 TX     262149      13576          6          0        266          1
000000009398F040 000000009398F098    55 AE        100          0          4          0        371          0
00007FABEA621F88 00007FABEA621FE8    55 TM      87875          0          3          0        256          0
0000000091DDB308 0000000091DDB380    55 TX     327688      13773          6          0        256          0
000000009398F900 000000009398F958    55 TX     262149      13576          0          4        256          0

7 rows selected.

最后查询得是会话43阻塞了别的会话，是根源。


第三种情况，不同会话中同时bitmap索引列值相同的记录
session 1:
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        44

SQL> select * from T_ALL_OBJS ;

OWNER                           OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST                           2013011701 test1
TEST                           2013011702 test2

SQL>  create bitmap index ind_T_ALL_OBJS on T_ALL_OBJS (owner);

Index created.

SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');

1 row created.

未commit

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

       SID
----------
        40

SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test12');

一直等待。。。。。。。。。。

session 3：查询
SQL> select sid,sql_id,blocking_session,event,p1text,p1,p2text,p2,p3text,p3,wait_class,state from v$session where sid in (44, 40);

  SID SQL_ID        BLOCKING_SESSION EVENT                          P1TEXT             P1 P2TEXT                  P2 P3TEXT             P3 WAIT_CLASS      STATE
----- ------------- ---------------- ------------------------------ ---------- ---------- --------------- ---------- ---------- ---------- --------------- ----------
   40 8s2tzhjpgx1nc               44 enq: TX - row lock contention  name|mode  1415053316 usn<<16 | slot      655390 sequence        13564 Application     WAITING
   44                                SQL*Net message from client    driver id  1650815232 #bytes                   1                     0 Idle            WAITING

SQL> select s.sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = '8s2tzhjpgx1nc';

SQL_TEXT                                                                                             EXECUTIONS S.ELAPSED_TIME/1000000
---------------------------------------------------------------------------------------------------- ---------- ----------------------
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test12')                        0              96.034408

SQL> select * from v$lock where sid in (44, 40) order by sid, type;

ADDR             KADDR              SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009398E688 000000009398E6E0    40 AE        100          0          4          0        415          0
00007FABEA622FC0 00007FABEA623020    40 TM      87875          0          3          0        110          0
000000009398FBB8 000000009398FC10    40 TX     655390      13564          0          4        110          0
0000000091E54F48 0000000091E54FC0    40 TX     589844      13794          6          0        110          0
000000009398F3C0 000000009398F418    44 AE        100          0          4          0        410          0
00007FABEA622FC0 00007FABEA623020    44 TM      87875          0          3          0        126          0
0000000091E18128 0000000091E181A0    44 TX     655390      13564          6          0        126          1

7 rows selected.

最终查询得会话44是阻塞根源。

模拟故障：
session 1：
SQL> select sid from v$mystat where rownum<2;

       SID
----------
        46

SQL> select * from t_all_objs;

OWNER                           OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST                           2013011701 test1
TEST                           2013011702 test2

--插入数据，不提交
SQL> update t_all_objs set object_name='test11' where object_id=2013011701;

1 row updated.

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

       SID
----------
        52

SQL> select * from t_all_objs;

OWNER                           OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST                           2013011701 test1
TEST                           2013011702 test2

--对同一行数据进行更新，不提交
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;

等待ing.....................

session 3:
--查询tx锁会话sid,row_wait_object#信息
select sid,sql_id,status,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention';

  SID SQL_ID        STATUS   BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
----- ------------- -------- ---------------- ------------- -------------- --------------- -------------
   52 c53uad8st2u8t ACTIVE                 46         87875              1          143649             0

--查询被锁的对象信息：
select object_name from dba_objects where object_id in (87875);

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
T_ALL_OBJS

select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from all_objects where object_name='T_ALL_OBJS';

OWNER                          OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- -------------------
SYS                            T_ALL_OBJS                          87875          87875 TABLE

--查询被锁的sesson所执行的sql
select sql_text from v$sql where sql_id in (select sql_id from v$session where sid=52);

SQL_TEXT
----------------------------------------------------------------------------------------------------
update t_all_objs set object_name='test101' where object_id=2013011701

--最后查询 V$lock：
select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request<>0;

  SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
----- -- ---------- ---------- ---------- ---------- ---------- ----------
   46 TX     524304      13916          6          0        296          1
   52 TX     524304      13916          0          6        284          0


或者通过如下SQL查询会话之间锁等待的关系：
select a.sid hold_sid, b.sid wait_sid, a.type, a.id1, a.id2, a.ctime
  from v$lock a, v$lock b
 where a.id1 = b.id1
   and a.id2 = b.id2
   and a.block = 1
   and b.block = 0;

  HOLD_SID   WAIT_SID TY        ID1        ID2      CTIME
---------- ---------- -- ---------- ---------- ----------
        46         52 TX     524304      13916       2717

或者如下
 select decode(request,0,'holder: ','waiter: ') ||
 sid session_id, id1, id2, lmode, request, type
 from v$lock
 where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0)
 order by id1, request;

SESSION_ID                                              ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
holder: 46                                           524304      13916          6          0 TX
waiter: 52                                           524304      13916          0          6 TX

 
最后可知sid为46的会话是阻塞根源。解决联系应用是不是会话为提交，或者kill掉

上述内容就是如何进行等待事件enq TX row lock contention的分析，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注创新互联行业资讯频道。