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:[email protected]
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)
----------------------------------------------------------------------
