Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of bpendleton.derby@gmail.com
 designates 209.85.210.172 as permitted sender)
MIME-Version: 1.0
In-Reply-To: <freemail.20121109125443.3174.1@xmldata09.freemail.hu>
References: <freemail.20121109125443.3174.1@xmldata09.freemail.hu>
Date: Sat, 10 Nov 2012 07:51:51 -0800
Message-ID: 
 <CANi-yg8NVXYkrP+waS99fz7=n05JStHVE5gZRkGZ4W3qSWUU=A@mail.gmail.com>
Subject: Re: derby (dead)lock exception
From: Bryan Pendleton <bpendleton.derby@gmail.com>
To: Derby Discussion <derby-user@db.apache.org>
Content-Type: text/plain; charset=ISO-8859-1

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