ORA-00060 Deadlock detected 解读

      今天，在GC监控控制台里发现了ORA-00060 Deadlock detected死锁信息，主要信息如下：

Dump file d:\oracle\product\10.2.0\admin\mc\udump\mc_ora_14100.trc

Fri Dec 14 09:41:07 2012
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.0 Service Pack 2
CPU                 : 16 - type 8664, 8 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:2504M/16372M, Ph+PgF:16350M/32959M
Instance name: mc

Redo thread mounted by this instance: 1

Oracle process number: 398

Windows thread id: 14100, image: ORACLE.EXE (SHAD)


*** 2012-12-14 09:41:07.078
*** ACTION NAME:() 2012-12-14 09:41:07.070
*** MODULE NAME:(w3wp.exe) 2012-12-14 09:41:07.070
*** SERVICE NAME:(SYS$USERS) 2012-12-14 09:41:07.070
*** SESSION ID:(269.59908) 2012-12-14 09:41:07.070
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-00090017-000be5c4       398     269     X            390     154           X
TX-00060008-0006b498       390     154     X            398     269           X
session 269: DID 0001-018E-000095FD    session 154: DID 0001-0186-00008596

session 154: DID 0001-0186-00008596    session 269: DID 0001-018E-000095FD

以上信息说明了两个会话的相互等待资源情况

其中Resource Name 说明了死锁类型，TX-00090017-000be5c4的含义为 ：

锁类型-v$lock的ID1字段-v$lock的ID2字段

其中ID1字段分为事务的回滚段号+slot号组合而成，前4位为回滚段号：9和后四位为SLOT号：23

Rows waited on:
Session 154: obj - rowid = 0000D2D9 - AAANLZAAJAAEBK0AA2
  (dictionary objn - 53977, file - 9, block - 1053364, slot - 54)
Session 269: obj - rowid = 0000D2D9 - AAANLZAAJAAEBK0AA3

  (dictionary objn - 53977, file - 9, block - 1053364, slot - 55)

以上信息说明等待资源的对象object id和rowid，括号里是解释对象ID代表的物理对象，如文件，块等

Information on the OTHER waiting sessions: （被阻塞的session的内容）
Session 154:
  pid=390 serial=49211 audsid=37251930 user: 61/DPACK
  O/S info: user: A0748, term: A0748, ospid: 3880:3888, machine: GOLDDRAGON.COM\A0748
            program: dpack.exe
  application name: dpack.exe, hash value=3910194280
  Current SQL Statement:
  update OE_SEQ_NO set maxno =maxno + 1 , update_date =:1 , update_pgm_id =:2 , update_term =:3 where ctl_cd =:4 and to_char ( update_date , 'Mon DD YYYY hh:mm:sssssAM' ) =:5
End of information on OTHER waiting sessions.
Current SQL statement for this session: （当前session的内容）

UPDATE OE_SEQ_NO SET MAXNO = MAXNO + 1 ,UPDATE_DATE = SYSDATE ,UPDATE_PGM_ID = 'SP_Action' ,UPDATE_TERM = 'SP_Host' WHERE CTL_CD = :B2 AND TO_CHAR(UPDATE_DATE,'Mon DD YYYY hh:mm:sssssAM') = :B1

看了这个死锁，应用程序的实现是比较大的问题，已经反馈给应用开发人员进行处理，这个死锁报错后，估计操作人员等待太久后，其中一方取消操作后死锁解除。