解决ORA-00060: Deadlock detected小例(三)
LLOUT_HISTORY SET TIME_CALL=:B5 ,OUTBOUND_RESULT=:B4 ,RECFILE =:B3 ,DELIVER_TIMES=0,ISSEND=0, ASR_RESULT = :B2 WHERE ID=:B1 AND
FLOWFLAG IN (51,52,53,54)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE DAT_OB_IVRCALLOUT_HISTORY SET TIME_CALL=:B5 ,OUTBOUND_RESULT=:B4 ,RECFILE =:B3 ,DELIVER_TIMES=0,ISSEND=0, ASR_RESULT = :B2 WHERE ID=:B1 AND FLOWFLAG IN (51,52,53,54)
----- PL/SQL Call Stack -----
object line object
handle number name
0x196a38e48 122 procedure XEZF.PROC_OB_GETDATA_ASR
0x19454f3c8 1 anonymous block
===================================================
根据trc信息，查询：
SQL> select addr,pid,spid,username,serial# from v$process t where t.PID in (132,138);
ADDR PID SPID USERNAME SERIAL#
---------------- ---------- ------------ --------------- ----------
000000019138CE88 132 13782 oracle 52
00000001983B5378 138 13792 oracle 6
SQL> select sid,serial#,paddr from v$session k where k.PADDR in ('000000019138CE88',
2 '00000001983B5378'
3 )
4 ;
SID SERIAL# PADDR
---------- ---------- ----------------
870 2207 000000019138CE88
891 9175 00000001983B5378
SQL>
根据 v$process 提供的spid 在操作
系统层面查询：
[root@qs-xezf-db1 ~]# ps -ef |grep 13782 oracle 13782 1 1 11:30 00:01:48 oraclexezf (LOCAL=NO) root 27059 5697 0 13:56 pts/3 00:00:00 grep 13782 [root@qs-xezf-db1 ~]# ps -ef |grep 13792 oracle 13792 1 1 11:30 00:01:45 oraclexezf (LOCAL=NO) root 27065 5697 0 13:56 pts/3 00:00:00 grep 13792
由于上面的时间可以看出，在相同时间同时调用了两次，于是让开发的同事检查程序是否同时调用了两次，反馈信息确实如此，于是让他们更改之后，问题得以解决。