ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 从trc查找死锁的问题

从trc查找死锁的问题

原创 Linux操作系统 作者:bisal 时间:2013-09-29 17:29:23 0 删除 编辑

今天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 - 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 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:

一般是由应用产生的,所以检查应用可能是必要的,感觉分析的思路就是从trc中找到死锁的资源,再进一步分析关联的对象,挖掘产生的原因。


至于死锁的原因、外键索引以及trc的详细信息,找机会仔细研究下再总结。

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

请登录后发表评论 登录
全部评论
Oracle ACE,10g/11g OCP,11g OCM,国内首批Oracle YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),EXIN DevOps Master,Oracle爱好者,微信公众号:bisal的个人杂货铺

注册时间:2013-07-26

  • 博文量
    340
  • 访问量
    2622869