db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mikael Sundberg" <Mikael.Sundb...@artificial-solutions.com>
Subject RE: Foreign keys cuases deadlock?
Date Thu, 19 Feb 2009 18:20:41 GMT
Hi again
While createing a general test from my code i realised that for some
reasone there was some static references that made the second inserts
actually go in another connection/session. So removing that very
unnecessary static reference made it work.
Thanks for the help anyway :-). 

-----Original Message-----
From: Michael Segel [mailto:msegel@segel.com] On Behalf Of
derby@segel.com
Sent: den 19 februari 2009 18:34
To: 'Derby Discussion'
Subject: RE: Foreign keys cuases deadlock?


Mikael,

Standard in that what you're trying to do shouldn't fail.

Here's the classic use case. Order Entry. It's a master/detail record.

You have a header rec that contains the order number and customer
information. You then have a detail rec where each purchase detail is a
separate record in a second table. On the second table, the order_id is
the
foreign key.

So you insert the header record in to table A, then for each detail row,
you
insert it in to table B.

You don't want to auto commit because if you can't insert the entire
order
in to the system, you want to roll back the entire transaction. All or
nothing.

You should not have your deadlock issue. You should not fail.

This is why I wonder what you're doing that's different. 


Does that make sense?

-Mike


> -----Original Message-----
> From: Mikael Sundberg
[mailto:Mikael.Sundberg@artificial-solutions.com]
> Sent: Thursday, February 19, 2009 8:06 AM
> To: Derby Discussion; msegel@segel.com
> Subject: RE: Foreign keys cuases deadlock?
> 
> Standard as in "that's not expected to work" or standard as in "that
is
> used a lot and always work"?
> 
> It's the same thread.
> Yes we have a connection pool, Apache commons pool/dbcp
> 
> 
> /Micke
> 
> -----Original Message-----
> From: Michael Segel [mailto:msegel@segel.com] On Behalf Of
> derby@segel.com
> Sent: den 19 februari 2009 14:48
> To: 'Derby Discussion'
> Subject: RE: Foreign keys cuases deadlock?
> 
> 
> Ok,
> 
> This doesn't make sense.
> Its pretty much standard.
> 
> I just have to ask if you're doing this entire sequence in a single
> connection?
> 
> I mean you're not doing the insert in to table 1 in a different jdbc
> connection/thread, getting its results and then trying to use a
> different
> jdbc connection to insert in to the second table.?
> 
> Are you using some form of connection pooling or just a straight
single
> jdbc
> connection?
> 
> 
> > -----Original Message-----
> > From: Mikael Sundberg
> [mailto:Mikael.Sundberg@artificial-solutions.com]
> > Sent: Thursday, February 19, 2009 7:32 AM
> > To: Derby Discussion; msegel@segel.com
> > Subject: RE: Foreign keys cuases deadlock?
> >
> > Yes I belive that is the case.
> > The return code I get is the value of the "id" column of table1.
This
> is
> > also the column that table2 has a foreign key to.
> > It fails before the Commit. More precise at the
> > prepearedStatement.executeUpdate for the second insert.
> >
> > If im unclear I can write a testcase for it.
> > /Micke
> >
> > -----Original Message-----
> > From: Michael Segel [mailto:msegel@segel.com] On Behalf Of
> > derby@segel.com
> > Sent: den 19 februari 2009 14:21
> > To: 'Derby Discussion'
> > Subject: RE: Foreign keys cuases deadlock?
> >
> > Let me see if I have this straight.
> >
> > Auto commit is off.
> >
> > You're trying the following pseudo code...
> >
> > Begin transaction
> >   Insert into table 1
> >     Get some return code from the insert for your foreign key for
> table
> > 2
> >   Insert into table 2
> > Commit Transaction
> >
> > And this is failing?
> >
> >
> > > -----Original Message-----
> > > From: Mikael Sundberg
> > [mailto:Mikael.Sundberg@artificial-solutions.com]
> > > Sent: Thursday, February 19, 2009 7:08 AM
> > > To: Derby Discussion
> > > Subject: Foreign keys cuases deadlock?
> > >
> > > Hi I have the following problem.
> > >
> > > Im using autocommit off.
> > > I start by inserting into table1.
> > >
> > > I then want to insert into table2 that has a foreign key to table
1.
> > (I
> > > use generatedkeys from my insert)
> > > This fails since it cant obtaing a lock on table1
> > > Is that expected?
> > > If I add a commit between the two statements it works.
> > >
> > > /Micke
> >
> 
> 




Mime
View raw message