从trc查找死锁的问题

查看trc文件：

[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 TM-0000d6d7-00000000       172      80    SX             27    5660    SX   SSX TX-01580013-00001b1d        27    5660     X            172      80           X   session 80: DID 0001-00AC-0000E2A6      session 5660: DID 0001-001B-00000017 session 5660: DID 0001-001B-00000017    session 80: DID 0001-00AC-0000E2A6   这里显示发生死锁的会话   Rows waited on:   Session 80: obj - rowid = 0000D6D7 - AAANbXAAEAAAR3uAAX   (dictionary objn - 54999, file - 4, block - 73198, slot - 23)   Session 5660: no row   ----- Information for the OTHER waiting sessions ----- Session 5660:   sid: 5660 ser: 29 audsid: 24962141 user: 62/NBPM     flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-     flags2: (0x40009) -/-/INC   pid: 27 O/S info: user: oracle, term: UNKNOWN, ospid: 5635     image: oracle@bestoradb2.800best.com   client details:     O/S info: user: nbpm, term: unknown, ospid: 1234     machine: nbmp.800best.com program: JDBC Thin Client     application name: JDBC Thin Client, hash value=2546894660   current SQL:   DELETE FROM bpm_USER WHERE ID = :1   ----- End of information for the OTHER waiting sessions -----   Information for THIS session:   ----- Current SQL Statement for this session (sql_id=0t8gtvwsgqsvw) ----- DELETE FROM bpm_USER_ROLEGROUP t WHERE t.user_Id=:1 发生死锁的两个dml语句 =================================================== PROCESS STATE

 

通过select dbms_rowid.rowid_object('AAANbXAAEAAAR3uAAX') from dual; 找出发生死锁的object_id.

SQL> select dbms_rowid.rowid_object('AAANbXAAEAAAR3uAAX') from dual;

DBMS_ROWID.ROWID_OBJECT('AAANBXAAEAAAR3UAAX')
---------------------------------------------
                                        54999

 

通过object_id找出object_name;

SQL> SELECT t.owner,t.object_name,t.object_type FROM all_objects t WHERE t.data_object_id = 54999;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
OBJECT_TYPE
-------------------
NBPM                           BPM_USER_ROLEGROUP
TABLE

 

接下来可以用object_name 找出发生死锁的行：

SQL> SELECT * FROM  NBPM.BPM_USER_ROLEGROUP WHERE ROWID='AAANbXAAEAAAR3uAAX';

no rows selected

改行已经不存在了。

 

其实产生死锁的object就在dml语句中，没必要这样去找。