RE: [Fwd: Re: Deadlock inserting into same rowid (different block)]

From: Patterson, Joel <jpatterson_at_entint.com>
Date: Thu, 21 Mar 2013 12:46:24 -0400
Message-ID: <C1117B1AA0340645894671E09A7891F714F8833B32_at_EIHQEXVM2.ei.local>

The theory is (related to sequences and RAC), because there are two, (or more), application servers, (nodes), and the sequence cache is set to 500, that both servers either have the cached sequences or are using the same cached sequences in the database, (servers are running hibernate) -- at the same time running a batch process that is inserting records into the child table.

Could be wrong, but the developer is going to insure that the nodes to not run at the same time for now, but the looking continues.

Joel Patterson
Database Administrator
904 928-2790

--
Joel Patterson
Sr. Database Administrator | Enterprise Integration
Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/

http://www.entint.com/

http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT

This message (and any associated files) is intended only for the use
of the addressee and may contain information that is confidential,
subject to copyright or constitutes a trade secret. If you are not the
intended recipient, you are hereby notified that any dissemination,
copying or distribution of this message, or files associated with this
message, is strictly prohibited. If you have received this message in
error, please notify us immediately by replying to the message and
deleting it from your computer. Messages sent to and from us may be
monitored. Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company. [v.1.1]

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of D'Hooge Freek
Sent: Thursday, March 21, 2013 12:03 PM
To: oracle-l_at_freelists.org
Subject: [Fwd: Re: Deadlock inserting into same rowid (different block)]

resending because mail on freelists showed only some gibberish

But if your values for the primary key constraint on the child table are generated by a sequence, how would you end up with sessions trying to insert duplicate values?

--
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(03) 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer.html





On do, 2013-03-21 at 16:19 +0100, Patterson, Joel wrote:


> This was enough to dig into the application further.  We know that last night's process had to be one of a few - since it is acceptance and no 'user' was logged in and working.

> We hypothesize that there are two application layer 'nodes' executing inserts into the child table - at the same time.  The Cache on the sequence used for the primary key is set to 500 - clue.

>

> However, as to the suspect reporting of the deadlock graph - it appears that Jonathan was spot on, (I have a little English in me).

>

> The column used by the child table sequence is the primary key for the child table, and is not related to the FK index mentioned in the Deadlock graph, object 199909, which references the parent table on a totally different column.

>

> So we theorize that the primary key overlap is actually on the child table - and had nothing to do with the FK index to the parent table as reported in the deadlock graph; 'mystery'.

>

> Still looking into getting the values for the bind variable in the trace file.  I haven't done an insert in so long I have opened an SR - this may confirm the theory.

>

> Joel Patterson

> Database Administrator

> 904 928-2790

>


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



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

Received on Thu Mar 21 2013 - 17:46:24 CET