Hi Bryan, Thank You for answering. This is a deadlock for sure.
The related docs from derby: http://db.apache.org/derby/docs/10.9/devguide/ http://db.apache.org/derby/docs/10.9/devguide/cdevconcepts30291.html http://db.apache.org/derby/docs/10.9/devguide/cdevconcepts28436.html Maybe our queries are not correctly formulated, and the row level locks are propagated to table locks... Possible solutions: -try to understand what is wrong with the query, and fix To start with this: we could try to execute the problematic query by hand, in separate transactions and find out how the system behave... -shorten the live of locks (this don't fix the problem, just it will likely not appear so often) by execute the delition in separate transaction Regards, Sandor Bryan Pendleton <[email protected]> írta: >> . What does U and X mean after the trans. Ids?> >> > The query is:> >> > DELETE FROM TRIP_TIMETABLE> > WHERE EXISTS(> > SELECT ID FROM TRIP WHERE (ID IN (?)) AND ID = > TRIP_TIMETABLE.trips_ID> > )> >> > What can be wrong with this query?> > I'm not exactly sure what's wrong, but I agree that they both> appear to have gone after the same set of rows, but in> different orders. I think that the '1,19' and '1,20' are row identifiers;> the 19th row in table 1 and the 20th row in table 1, or something like that.> > Apparently there were two timetables for a certain trip, and one transaction> was trying to delete timetable 19, then timetable 20, while the other was> trying to delete timetable 20, then timetable 19.> > U and X are lock modes. U means: "I'm reading this row and intend to update it",> while X means: "I'm updating this row."> > "Updating" can be any of: insert, update, delete; in your case it is delete.> > One way that I've used to get around problems like this in the past> is a bit of a sledgehammer: immediately prior to the DELETE statement,> but within the same transaction, you can do:> > LOCK TABLE TRIP IN EXCLUSIVE MODE> LOCK TABLE TRIP_TIMETABLE IN EXCLUSIVE MODE.> > This technique works best if you adjust your code so that these> three statements (the 2 LOCK TABLE statements, and the DELETE> statement) are the only three statements in the transaction; that is,> commit immediately before and immediately after this.> > Hope this gives you some clues.> > bryan>
