db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ecker Sandor <esa...@freemail.hu>
Subject Re: derby (dead)lock exception
Date Mon, 12 Nov 2012 08:54:46 GMT
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 <bpendleton.derby@gmail.com> í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>


Mime
View raw message