From: D'Hooge Freek <Freek.DHooge@uptime.be>
To: "oracle-l@freelists.org" <oracle-l@freelists.org>
Date: Thu, 21 Mar 2013 17:02:44 +0100
Subject: [Fwd: Re: Deadlock inserting into same rowid (different block)]
Thread-Topic: [Fwd: Re: Deadlock inserting into same rowid (different block)]
Message-ID: <1363881764.2948.86.camel@dhoogfr-lpt1>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: Freek.DHooge@uptime.be

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