db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Parsing lockTable dumps
Date Wed, 02 Nov 2005 12:34:40 GMT
Lars Clausen <lc@statsbiblioteket.dk> writes:

> 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
>> > -------------------------------------------------------------------------------------------------------------------------------------------------------------
>> > *** 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.

This depends on the isolation level:

   1) In read uncommitted mode, both shared and exclusive locks are
      released after you have finished the operation on a row.

   2) In read committed mode (default in Derby), shared locks are
      released after you have finished reading the row, but exclusive
      locks are kept until the transaction is committed or rolled

   3) In repeatable read mode, both shared and exclusive locks are
      kept until the transaction has finished.

   4) Serializable mode is like repeatable read, but additionally
      prevents so-called phantom reads.

If you are using the default isolation level, shared locks should not
be kept until the next commit. The lock table indicates that you are
running with repeatable read or serializable.

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

Knut Anders

View raw message