> . 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
