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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-773617/，如需轉載，請註明出處，否則將追究法律責任。