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

Joel,

On rac, each node has it's own set of cached sequence numbers. E.g node 1 has sequence numbers 1 to 500 and node 2 has sequence numbers 501 to 1000.
So neither rac nor caching should be a source of duplicate sequence numbers.
The caching has also no direct relation to the application servers as it is just a memory heap in the shared pool.

Or aren't we talking about oracle db sequences but some kind of hibernate (man, I hate that product) sequences like SequenceGenerator? I did some googling and following might be a source of duplicate values with rac and SequenceGenerator:
http://stackoverflow.com/questions/7171626/hibernate-with-oracle-sequence-doesnt-use-it

Kind regards,

-- 
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 17:46 +0100, Patterson, Joel wrote: 


> 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