db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Robert J. Carr" <rjc...@gmail.com>
Subject Re: locking problem
Date Tue, 21 Jul 2009 22:11:01 GMT
Hi Knut-

Thanks for the quick reply, and sorry to burden everyone, but I have a
quick follow-up:

> Yes, (2,43) tells you the page number (2) and the row number (43)

Could you explain what the page number represents?

> That's simple. :) Just don't commit your transactions:

I did have a handful of setAutoCommit() methods but I removed all
traces of them and I get the same problem in the same exact way.  I
also logged the auto commit status and before the problem occurs it is
set to TRUE.  Can you think of anything else that may cause a lock in
this way?

Thanks!

On Tue, Jul 21, 2009 at 2:34 PM, Knut Anders Hatlen<Knut.Hatlen@sun.com> wrote:
> "Robert J. Carr" <rjcarr@gmail.com> writes:
>
>> Hi Knut, et al.-
>>
>> So now, with your help, I can see the exact statement that is failing,
>> with the exact parameters that are being passed into the prepared
>> statement.  However, this is only giving me the "where", but I need to
>> know the "how" and more importantly the "why".
>>
>> Superficially, the statement parameters don't look any different than
>> any other time it is used.  Nor are there any logs around the failed
>> statement to give any clue as to what is going wrong.  The only
>> difference is there is a log that says "Cleanup action starting" and
>> then there's a subsequent log that says "Failed Statement is:" and
>> gives the failed statement.
>>
>> Again, in the lock table dump, all I'm seeing is this:
>>
>> *** The following row is the victim ***
>> 9526      |ROW          |X   |0        |(2,43)  |WAIT |T |NULL  |SG_TRACKS
>> *** The above row is the victim ***
>>
>> Maybe the (2,43) is a clue?  Does this have anything to do with the
>> row and column numbers?
>
> Yes, (2,43) tells you the page number (2) and the row number (43) within
> that page. So what you should be looking for in the lock table dump is
> other occurrences of row locks on (2,43) in the SG_TRACKS table. Then
> look in derby.log for statements with the same transaction id as those
> occurrences. This should give you a clue as to where in your code the
> locks are held.
>
>> My point is, I have a lot of logging information and stack traces, but
>> still nothing is telling me why this happened.  Can someone explain
>> how derby can even get into a WAIT ROW LOCK without involving
>> concurrency?
>
> That's simple. :) Just don't commit your transactions:
>
> Connection c1 = DriverManager.getConnection("jdbc:derby:db");
> c1.setAutoCommit(false);
> c1.createStatement().execute("update t set x = x + 1");
> Connection c2 = DriverManager.getConnection("jdbc:derby:db");
> c2.createStatement().execute("update t set x = x - 1"); // will time out
>
> --
> Knut Anders
>

Mime
View raw message