help with a GES deadlock

From: Walker, Jed S <Jed_Walker_at_cable.comcast.com>
Date: Mon, 15 Aug 2011 15:33:22 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C4E39796C_at_COPDCEXMB08.cable.comcast.com>

Hi all,

Here's my next new to RAC question. We are getting a Global Enqueue Services Deadlock quite often. Looking at the trace file it looks to me like it is a regular deadlock, so I'm wondering if something else is going on here, or if RAC just reports a regular deadlock as a GED deadlock. This is a five node 10.2.0.4.0 RAC cluster.

Each schema should only be used on one instance of the database (each "market" has its own schema and all connections for that market are sent to a single service running on a single instance, so there should not be any blocks being worked by multiple instances).

Would a standard deadlock show up as a GED deadlock in RAC?

If I read this correctly, sid 42 is doing a delete and then an update, but 41 is doing an update and then a delete (presumably affecting the same rows).

Am I reading this correctly?

• 2011-08-01 17:17:45.785 Setting 3-way CR grants to 1 global-lru off? 0
• 2011-08-01 18:14:11.067 user session for deadlock lock 0x4560db490 pid=41 serial=4770 audsid=156523088 user: 0/SYS O/S info: user: , term: , ospid: 1234, machine: flux-atlt-04.westchester.pa.bo.comcast.net program: Current SQL Statement: delete from STB_SETTING where STB_SETTING_ID=:1 user session for deadlock lock 0x4560db340 pid=42 serial=16019 audsid=156522961 user: 70/RDVRGW_ATLT O/S info: user: , term: , ospid: 1234, machine: flux-atlt-01.westchester.pa.bo.comcast.net program: Current SQL Statement:

update FLUX_STB_MESSAGE_QUEUE set SETTING_TYPE_ID=:1, SETTING_TYPE_RECORD_ID=:2, MAC_ADDR=:3, MESSAGE_SEQUENCE=:4, REQUEST_TAG=:5, STB_SYNC_STATE_ID=:6, ACTION_ID=:7, MESSAGE_SENT_DATE=:8, REC_START_TIME=:9, REC_END_TIME=:10, MESSAGE_CREATED=:11, PRIORITY=:12, STB_SETTING_ID=:13 where FLUX_STB_MESSAGE_QUEUE_ID=:14 user session for deadlock lock 0x457130888   pid=42 serial=16019 audsid=156522961 user: 70/RDVRGW_ATLT   O/S info: user: , term: , ospid: 1234, machine: flux-atlt-01.westchester.pa.bo.comcast.net

            program:
  Current SQL Statement:

update FLUX_STB_MESSAGE_QUEUE set SETTING_TYPE_ID=:1, SETTING_TYPE_RECORD_ID=:2, MAC_ADDR=:3, MESSAGE_SEQUENCE=:4, REQUEST_TAG=:5, STB_SYNC_STATE_ID=:6, ACTION_ID=:7, MESSAGE_SENT_DATE=:8, REC_START_TIME=:9, REC_END_TIME=:10, MESSAGE_CREATED=:11, PRIORITY=:12, STB_SETTING_ID=:13 where FLUX_STB_MESSAGE_QUEUE_ID=:14 user session for deadlock lock 0x457130738   pid=41 serial=4770 audsid=156523088 user: 0/SYS   O/S info: user: , term: , ospid: 1234, machine: flux-atlt-04.westchester.pa.bo.comcast.net

            program:
  Current SQL Statement:
  delete from STB_SETTING where STB_SETTING_ID=:1

Global blockers dump start:---------------------------------

DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x2fa0019][0xc6eb2],[TX]

----------resource 0x0x4329c7288----------------------
resname       : [0x2fa0019][0xc6eb2],[TX]
Local node    : 0
dir_node      : 0
master_node   : 0
hv idx        : 31
hv last r.inc : 10
current inc   : 10
hv status     : 0
hv master     : 1
open options  : dd
grant_bits    : KJUSERNL KJUSEREX
grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX
count         : 1         0         0         0         0         1
val_state     : KJUSERVS_NOVALUE
valblk        : 0x00000000000000000000000000000000 .
access_node   : 0
vbreq_state   : 0
state         : x0
resp          : 0x4329c7288
On Scan_q?    : N
Total accesses: 43
Imm.  accesses: 38

Granted_locks : 1
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 GRANTED_Q :
lp 0x4560db340 gl KJUSEREX rp 0x4329c7288 [0x2fa0019][0xc6eb2],[TX]   master 0 gl owner 0x45b3b2330 possible pid 13832 xid 2B000-0001-00000BB0 bast 0 rseq 3 mseq 0 history 0x14951495   open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0x4560db490 gl KJUSERNL rl KJUSEREX rp 0x4329c7288 [0x2fa0019][0xc6eb2],[TX]   master 0 gl owner 0x45b41d3b0 possible pid 13830 xid 2B000-0001-00000BC8 bast 0 rseq 3 mseq 0 history 0x1495149a   convert opt KJUSERGETVALUE
.......

• Jed Walker

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 15 2011 - 10:33:22 CDT