db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lars Clausen ...@statsbiblioteket.dk>
Subject Re: Parsing lockTable dumps
Date Wed, 02 Nov 2005 11:12:05 GMT
On Tue, 2005-11-01 at 15:36, Knut Anders Hatlen wrote:
> Lars Clausen <lc@statsbiblioteket.dk> writes:
> 
> > Hi!
> >
> > Delving further into our deadlock/lock timeout problems, I now have a
> > dump of the locks in question.  I think I understand what they're
> > saying, but my interpretation doesn't make as much sense as I'd like it
> > to.  The lockTable dump is as follows:
> >
> > ERROR 40XL2: A lock could not be obtained within the time requested.  The lockTable
dump is: 
> > 2005-11-01 12:14:33.516 GMT
> > XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME    |STATE|TABLETYPE / LOCKOBJ 
|INDEXNAME / CONTAINER_ID / (MODE for LATCH only)  |TABLENAME / CONGLOM_ID  |
> > -------------------------------------------------------------------------------------------------------------------------------------------------------------
> > *** The following row is the victim ***
> > 20965     |ROW          |X   |0        |(4,105)     |WAIT |T                   
|NULL                                              |SEEDLISTS               |
> > *** The above row is the victim ***
> > 20963     |ROW          |S   |9        |(4,105)     |GRANT|T                   
|NULL                                              |SEEDLISTS               |
> > 20965     |ROW          |S   |1        |(4,105)     |GRANT|T                   
|NULL                                              |SEEDLISTS               |
> > 20965     |ROW          |S   |1        |(5,1)       |GRANT|T                   
|SEEDLISTDOMAIN                                    |SEEDLISTS               |
[...]
> > -------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > The interesting lock of course is on the SEEDLISTS table.  It appears to
> > me that the 20963 XID has a shared table lock while the 20965 XID has an
> > exclusive table lock.  That's the first thing that doesn't make sense --
> > doesn't seem like a very exclusive lock that.  Also, I would expect the
> > 20965 XID to have no problems getting an additional lock on a SEEDLISTS
> > table row, since it already has a lock on the entire table.  Could
> > somebody explain these oddities to me?  
> 
> You have misinterpreted IS and IX locks. These are intentional locks,
> which means that the transaction intends to lock rows in that
> table. For example, if a transaction wants an exclusive lock on row R1
> in table T1, it will lock table T1 with intent exclusive (IX) and row
> R1 in exclusive mode (X). This prevents others from locking the entire
> table or that particular row, but not from locking other rows in the
> table.

Thank you, that made it a lot clearer.  So for wanting to lock a row,
the intentional table locks are irrelevant.  That trims the relevant
entries to the above.  Now I got a suspicion that I want to run by you: 

I've been running the entire system with autocommit off and committing
explicitly after all updates.  Does that mean that shared locks are kept
until the next commit, even if the statements causing the locks are
close()d?  It appears likely, as switching to autocommit on for all but
the few places that requires transactions seems to cure the problem[1],
but it is not the behaviour I expected from reading the java.sql
documentation.

-Lars

[1] Seems to, as it is not quite consistently reproducable.


Mime
View raw message