Linux ora-00060,解决 ORA-00060: Deadlock detected 小例

数据库版本:

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 - 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-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/

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#

---------------- ---------- ------------ --------------- ----------

000000019138CE88 13213782 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

---------- ---------- ----------------

8702207 000000019138CE88

8919175 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

由于上面的时间可以看出,在相同时间同时调用了两次,于是让开发的同事检查程序是否同时调用了两次,反馈信息确实如此,于是让他们更改之后,问题得以解决。

0b1331709591d260c1c78e86d0c51c18.png

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值