解决ORA-00060: Deadlock detected小例
解决ORA-00060: Deadlock detected小例
数据库版本:
SQL > select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production
事件:数据库产生死锁:
ORA-00060: Deadlock detected
alert 日志如下:
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 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13792.trc. Wed Jul 10 12:40:02 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc. Wed Jul 10 12:41:56 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13790.trc. Wed Jul 10 12:43:00 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13792.trc. Wed Jul 10 12:44:54 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13792.trc. Wed Jul 10 12:48:09 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc. Wed Jul 10 12:57:01 CST 2013 Thread 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.log Wed Jul 10 12:57:03 CST 2013 ARC0: Standby redo logfile selected for thread 1 sequence 33865 for destination LOG_ARCHIVE_DEST_2 Wed Jul 10 12:57:09 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc. Wed Jul 10 13:03:59 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc. Wed Jul 10 13:08:55 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13780.trc. Wed Jul 10 13:12:58 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13780.trc. Wed Jul 10 13:16:06 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc. Wed Jul 10 13:18:07 CST 2013 Thread 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.log Wed Jul 10 13:18:10 CST 2013 ARC0: Standby redo logfile selected for thread 1 sequence 33866 for destination LOG_ARCHIVE_DEST_2 Wed Jul 10 13:24:07 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc. Wed Jul 10 13:36:59 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc. Wed Jul 10 13:38:03 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc. Wed Jul 10 13:40:58 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13780.trc. Wed Jul 10 13:42:08 CST 2013 Thread 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.log Wed Jul 10 13:42:10 CST 2013 ARC0: Standby redo logfile selected for thread 1 sequence 33867 for destination LOG_ARCHIVE_DEST_2 Wed Jul 10 13:44:04 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13788.trc. Wed Jul 10 13:53:11 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc. Wed Jul 10 13:55:05 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc. Wed Jul 10 13:57:07 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13786.trc. Wed Jul 10 13:59:11 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13794.trc. Wed Jul 10 14:01:07 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc. Wed Jul 10 14:03:14 CST 2013 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/xezf/udump/xezf_ora_13790.trc.
查看trc文件如下:
/u01/app/oracle/admin/xezf/udump/xezf_ora_13782.trc Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 System name: Linux Node name: qs-xezf-db1 Release: 2.6.18-194.el5 Version: #1 SMP Tue Mar 16 21:52:39 EDT 2010 Machine: x86_64 Instance name: xezf Redo thread mounted by this instance: 1 Oracle process number: 132 Unix 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.159 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-007f001d-00003059 132 870 X 138 891 X TX-009a0015-000032f0 138 891 X 132 870 X session 870: DID 0001-0084-00011DC8 session 891: DID 0001-008A-0001E820 session 891: DID 0001-008A-0001E820 session 870: DID 0001-0084-00011DC8 Rows 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 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# ---------------- ---------- ------------ -------------
上一个:Oracle RAC日常管理命令
下一个:recyclebin学习