从trc查找死锁的问题

今日alert日志报ORA-00060死锁错误，查看trc文件：

*** 2013-09-29 01:03:47.762 *** SERVICE NAME:(SYS$USERS) 2013-09-29 01:03:47.744 *** SESSION ID:(997.178) 2013-09-29 01:03:47.744 DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-005d002f-000046dd 113 997 X 182 786 X TX-004d0026-00009b4e 182 786 X 113 997 X session 997: DID 0001-0071-00000006 session 786: DID 0001-00B6-0000064E session 786: DID 0001-00B6-0000064E session 997: DID 0001-0071-00000006 Rows waited on: Session 786: obj - rowid = 0002D33A - AAATM6AAAAAABABO (dictionary objn - 185146, file - 29, block - 40769, slot - 78) Session 997: obj - rowid = 000527D6 - AABSfWAAdAACmKAAAe (dictionary objn - 337878, file - 29, block - 680576, slot - 30) Information on the OTHER waiting sessions: Session 786: pid=182 serial=10783 audsid=64898626 user: 96/GALT O/S info: user: batch, term: , ospid: 23674, machine: v490c1-app program: sqlplus@v490c1-app (TNS V1-V3) application name: SQL*Plus, hash value=3669949024 Current SQL Statement: DELETE FROM ANA A WHERE EXISTS (SELECT 1 FROM (SELECT LOCATOR_ID FROM (SELECT T.LOCATOR_ID,ROWNUM RN FROM TEMP T ) WHERE RN > :B2 AND RN <= :B1 ) B WHERE A.LOCATOR_ID = B.LOCATOR_ID) End of information on OTHER waiting sessions. Current SQL statement for this session: update ana_segset SEGMENT_ID = :1,SEAT_STATUS = :2,SEGMENT_CLASS = :3,SEGMENT_SHARE_CLASS = :4,SEG_SEAT_NO = :5,SEG_CREATION_NUM= :6,SEG_CREATION_TIME = :7where locator_id = :8and SEG_ORDER_ID = :9

通过

select dbms_rowid.rowid_objectAAATM6AAdAAJ9BABO') from dual;

select dbms_rowid.rowid_object('AABSfWAAdAACmKAAAe') from dual;

Object_发现了两个与死锁资源对应的ROWIDid，然后执行

SELECT t.owner,t.object_name,t.object_type FROM all_objects t WHERE t.data_object_id = id;

找到相应的对象信息。ANA表对应，ANA_SEG表对应。

然后也可以通过：

SELECT * FROM ANA WHERE ROWID=AATM6AAAABABO';

查找银行在表中记录的内容。

这里发现两个表之间没有主外键关联，自然不是因为外键没有索引导致的死锁，数据可能已经被覆盖，所以没有找到ROWID对应的记录，也没有找到原因。怀疑是否可能是记录TRC时的混乱？但至少上述方法可以进一步挖掘死锁SQL资源和相应的对象信息。

关于死锁，上面的Oracle也说：

The following deadlock is not an ORACLE error. It is adeadlock due to user error in the design of an applicationor from issuing incorrect ad-hoc SQL. The followinginformation may aid in determining the deadlock:

一般来说，它是由应用程序产生的，因此有必要检查应用程序。感觉分析的思路是从trc中找到死锁资源，进一步分析相关对象，挖掘原因。

至于死锁的原因、外键索引和trc的详细信息，在总结之前找机会仔细研究。