derby (dead)lock exception

[Ecker Sandor <es...@freemail.hu>]

Hi,

I'm getting the following exception.

    <-: 'deleteByIds' throws '[DatabaseException@74c9e3[
      Internal Exception: java.sql.SQLTransactionRollbackException: DERBY SQL error: SQLCODE: -1, SQLSTATE: 40001, SQLERRMC: Lock : ROW, TRIP_TIMETABLE, (1,20)
        Waiting XID : {5144, U} , TTM, DELETE FROM TRIP_TIMETABLE WHERE EXISTS(SELECT ID FROM TRIP WHERE (ID IN (?)) AND ID = TRIP_TIMETABLE.trips_ID)
        Granted XID : {5150, X} 
      Lock : ROW, TRIP_TIMETABLE, (1,19)
        Waiting XID : {5150, U} , TTM, DELETE FROM TRIP_TIMETABLE WHERE EXISTS(SELECT ID FROM TRIP WHERE (ID IN (?)) AND ID = TRIP_TIMETABLE.trips_ID)
        Granted XID : {5144, X} 
      ^T5144^T40001
      Error Code: -1

Does this mean I had a deadlock? I guess there are two transactions 5144, 5150, they execute the same query. 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?

Thanks,
Regards,
Sandor

Re: derby (dead)lock exception

[Ecker Sandor <es...@freemail.hu>]

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

Re: derby (dead)lock exception

[Bryan Pendleton <bp...@gmail.com>]

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