db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Van Couvering <David.Vancouver...@Sun.COM>
Subject [Fwd: Re: locking issues]
Date Tue, 19 Jul 2005 16:23:10 GMT
Hi, all.  We have an internal user who is having deadlock problems.  Can 
anybody on the group help Eric out here?

Eric, please do subscribe to derby-user@db.apache.org by sending an 
email to derby-user-subscribe@db.apache.org, this is a very helpful list.

Thanks,

David

-------- Original Message --------
Subject: Re: locking issues
Date: Tue, 19 Jul 2005 12:02:01 +0200
From: Eric Lemoine <Eric.Lemoine@Sun.COM>

Hi Olav,


On Tue, Jul 19, 2005 at 09:36:20AM +0200, Olav Sandstaa wrote:
> See comments inline.
> 
> Eric Lemoine <Eric.Lemoine@Sun.COM> 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.)

That's precisely my concern. If another transaction grabs the same
two Update locks in the reverse order, this is deadlock-prone.

To me this deadlock looks like a bug in the Derby server. I, as
a JDBC user, have no control on how row locking is done.

> I hope this helped. I will try to get back to you with a more detailed
> answer when I have it.

Thanks a lot Olav,

-- 
Eric


Mime
View raw message