See comments inline.
Eric Lemoine <[EMAIL PROTECTED]> wrote:
2005-07-18 10:04:42.945 GMT Thread[DRDAConnThread_3,5,main] (XID =
15355871), (SESSIONID = 6), (DATABASE = Ganymede), (DRDAID =
AC10C102.OE9F-448669991195791140{7}), Cleanup action starting
2005-07-18 10:04:42.945 GMT Thread[DRDAConnThread_3,5,main] (XID =
15355871), (SESSIONID = 6), (DATABASE = Ganymede), (DRDAID =
AC10C102.OE9F-448669991195791140{7}), Failed Statement is: DELETE FROM
hourlymeasures WHERE time < 1121681048 AND mo IN (SELECT id FROM
managedobjects WHERE node=1)
ERROR 40001: A lock could not be obtained due to a deadlock, cycle
of locks & waiters is:
Lock : ROW, HOURLYMEASURES, (3,229)
Waiting XID : {15355871, U} , GANYMEDE, DELETE FROM hourlymeasures
WHERE time < 1121681048 AND mo IN (SELECT id FROM managedobjects WHERE
node=1)
Granted XID : {15345560, X}
Lock : ROW, HOURLYMEASURES, (78,164)
Waiting XID : {15345560, U} , GANYMEDE, DELETE FROM hourlymeasures
WHERE time < 1121681045 AND mo IN (SELECT id FROM managedobjects WHERE
node=3)
Granted XID : {15355871, U}
The selected victim is XID : 15355871
---
I understand from this trace that that XID 15355871 is trying to grab
a lock on entry (3,229), which is already grabbed by XID 15345560.
And, at the same time, XID 15345560 is trying to grab a lock on entry
(78,164), which is already grabbed by XID 15355871. Is my
understanding correct?
Yes.
Why does the Derby code let a transaction try to grab a lock while it
already holds on another one? Reading the Developper's Guide I
understand that the transaction should release (or downgrade it to
"shared" [*]) an update lock on a row when going to the next row. To
me this is in contradiction with the above trace. Can anyone shed
so light here?
A transaction will keep all its lock until it commits, at least all
its exclusive locks. Since you are using Uncommitted Read isolation
level the transaction should release the update locks when it has
decided that it will not do an update to the record as you said.
In the example above you see one transaction (15355871) that holds one
Update lock while it tries to grab one more Update lock. So why didn't
this transaction not release the first Update lock before trying to
grab a new Update lock?
I am not sure of the exact answer, but I think
this might have to do with that Derby will keep the first Update lock
until it actually have acquired the next lock (and then release it).
(I am sending a CC to two other members in the team who might have a
better answer to this.)
