enq: TX - row lock contention等待事件处理

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5762897.html

今天早晨发现数据库出现”enq: TX - row lock contention“等待，记录下处理过程：
SQL*Plus: Release 11.2.0.2.0 Production
  SID EVENT                                                                   P1                  P2               SECONDS_IN_WAIT
----- -------------------------------------------------- ---------- ---------- ----------------------------------------
 7879 enq: TX - row lock contention                      1415053318   48431124 3344740
 8167 enq: TX - row lock contention                      1415053318   48431124 3749563

通常，产生enq: TX - row lock contention事件的原因有以下几种可能：
    * 不同的session更新或删除同一条记录；
    * 唯一索引有重复索引；
    * 位图索引同时被更新或同时并发的向位图索引字段上插入相同字段值；
    * 并发的对同一个数据块上的数据进行update操作；
    * 等待索引块完成分裂；

去看看具体等待什么吧
SQL> select sid,event,SQL_ADDRESS,SQL_ID from v$session
  2  where sid in (7879,8167);
  SID EVENT                                              SQL_ADDRESS      SQL_ID
----- -------------------------------------------------- ---------------- -------------
 7879 enq: TX - row lock contention                      C000000CCE8A76F0 08qruv04r2v6d
 8167 enq: TX - row lock contention                      C000000CCE8A76F0 08qruv04r2v6d
看看sql语句是什么

SQL> select SQL_ID,SQL_TEXT from v$sql
  2*     where sql_id = '08qruv04r2v6d'
SQL> /

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
08qruv04r2v6d UPDATE "XI_AF_LIC_AGG_LOG" SET "START_TIME" = :1,"STATUS" = :2,"LAST_UPDATE" = :3 WHERE "AGG_LEVEL" = :4 AND "PROCESS_ID" = :5

难道是update操作同一个表造成的？

SQL> select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%';

EVENT                                                SID         P1         P2         P3
-------------------------------------------------- ----- ---------- ---------- ----------
pmon timer                                           315        300          0          0
VKTM Logical Idle Wait                               629          0          0          0
DIAG idle wait                                       943          5          1       1000
Space Manager: slave idle wait                       975          0          0          0
DIAG idle wait                                      1257          5          1       1000
Space Manager: slave idle wait                      1913          2          0          0
log file parallel write                             2827          2          2          2
smon timer                                          3141        300          0          0
db file sequential read                             3939         19      52910          1
Streams AQ: qmn coordinator idle wait               4554          0          0          0
Streams AQ: waiting for time management or cleanup  4711          0          0          0
Streams AQ: qmn slave idle wait                     5025          1          0          0
enq: TX - row lock contention                       7879 1415053318   48431124     851369
enq: TX - row lock contention                       8167 1415053318   48431124     851369

14 rows selected.
看看发生等待的对象
SQL> select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# 
  2  from v$session where event='enq: TX - row lock contention';

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        68681              8          674739             0
        68681              8          674739             0
SQL> select object_name from dba_objects where object_id in (68681);

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
XI_AF_LIC_AGG_LOG
XI_AF_LIC_AGG_LOG
但是，再来看看锁的情况  
SQL> select * from v$lock where type='TX' and request>0;

ADDR             KADDR              SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
C000000D32D41E40 C000000D32D41E98  8167 TX   48431124     851369          0          6    3750894          0
C000000D32D557D0 C000000D32D55828  7879 TX   48431124     851369          0          6    3746998          0

SQL> 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
----- -- ---------- ---------- ---------- ---------- ---------- ----------
 8167 TX   48431124     851369          0          6    3747891          0
 7879 TX   48431124     851369          0          6    3743995          0
 5364 TX   48431124     851369          6          0    3747893          1

上面的查询可以看出，5364会话以锁模式6的方式持有了(48431124  851369)。然后8167会话和7879会话申请模式6的锁，发现已经被5364会话持有，
只好等待5364会话的释放，表现为等待事件：enq: TX - row lock contention
看看5364会话在做什么

SQL> select sid,event,SQL_ADDRESS,SQL_ID from v$session
  2     where sid in (5364);

  SID EVENT                                              SQL_ADDRESS      SQL_ID
----- -------------------------------------------------- ---------------- -------------
 5364 SQL*Net message from client   

再看下会话5364的状态：居然在等待客户端消息
SQL> select sid,USER#,USERNAME,COMMAND,LOCKWAIT,STATUS,SERVER,SCHEMANAME,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM,TYPE,EVENT,STATE,LOGON_TIME
  2  from v$session
  3   where sid='5364';

  SID      USER# USERNAME                          COMMAND LOCKWAIT         STATUS   SERVER    SCHEMANAME                     OSUSER                         PROCESS
----- ---------- ------------------------------ ---------- ---------------- -------- --------- ------------------------------ ------------------------------ ------------------------
MACHINE                                                          TERMINAL                       PROGRAM                                          TYPE
---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ----------
EVENT                                              STATE               LOGON_TIME
-------------------------------------------------- ------------------- ---------------
 5364         38 SAPSR3DB                                0                  INACTIVE DEDICATED SAPSR3DB                       pp0adm                         1234
apppp06                                                          unknown                        JDBC Thin Client                                 USER
SQL*Net message from client                        WAITING             20-JUL-13

继续
SQL> select sid,serial# from v$session  where sid=5364;
  SID    SERIAL#
----- ----------
 5364      24841
SQL> select sid,serial# from v$session  where sid=5364;
  SID    SERIAL#
----- ----------
 5364      24841

SQL> select paddr from v$session where sid= 5364;
PADDR
----------------
C000000D2194FCD0
SQL> select spid from v$process where addr='C000000D2194FCD0';
SPID
------------------------
19669

SQL> ! ps -ef | grep 19669
  orapp0 19669     1  0  Jul 20  ?         0:03 oraclePP0 (DESCRIPTION=(LOCAL=NO)(SDU=32767))

和客户应用管理人员确认后，执行alter system kill session '5364,24841';命令没能杀掉该会话，只好在层面通过kill -9 19669杀掉进程，结束会话，问题得以处理。

--The end