ORA-00060: Deadlock detected

Thu Nov 19 01:13:49 2015
ORA-00060:Deadlock detected. More info infile
/u01/oracle/diag/rdbms/appserv/appserv/trace/appserv_ora_20577.trc.

去查看相应的trace文件appserv_ora_20577.trc的死锁信息

PS:相应的trc文件中的内容比较多建议要看下响应报错的时间点要吻合

*** 2015-11-19 01:13:48.592
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-00090002-00030b2f        67     629     X             66     601           X
TX-000b0009-00147b59        66     601     X             67     629           X
 
session 629: DID 0001-0043-00000011 session 601: DID 0001-0042-000004C5 
session 601: DID 0001-0042-000004C5 session 629: DID 0001-0043-00000011 
 
Rows waited on:
  Session 629: obj - rowid = 00016255 - AAAZfqAAJAAElQvAAZ
  (dictionary objn - 90709, file - 9, block - 1201199, slot - 25)
  Session 601: obj - rowid = 00016255 - AAAZfqAAJAAElStAAV
  (dictionary objn - 90709, file - 9, block - 1201325, slot - 21)
 
----- Information for the OTHER waiting sessions -----
Session 601:
  sid: 601 ser: 941 audsid: 3082107 user: 88/STAT_HOLLYCRMAPP
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 66 O/S info: user: oracle, term: UNKNOWN, ospid: 12644
    image: oracle@appserv172.szgas.com
  client details:
    O/S info: user: Administrator, term: unknown, ospid: 1234
    machine: CALLCENTER246 program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
  current SQL:
  delete call_rec where 1=1  and row_date >= to_date('2015-11-18','yyyy-MM-dd')  and row_date < to_date('2015-11-19','yyyy-MM-dd') 
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=1mp3bsu6ggr12) -----
delete call_rec where 1=1  and segstart >=  1447884000 and segstart < 1447898400
===================================================

 

根据trc信息，查询：

SQL> select addr,pid,spid,username,serial# from v$process t where t.PID in (67,66);

 
ADDR          PID SPID    USERNAMESERIAL#
----------------                     ----- ------- --------------- ----------
00000003CA391AF0 66 12644    oracle   248
00000003C8365EB8 67 20577     oracle    14
SQL> select sid,serial#,paddr from v$session k where k.PADDR in ('00000003CA391AF0',
'00000003C8365EB8') ;
  2  
       SID    SERIAL# PADDR
---------- ---------- ----------------
       601  941 00000003CA391AF0
       629 2821 00000003C8365EB8
SQL> select SID, STATUS FROM v$session where SID=601;   


       SID STATUS
---------- --------
       601 INACTIVE


SQL> 

INACTIVE状态的会话表示此会话处于非活动、空闲、等待状态。

ORACLE对于这种情况一般会自动处理，但如果频繁报错就需要应用去改了