db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English ...@brighton.ac.uk>
Subject Re: Locking problem
Date Wed, 06 Oct 2010 18:27:11 GMT
On 06/10/2010 14:57, Bryan Pendleton wrote:
> It does take a bit of practice to learn how to read the information,
> although I suspect you're not looking for basic introductory info
> at this point. Still, this is a place to start:
>
> http://wiki.apache.org/db-derby/LockDebugging

OK, thanks. I've had a look at the IRC debugging walkthrough that this
page links to and I'd really like to get info like it describes:

06-07-07 03:29:01.011 GMT Thread[DRDAConnThread_33,5,main] (XID = 
1016836), (SESSIONID = 31), (DATABASE = tradeDB), (DRDAID = 
NF000001.A636-867504725979599249{31}), Failed Statement is: select 
q1."PURCHASEPRICE", q1."HOLDINGID", q1."QUANTITY", q1."PURCHASEDATE", 
q1."ACCOUNT_ACCOUNTID", q1."QUOTE_SYMBOL" from HOLDINGEJB q1, ACCOUNTEJB 
q2, ACCOUNTPROFILEEJB q3 where ( q3."USERID" = ?) and ( q2."ACCOUNTID" = 
q1."ACCOUNT_ACCOUNTID") and ( q3."USERID" = q2."PROFILE_USERID") with 1 
parameters begin parameter #1: uid:217 :end parameter ERROR 40XL2: A 
lock could not be obtained within the time requested. The lockTable dump 
is: ...

What spell do I need to cast to get this level of debug info?

> Another thing you might try is to run your SQL statements outside
> of their triggers, and study their locking behavior in isolation first.

OK.

> I definitely don't understand the excerpt of the lock table that
> you included -- is it possible to enclude the entire output?

Can do, but...

> The excerpt that you showed seemed to show transaction 188504168 waiting
> for a shared row lock on row (2,6), but it didn't show anyone *holding*
> a lock on row (2,6) -- the other locks you showed were on other rows.

Sorry -- there is indeed a (2,6) lock, just like the other ones:
   188504167 |ROW |X |1|(2,6)|GRANT|T|NULL|USERS

> Was transaction ...167 indeed locking every single row in the users
> table in exclusive mode, one at a time? Do you have such a transaction
> in your program? If you look in the full table, do you see an entry for
> transaction ...167 holding row (2,6) in exclusive mode?

It looks like it was. I have no idea why, but I will continue to puzzle
over it all.

I normally leave autocommit turned on, but in a few places in my code I
wrap multiquery transactions like this:

   try {
     setAutoCommit(false);
     ...    // do a bunch of related updates
   }
   finally {
     commit();
     setAutoCommit(true);
   }

Is this the right way to do it, and would the behaviour I see have to be
caused by a multi-query transaction of this sort (since the locks all
have the same XID)? If so, it would narrow down the things I need to
look at...

Many thanks for your advice!

----------------------------------------------------------------------
  John English              | mailto:je@brighton.ac.uk
  Senior Lecturer           | http://www.it.bton.ac.uk/staff/je
  School of Computing & MIS | "Those who don't know their history
  University of Brighton    |  are condemned to relive it" (Santayana)
----------------------------------------------------------------------

Mime
View raw message