Solving ORA-00060: Deadlock detected small example

Solve ORA-00060: Deadlock detected small instance database version:

SQL > select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - Production

 

Event: database generated Deadlock: ORA-00060: Deadlock detected alert Log as follows:

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 12:39:00 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13792.trc.Wed Jul 10 12:40:02 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 12:41:56 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13790.trc.Wed Jul 10 12:43:00 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13792.trc.Wed Jul 10 12:44:54 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13792.trc.Wed Jul 10 12:48:09 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 12:57:01 CST 2013Thread 1 advanced to log sequence 33866 (LGWR switch)  Current log# 3 seq# 33866 mem# 0: /u02/oradata/xezf/redo30.log  Current log# 3 seq# 33866 mem# 1: /u01/app/oracle/oradata/redo32.logWed Jul 10 12:57:03 CST 2013ARC0: Standby redo logfile selected for thread 1 sequence 33865 for destination LOG_ARCHIVE_DEST_2Wed Jul 10 12:57:09 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc.Wed Jul 10 13:03:59 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 13:08:55 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13780.trc.Wed Jul 10 13:12:58 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13780.trc.Wed Jul 10 13:16:06 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc.Wed Jul 10 13:18:07 CST 2013Thread 1 advanced to log sequence 33867 (LGWR switch)  Current log# 10 seq# 33867 mem# 0: /u02/oradata/xezf/redo10a.log  Current log# 10 seq# 33867 mem# 1: /u01/app/oracle/oradata/redo10b.logWed Jul 10 13:18:10 CST 2013ARC0: Standby redo logfile selected for thread 1 sequence 33866 for destination LOG_ARCHIVE_DEST_2Wed Jul 10 13:24:07 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 13:36:59 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 13:38:03 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 13:40:58 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13780.trc.Wed Jul 10 13:42:08 CST 2013Thread 1 advanced to log sequence 33868 (LGWR switch)  Current log# 8 seq# 33868 mem# 0: /u01/app/oracle/oradata/redo81.log  Current log# 8 seq# 33868 mem# 1: /u02/oradata/xezf/redo80.logWed Jul 10 13:42:10 CST 2013ARC0: Standby redo logfile selected for thread 1 sequence 33867 for destination LOG_ARCHIVE_DEST_2Wed Jul 10 13:44:04 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13788.trc.Wed Jul 10 13:53:11 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc.Wed Jul 10 13:55:05 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc.Wed Jul 10 13:57:07 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13786.trc.Wed Jul 10 13:59:11 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc.Wed Jul 10 14:01:07 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc.Wed Jul 10 14:03:14 CST 2013ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13790.trc.

 

View the trc file as follows:

/u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trcOracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1System name:    LinuxNode name:      qs-xezf-db1Release:        2.6.18-194.el5Version:        #1 SMP Tue Mar 16 21:52:39 EDT 2010Machine:        x86_64Instance name: xezfRedo thread mounted by this instance: 1Oracle process number: 132Unix process pid: 13782, image: oracle@qs-xezf-db1*** 2013-07-10 12:57:09.184*** ACTION NAME:() 2013-07-10 12:57:09.159*** MODULE NAME:(JDBC Thin Client) 2013-07-10 12:57:09.159*** SERVICE NAME:(SYS$USERS) 2013-07-10 12:57:09.159*** SESSION ID:(870.2207) 2013-07-10 12:57:09.159DEADLOCK DETECTED ( ORA-00060 )[Transaction Deadlock]The following deadlock is not an ORACLE error. It is adeadlock due to user error in the design of an applicationor from issuing incorrect ad-hoc SQL. The followinginformation may aid in determining the deadlock:Deadlock graph:                       ---------Blocker(s)--------  ---------Waiter(s)---------Resource Name          process session holds waits  process session holds waitsTX-007f001d-00003059       132     870     X            138     891           XTX-009a0015-000032f0       138     891     X            132     870           Xsession 870: DID 0001-0084-00011DC8     session 891: DID 0001-008A-0001E820session 891: DID 0001-008A-0001E820     session 870: DID 0001-0084-00011DC8Rows waited on:Session 891: obj - rowid = 0000E6B1 - AAAOaxAAGAABL3mAAe  (dictionary objn - 59057, file - 6, block - 310758, slot - 30)Session 870: obj - rowid = 0000E6B1 - AAAOaxAAGAABL3mAAA  (dictionary objn - 59057, file - 6, block - 310758, slot - 0)Information on the OTHER waiting sessions:Session 891:  sid: 891 ser: 9175 audsid: 23320314 user: 61/<none>    flags: (0x100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-    flags2: (0x8)  pid: 138 O/S info: user: oracle, term: UNKNOWN, ospid: 13792    image: oracle@qs-xezf-db1  O/S info: user: root, term: unknown, ospid: 1234, machine: qs-xept-app            program: JDBC Thin Client  application name: JDBC Thin Client, hash value=2546894660  Current SQL Statement:  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)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  name0x196a38e48       122  procedure XEZF.PROC_OB_GETDATA_ASR0x19454f3c8         1  anonymous block===================================================

 

Based on trc information, query:

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                  5200000001983B5378        138 13792        oracle                   6SQL> 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 00000001983B5378SQL>

 

Query by the spid provided by v $ process at the operating system level:

[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

 

As we can see from the time above that the program was called twice at the same time, we asked the development colleagues to check whether the program was called twice at the same time. The feedback is true, so they can solve the problem after making the changes.