结果发现很多的enq: TX - row lock contention等待事件，都是昨天的，这个是怎么回事，这个需要我们详细查看GV_$ACTIVE_SESSION_HISTORY表中的字段

首先，我们最容易想到的是sql_ID字段，查询

select * from V$SQL where sql_id='a209wq32zy3jw';

你妹啊，竟然是没有，不会啊，才昨天的sql，应该不会被重share pool中删掉啊，想了想，由于我们这个是rac会不会是连得另外一个节点啊

[sql]

select * from GV_$SQL where sql_id='a209wq32zy3jw';

scrac1/sys@JSCN> select INST_ID,SQL_TEXT from GV_$SQL where sql_id='a209wq32zy3jw';

INST_ID www.2cto.com

----------

SQL_TEXT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1

update QUARTZ.TASK_LOG SET TASK_ID = :1, STATUS = :2, RESULT_DESC = :3, START_TIME = :4, END_TIME = :5

where LOG_ID = :6

1

update QUARTZ.TASK_LOG SET TASK_ID = :1, STATUS = :2, RESULT_DESC = :3, START_TIME = :4, END_TIME = :5

where LOG_ID = :6

这个表和sql都很奇怪，之前我们并没有看到过，GV_$ACTIVE_SESSION_HISTORY中还有一个USER_ID=59

[sql]

scrac1/sys@JSCN> select USERNAME,USER_ID,CREATED from dba_users where USER_ID='59' ;

www.2cto.com

USERNAME USER_ID CREATED

------------------------------ ---------- ------------

QUARTZ 59 25-JUN-12

查看到底是那个表出现了锁等待

[sql]

scrac1/sys@JSCN> select * from dba_objects where object_id='58359';

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S

------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------- ------------ ------------ ------------------- ------- - - -

QUARTZ TASK_LOG 58359 58359 TABLE 02-AUG-12 02-AUG-12 2012-08-02:14:33:28 VALID N N N

想起来了，昨天他们一直再用这个用在做定时任务，在不停的测试。所以产生的。到此原因已经查明。

如果是在session中出现此类问题，解决方法有：

解决方法：

1：通过v$session找到BLOCK=1的用户，告知用户提交事务

2：通过sid找到pid，kill掉该进程

3：更改sql语句，SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME = :1 FOR UPDATE no wait

一般如果是现网中出现了大量类似的问题，排除人为原因，那么就要检查应用了

www.2cto.com

再看看官网上关于TX - row lock contention类容

http://docs.oracle.com/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref761

10.3.7.2.4 TX enqueue

These are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.

Waits for TX in mode 6: occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.