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对于这种情况一般会自动处理,但如果频繁报错就需要应用去改了