关于Oracle等待事件enq: TX - row lock contention的分析过程

客户突然报障说系统比较慢，登录Oracle数据库服务器查看系统资源非常忙,登录数据库检查等待事件，发现大量的[size=12.5pt]enq: TX - row lock contention[size=12.5pt]该等待事件[size=12.5pt]，现在将整个操作过程记录下来

1、查询当前系统的等待事件

SQL> select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%'and event not like 'rdbms%';
enq: TX - row lock contention 633 1415053318 9437214 2583
class slave wait 635 0 0 0
enq: TX - row lock contention 681 1415053318 8716293 21746
log file switch (archiving needed) 687 0 0 0
enq: TX - row lock contention 692 1415053318 9437214 2583
enq: TX - row lock contention 693 1415053318 8716293 21746
enq: TX - row lock contention 713 1415053318 8716293 21746
enq: TX - row lock contention 774 1415053318 9437214 2583
enq: TX - row lock contention 775 1415053318 9437214 2583
enq: TX - row lock contention 778 1415053318 8716293 21746
enq: TX - row lock contention 784 1415053318 9437214 2583
db file sequential read 787 22 1906315 1
enq: TX - row lock contention 789 1415053318 9437214 2583
enq: TX - row lock contention 804 1415053318 9437214 2583
enq: TX - row lock contention 806 1415053318 9437214 2583
enq: TX - row lock contention 808 1415053318 8716293 21746
enq: TX - row lock contention 810 1415053318 8716293 21746
enq: TX - row lock contention 813 1415053318 8716293 21746
enq: TX - row lock contention 814 1415053318 8716293 21746
enq: TX - row lock contention 834 1415053318 8716293 21746
db file sequential read 842 37 298951 1
enq: TX - row lock contention 863 1415053318 9437214 2583
Streams AQ: qmn slave idle wait 878 0 0 0
enq: TX - row lock contention 894 1415053318 9437214 2583
enq: TX - row lock contention 900 1415053318 9437214 2583
enq: TX - row lock contention 901 1415053318 9371658 8423
enq: TX - row lock contention 916 1415053318 9437214 2583
Streams AQ: qmn slave idle wait 941 0 0 0
db file sequential read 990 25 526635 1
enq: TX - row lock contention 992 1415053318 9437214 2583
db file scattered read 1009 35 226809 16
enq: TX - row lock contention 1017 1415053318 8716293 21746
enq: TX - row lock contention 1025 1415053318 8716293 21746
enq: TX - row lock contention 1029 1415053318 9437214 2583
enq: TX - row lock contention 1031 1415053318 9437214 2583

50 rows selected.

也可以使用以下SQL语句查询到相应的SID与seq#
select sid,seq#,EVENT
from v$session_wait t
where t.EVENT not in
('SQL*Net message from client', 'rdbms ipc message',
'SQL*Net more data from client', 'SQL*Net more data to client',
'SQL*Net message to client', 'jobq slave wait');

上面的enq是一种保护共享资源的锁定机制，一个排队机制，先进先出(FIFO)
发生TX锁的原因一般有几个
1.不同的session更新或删除同一个记录。
2.唯一索引有重复索引
3.位图索引多次更新
4.同时对同一个数据块更新
5.等待索引块分裂

关于P1, P2, P3的说明：
P1—与等待相关的数据文件的全部文件数量。
P2—P1中的数据文件的块数量。
P3—描述等待产生原因的代码

2、下面我们通过enq: TX - row lock contention来看看这些session都在等什么

SQL> select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention';
59167 16 23456 9
58446 21 2517 6
59167 14 91795 6
58446 20 1474020 8
58446 20 1473915 2
59167 16 251983 9
59167 14 44881 13
58446 18 1456189 0
59167 14 58689 2
59167 14 17558 15
59167 15 2375 17
59167 15 392022 2
58446 22 1479462 12
58446 21 2474 11
58446 21 1469334 9
58446 21 1469334 8
58446 21 2517 6
59167 16 102211 13
59167 14 89182 7
59167 14 89193 2
59162 17 771660 13
59162 16 757033 11
59167 14 371189 4
59167 14 30063 6
58446 21 1469334 9
58904 22 1498822 1
59167 14 495702 13
59167 14 89164 11
58446 21 1469334 8

29 rows selected.

关于ROW_WAIT_OBJ#重点分布在：59162、58446、59167这几个上面。

3、通过上面sql查找出来的对象编号找到对应的对象名称

SQL> select object_name from dba_objects where object_id in (59167);
BBB221

SQL> select object_name from dba_objects where object_id in (58446);
BBB221

SQL> select object_name from dba_objects where object_id in (59162);
BBB221

4、通过对象名称找出该对象的对应属性，对象属性为TABLE

SQL> select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID, OBJECT_TYPE from all_objects where object_name='BB221';
----------- ----------- ------------ --------- -------
TESTUSER BBB221 58446 58446 TABLE

5、通过正在等待的SID查看它们都在执行什么操作

SQL> select sid,sql_text from v$session a,v$sql b where sid in(806,808,804,810,813,814) and (b.sql_id=a.sql_id or b.sql_id=a.prev_sql_id);

806
update BBB221 set BBB193='1' where bba012=:1 and bbb191=:2

806
update BBB221 set BBB193='1' where bba012=:1 and bbb191=:2

806
update BBB221 set BBB193='1' where bba012=:1 and bbb191=:2
......
......
从上面的结果可以看出，TESTUSER用户下的几个session同时在执行一条相同的sql语句，对应的对象则是BB221这个表， 所以发生了锁，从而产生等待。

6、下面我们去找一下对应sid产生的锁

SQL> select SID,TY,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request<>0;

659 9437214 2583 0 6 70 0
715 5963811 597 0 6 128 0
871 9437214 2583 0 6 25 0
1047 5963811 597 0 6 109 0
675 5963811 597 0 6 106 0
810 9437214 2583 0 6 94 0
677 5963811 597 0 6 73 0
894 9371658 8423 6 0 1467 1
687 9437214 2583 6 0 1671 1

由此可以查看，BLOCK=1的sid是该等待事件的根源，其他session则等待该锁被释放。

[color=#3366ff]解决方法：
1：通过v$session找到BLOCK=1的用户，告知用户提交事务
2：通过sid找到pid，kill掉该进程

select sid, SERIAL# from v$session where sid in ('1092')
alter system kill session '894,22';
alter system kill session '687,52163';