從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 - 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的詳細資訊，找機會仔細研究下再總結。