db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendleton.de...@gmail.com>
Subject Re: derby (dead)lock exception
Date Sat, 10 Nov 2012 15:51:51 GMT
> . 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

Mime
View raw message