oracle通过trc查找死锁sql,从trc查找死锁的有关问题

从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-00000006session 786: DID 0001-00B6-0000064E

session 786: DID 0001-00B6-0000064Esession 997: DID 0001-0071-00000006

Rows waited on:

Session 786: obj - rowid = 0002D33A - AAAtM6AAdAAAJ9BABO

(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_seg

set

SEGMENT_ID = :1,

SEAT_STATUS = :2,

SEGMENT_CLASS = :3,

SEGMENT_SHARE_CLASS = :4,

SEG_SEAT_NO = :5,

SEG_CREATION_NUM= :6,

SEG_CREATION_TIME = :7

where locator_id = :8

and SEG_ORDER_ID = :9

通过

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

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

查找出死锁资源的两个ROWID对应的object_id，然后执行

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='AAAtM6AAdAAAJ9BABO';

查找表中该行记录的内容。

这里查到这两个表之间没有主外键关联，自然也不是因为外键没索引导致的死锁，且数据可能已被覆盖，因此查不到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的详细信息，找机会仔细研究下再总结。