enq:TX-rowlockcontention故障处理一则 - 数据库编程

一个很简单的问题，之所以让我对这个问题进行总结，一是因为没我想象的简单，在处理的过程中遇到了一些磕磕碰碰，甚至绕了一些弯路，二是引发了我对故障处理时的一些思考。
6月19日，下午5点左右，数据库出现了大量的enq: TX - row lock contention等待事件，按照以往的经验，这类等待一般与业务逻辑有关，DBA能够做的事情，一般就是将锁等待着的连接信息，等待锁的SQL语句，甚至等待的具体数据行，还有就是锁持有者的连接信息，造成锁等待的SQL语句等一些基本信息提交给开发人员，修改业务逻辑。
注意很多情况下，修改业务逻辑不是一时半会能够立即解决的事，有时候如果锁持有或者锁等待业务不是特别重要，可以与应用维护人员协商，先停掉这部分业务，让其它业务正常运行很多时候，数据库只有一个锁持有者，并且这个锁持有的连接也没有活动，可以与应用维护人员协商，尝试kill锁持有者，看看后续锁等待能否自动解决。这种情况一般是一个单独的连接出现僵死导致，kill掉持有者以后，问题自然就解决了另外一种情况就是，前天晚上上线后，平时运行好好的业务，也没出现锁等待，有一天出现了这类问题，而且kill掉锁持有者以后，问题无法得到解决（业务逻辑问题，kill掉以后，后续还会发生），这一般是前天晚上上线引起。本来觉得数据库层提供信息很简单，结果与想象的有点区别，来看一下具体的过程（1）查询锁信息，如下

SESS	LMODE	LMODE	REQUEST	TYPE	EVENT	SQL_TEXT
Holder: 4266	exclusive	6	0	TX	SQL*Net message from client
Waiter: 3136	none	0	4	TX	enq: TX - row lock contention	insert into xxxxx(ID,xxx,xxxx,xxx,….) values(seq_xxx.nextval,:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")
Holder: 2276	exclusive	6	0	TX	SQL*Net message from client
Waiter: 1716	none	0	4	TX	enq: TX - row lock contention	insert into xxxxx(ID,xxx,xxxx,xxx,….) values(seq_xxx.nextval,:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")
Holder: 1288	exclusive	6	0	TX	SQL*Net message from client
Waiter: 1565	none	0	4	TX	enq: TX - row lock contention	insert into xxxxx(ID,xxx,xxxx,xxx,….) values(seq_xxx.nextval,:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")
Holder: 1000	exclusive	6	0	TX	SQL*Net message from client
Waiter: 1147	none	0	4	TX	enq: TX - row lock contention	insert into xxxxx(ID,xxx,xxxx,xxx,….) values(seq_xxx.nextval,:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")
Holder: 2989	exclusive	6	0	TX	SQL*Net message from client
Waiter: 862	none	0	4	TX	enq: TX - row lock contention	insert into xxxxx(ID,xxx,xxxx,xxx,….) values(seq_xxx.nextval,:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")

备注：表名和列名做了模糊化可以看到，锁等待语句正在等待Insert条记录（2）通过查看锁持有者，已经执行的语句，来判断到底是那个语句造成了锁等待，查询语句如下： select b.sql_text ,a.* from v$open_cursor a,v$sql b where a.sql_id=b.sql_id and a.sid=4266 and upper(b.sql_text) like '%xxxxx%'; （3）根据经验insert一条语句被阻塞，一般是由于主键约束引起（另一个连接也插入了同一条语句或者删除了一条语句，但是没有提交）但是我通过上面的语句查询的时候，发现怎么也找不到锁持有者有执行过这个表的任何DML，而且询问开发人员，他们也说没有对这张表的DML操作其中open_cursor为1000,v$open_cursor中的记录也远远没有达到这个数，才100条不到。session_cached_cursors设置为200，没有道理这个连接执行的语句游标已经被刷新出去（4）还真没有遇到过类似的问题，怎么也找不到。这时我换了一个想法，抛开那些经验，我在想，是不是有一种可能不对Insert插入语句进行任何DML操作，也会造成一条插入语句被锁掉？？我考虑了这张表的依赖对象是不是会造成种类等待，例如触发器、外键引用等等。仔细考虑一番，发现触发器，审计什么的，数据库应该能定位到具体的语句，而不是发生在这个insert语句本身（就算是递归语句， Oracle也能捕获到才对）， 因此，最让我怀疑的就是外键引用，通过下面这个查询，判断是否这个表通过外键引用了其他对象，如下 select a.table_name, a.owner, a.constraint_name, a.constraint_type, a.r_owner, a.r_constraint_name,--被外键引用的约束名 b.table_name --被外键引用的表名 from dba_constraints a, dba_constraints b where a.constraint_type = 'R' and a.r_constraint_name = b.constraint_name and a.r_owner = b.owner and b.table_name = 'xxxxx' and b.owner=''; 查询发现，确实有一张表引用这个插入等待的表，这时，顿时感觉希望很大。（5）通过一个简单的测试，我验证我的推测，如下 create table t3 (id number primary key,name varchar2(20),product_id number); create table t2 (id number primary key,name varchar2(20)); alter table t3 add constraint FK_PRODUCTSTAT_P

enq:TX-rowlockcontention故障处理一则(一)