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:[email protected]] > 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:[email protected]] On Behalf Of > [email protected] > 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:[email protected]] > > Sent: Thursday, February 19, 2009 8:06 AM > > To: Derby Discussion; [email protected] > > 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:[email protected]] On Behalf Of > > [email protected] > > 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:[email protected]] > > > Sent: Thursday, February 19, 2009 7:32 AM > > > To: Derby Discussion; [email protected] > > > 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:[email protected]] On Behalf Of > > > [email protected] > > > 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:[email protected]] > > > > 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 > > > > > > > > >
