ORA-00060 Deadlock detected 解读

我不是女神ヾ 2022-05-10 13:42 152阅读 0赞

今天，在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

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