db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Derby Discussion" <derby-u...@db.apache.org>
Subject RE: Foreign keys cuases deadlock?
Date Thu, 19 Feb 2009 18:37:56 GMT
Yeah, you have to be careful about things like that. That's why I asked
about your connection type and connection pooling. ;-)

Isolation levels can also make things a bit tricky too.

HTH

-Mike


> -----Original Message-----
> From: Mikael Sundberg [mailto:Mikael.Sundberg@artificial-solutions.com]
> Sent: Thursday, February 19, 2009 12:21 PM
> To: Derby Discussion
> Subject: RE: Foreign keys cuases deadlock?
> 
> 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