enq: TX - row lock contention

enq: TX - row lock contention

这个是以前公司的问题
前同事求救，明天上线，今天突然发现入库程序会hang住
重启应用后好一会之后又会hang住

通过show_lock脚本检查了下发现又行锁，并且都锁住了相同的表
进一步跟踪等待事件，两个session分别是
enq: TX - row lock contention
SQL*Net message from client

很明显问题出在enq: TX - row lock contention
这个等待事件出现的原因主要有三种：

Unique key contention
Foreign Key contention
Bitmap index contention

检查表中没有外键，有主键和位图索引
为了验证，在本地做了个模拟

session1：
SQL> conn an/an
Connected.

SQL> create table aa(id int);
Table created.

SQL> create unique index ind_aa on aa(id);
Index created.

SQL> insert into aa select 1 from dual;
1 row created.

session2：
SQL> conn an/an
Connected.
SQL> insert into aa select 1 from dual;

session3:
查询锁
SQL> set wrap off
SQL> set linesize 158
SQL> col username for a15
SQL> col machine for a10
SQL> select s.sid,
  2         s.serial#,
  3         s.username,
  4         machine,
  5         a.object_name,
  6         decode(locked_mode,
  7                0,
  8                'None',
  9                1,
 10                'Null',
 11                2,
 12                'Row share',
 13                3,
 14                'Row Exclusive',
 15                4,
 16                'Share',
 17                5,
 18                'Share Row Exclusive',
 19                6,
 20                'Exclusive') lock_type,
 21         paddr,
 22         p.addr,
 23         p.spid
 24    from v$session s, v$locked_object l, all_objects a, v$process p
 25   where s.sid = l.session_id
 26     and p.addr = paddr
 27     and l.object_id = a.object_id;

       SID    SERIAL# USERNAME        MACHINE    OBJECT_NAME                    LOCK_TYPE           PADDR    ADDR     SPID
---------- ---------- --------------- ---------- ------------------------------ ------------------- -------- -------- ------------
       141         91 AN              WORKGROUP\ AA                             Row Exclusive       6C8F0744 6C8F0744 1792
       161         38 AN              WORKGROUP\ AA                             Row Exclusive       6C8F2244 6C8F2244 3564

跟踪等待事件
SQL> select SID,serial#,USERNAME,MACHINE,PROGRAM,EVENT from v$session where sid=&sid and serial#=&serial;
Enter value for sid: 141
Enter value for serial: 91
old   1: select SID,serial#,USERNAME,MACHINE,PROGRAM,EVENT from v$session where sid=&sid and serial#=&serial
new   1: select SID,serial#,USERNAME,MACHINE,PROGRAM,EVENT from v$session where sid=141 and serial#=91

       SID    SERIAL# USERNAME        MACHINE    PROGRAM                                                  EVENT
---------- ---------- --------------- ---------- ---------------------------------------------------------------- ------------------------------

       141         91 AN              WORKGROUP\ sqlplus.exe                                              SQL*Net message from client

SQL> select SID,serial#,USERNAME,MACHINE,PROGRAM,EVENT from v$session where sid=&sid and serial#=&serial;
Enter value for sid: 161
Enter value for serial: 38
old   1: select SID,serial#,USERNAME,MACHINE,PROGRAM,EVENT from v$session where sid=&sid and serial#=&serial
new   1: select SID,serial#,USERNAME,MACHINE,PROGRAM,EVENT from v$session where sid=161 and serial#=38

       SID    SERIAL# USERNAME        MACHINE    PROGRAM                                                  EVENT
---------- ---------- --------------- ---------- ---------------------------------------------------------------- ------------------------------

       161         38 AN              WORKGROUP\ sqlplus.exe                                              enq: TX - row lock contention

如此重现了当时系统的情况
初步认定为应用入库时有重复数据导致的enq: TX - row lock contention等待事件出现，进而无法继续插入数据

解决了重复数据问题后问题解决

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13177610/viewspace-683290/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13177610/viewspace-683290/