Single resource deadlock with a zero DID

RAC有两个节点, 版本10.2.0.4

节点1告警日志:

Mon Feb  9 22:46:13 2009
Global Enqueue Services Deadlock detected. More info in file
 /home/oracle/app/oracle/admin/s3db/udump/s3db1_ora_9628.trc.
Mon Feb  9 22:46:13 2009
Trace dumping is performing id=[cdmp_20090209224613]
Mon Feb  9 22:52:13 2009
Trace dumping is performing id=[cdmp_20090209225213]

节点2告警日志:

Mon Feb  9 22:46:13 2009
Trace dumping is performing id=[cdmp_20090209224613]
Mon Feb  9 22:52:13 2009
Global Enqueue Services Deadlock detected. More info in file
 /home/oracle/app/oracle/admin/s3db/bdump/s3db2_j000_20610.trc.
Mon Feb  9 22:52:13 2009
Trace dumping is performing id=[cdmp_20090209225213]

bdump下产生许多cdmp(crash dump)目录

...
drwxr-x--- 2 oracle oinstall  4096 Feb  9 22:46 cdmp_20090209224613
drwxr-x--- 2 oracle oinstall  4096 Feb  9 22:52 cdmp_20090209225213

-rw-r----- 1 oracle oinstall 36864 Feb  9 22:46 s3db1_cjq0_13725.trw
-rw-r----- 1 oracle oinstall 38912 Feb  9 22:46 s3db1_ckpt_13719.trw
-rw-r----- 1 oracle oinstall 36864 Feb  9 22:46 s3db1_dbw0_13710.trw
-rw-r----- 1 oracle oinstall  6144 Feb  9 22:46 s3db1_diag_13692.trw
-rw-r----- 1 oracle oinstall 34816 Feb  9 22:46 s3db1_j000_13854.trw
-rw-r----- 1 oracle oinstall 32768 Feb  9 22:46 s3db1_j001_23488.trw
...

.trc跟踪文件显示:

*** 2009-02-09 22:46:13.208
*** ACTION NAME:() 2009-02-09 22:46:13.207
*** MODULE NAME:(JDBC Thin Client) 2009-02-09 22:46:13.207
*** SERVICE NAME:(s3db) 2009-02-09 22:46:13.207
*** SESSION ID:(255.38536) 2009-02-09 22:46:13.207
Single resource deadlock: blocking enqueue which blocks itself, f 1
Granted global enqueue 0xdd3e6960
----------enqueue 0x0xdd3e6960------------------------
lock version     : 546701
Owner node       : 0
grant_level      : KJUSERNL
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : (nil)
resp             : 0x6a3ccf68
procp            : 0xdfe29248
pid              : 13854
proc version     : 0
oprocp           : (nil)
opid             : 0
group lock owner : (nil)
xid              : 0000-0000-00000000
dd_time          : 0.0 secs
dd_count         : 0
timeout          : 1.0 secs
On_timer_q?      : Y
On_dd_q?         : N
lock_state       : OPENING CONVERTING
Open Options     :  KJUSERPROCESS_OWNED
Convert options  : KJUSERNODEADLOCKWAIT KJUSERNODEADLOCKBLOCK
History          : 0x1495149a
Msg_Seq          : 0x0
res_seq          : 1343
valblk           : 0x00000000000000000000000000000000 .
user session for deadlock lock 0xdd3e6960
  pid=27 serial=15989 audsid=6558759 user: 52/S3TEST
  O/S info: user: oracle, term: UNKNOWN, ospid: 13854, machine: Perf-DB-1.s3lab.mot.com
            program: oracle@Perf-DB-1.s3lab.mot.com (J000)
  Current SQL Statement:
  DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN process_events(); :mydate := next_date; IF broken T
HEN :b := 1; ELSE :b := 0; END IF; END;
Requesting global enqueue 0xdd2e3830
----------enqueue 0x0xdd2e3830------------------------
lock version     : 515577
Owner node       : 0
grant_level      : KJUSERPR
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : (nil)
resp             : 0x6a3ccf68
procp            : 0xdfe367f0
pid              : 9628
proc version     : 82
oprocp           : (nil)
opid             : 0
group lock owner : (nil)
xid              : 0000-0000-00000000
dd_time          : 0.0 secs
dd_count         : 0
timeout          : 0.0 secs
On_timer_q?      : N
On_dd_q?         : N
lock_state       : CONVERTING
Open Options     :  KJUSERPROCESS_OWNED
Convert options  : KJUSERNODEADLOCKWAIT KJUSERNODEADLOCKBLOCK
History          : 0x49514955
Msg_Seq          : 0x0
res_seq          : 1343
valblk           : 0x00000000000000000000000000000000 .
user session for deadlock lock 0xdd2e3830
  pid=92 serial=38536 audsid=6558621 user: 52/S3TEST
  O/S info: user: s3op1, term: unknown, ospid: 1234, machine: Perf-app-3.s3lab.mot.com
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL information unavailable
----------resource 0x0x6a3ccf68----------------------
resname       : [0x49014fd][0xd8ee66f6],[IV]
Local node    : 0
dir_node      : 1
master_node   : 1
hv idx        : 60
hv last r.inc : 16
current inc   : 16
hv status     : 0
hv master     : 1
open options  :
Held mode     : KJUSEREX
Cvt mode      : KJUSERNL
Next Cvt mode : KJUSERNL
msg_seq       : 0x40005
res_seq       : 1343
grant_bits    : KJUSERNL KJUSERPR
grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX
count         : 1         0         0         2         0         0
val_state     : KJUSERVS_NOVALUE
valblk        : 0x00000000000000000000000000000000 .
access_node   : 0
vbreq_state   : 0
state         : x8
resp          : 0x6a3ccf68
On Scan_q?    : N
cache level   : 1
Total accesses: 140471
Imm.  accesses: 135217
Granted_locks : 2
Cvting_locks  : 1
value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0xdd0ceb40 gl KJUSERPR rp 0x6a3ccf68 [0x49014fd][0xd8ee66f6],[IV]
  master 1 pid 13740 bast 0 rseq 1343 mseq 0 history 0x49514951
  open opt  KJUSERPROCESS_OWNED
lp 0xdd2e3830 gl KJUSERPR rp 0x6a3ccf68 [0x49014fd][0xd8ee66f6],[IV]
  master 1 pid 9628 bast 0 rseq 1343 mseq 0 history 0x49514955
  open opt  KJUSERPROCESS_OWNED
CONVERT_Q:
lp 0xdd3e6960 gl KJUSERNL rl KJUSEREX rp 0x6a3ccf68 [0x49014fd][0xd8ee66f6],[IV]
  master 1 pid 13854 bast 0 rseq 1343 mseq 0 history 0x1495149a
  convert opt KJUSERNODEADLOCKWAIT KJUSERNODEADLOCKBLOCK
*** 2009-02-09 22:46:13.210
KSTDUMP: In-memory trace dump
TIME(usecs):SEQ# ORAPID   SID EVENT  OP DATA
========================================================================
096E4B4F:2676CB3E    92   255 10401   1 KSXPVSND: client 2 tid(2,257,0x2ac3730f) buf 0xdd542828 sz 400
096E4B71:2676CB3F    92   255 10005   1 KSL WAIT BEG [gc cr request] 7/0x7 658977/0xa0e21 1/0x1
...

xid是0000-0000-00000000


这是个BUG, Bug 6145177 - Single resource deadlock with a zero DID
下载补丁SINGLE RESOURCE DEADLOCK ON INSERT



Where to Find Files for Analyzing Errors




-fin-