网路互联
虚拟化容器,大数据,DBA,中间件,监控。

解决ORA-00060: Deadlock detected小例

12 10月
作者:admin|分类:DBA运维

解决ORA-00060: Deadlock detected小例
 
数据库版本：
?
1
2
3
4
5
6
7
8
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  日志如下：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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文件如下：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
/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信息，查询：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 在操作系统层面查询：
 
?
1
2
3
4
5
6
[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

 

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

更多文章推荐

历史上的今天
10月
12
浏览2195 评论0
返回
目录 返回
首页
ORA-19527与ORA-00312和db_recovery_file_dest_size of 2048 MB is 0.00% used XEN: "x0" re-spawning too fast message/ agetty[8511]: /dev/xvc0: No such file or directory
↑