db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Parsing lockTable dumps
Date Wed, 02 Nov 2005 16:56:45 GMT

Lars Clausen wrote:
> On Wed, 2005-11-02 at 13:34, Knut Anders Hatlen wrote:
>>Lars Clausen <lc@statsbiblioteket.dk> writes:
>>>On Tue, 2005-11-01 at 15:36, Knut Anders Hatlen wrote:
>>>>Lars Clausen <lc@statsbiblioteket.dk> writes:
>>>>>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
>>>>>me that the 20963 XID has a shared table lock while the 20965 XID has
>>>>>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
>>>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
>>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
>>      back.
>>   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.

I am not sure that is what the lock table is saying, as I don't 
understand exactly what the application is doing.  During read
committed read locks will exist and one will be able to view them
in the lock table.

As another posting said, this is a lock timeout not a deadlock.  The
message will say deadlock if it is a deadlock.

The lock table is saying there are 2 transactions (20965 and 20963),
which should correspond to 2 connections in your program.
transaction 20965 has a S row lock on     (4, 105) (page 4, row id 105)
transaction 20965 is waiting on X lock on (4, 105)
transaction 20963 has a S row lock on     (4, 105)

20963 has locks outstanding on 4 tables, is that a single join or
4 concurrent open result sets?  If it is 4 concurrent result sets
then autocommit is almost always a bad idea.

So for some reason 20963 is holding the lock on the order of a minute
causing timeout of 20965.  The question is why.  Some possible

1) 20963 is some sort of gui sitting waiting for input before moving
    off the row.
2) 20963 did not close the statement so derby does not know it really
    wanted to "move off the row".
3) as has been suggested you are somehow not running in read committed
    mode, I don't know of any situation where Derby will change this
    if you don't ask it to.  You can use the available properties to
    get query plans for each statement to be printed to the derby.log -
    these query plans will include the isolation level.  See the 
4) there is some sort of undetected deadlock in your program that 
involves derby locking and some other wait mechanism.  The 2 most common
mistakes are:
     1) use 2 connections but only one thread.  Connection 1 obtains 
locks, Connection 2 waits inside Derby on the lock for Connection 1 to
give up it's lock but the code to execute another jdbc call for
Connection 1 can never run as it needs the call from Connection 2 to
come back.
     2) 2 threads, 1 connection in both threads.
        Connection 1 hold lock A.
        Connection 2 gets java synchronization on object A or routine A
        Connection 2 calls Derby and waits on lock A
        Connection 1 continues, and wants to do something that will 
release lock on A, but never can because if first needs java 
synchonization on object A or routine A.
> Curious.  I am not setting the isolation level programmatically, and
> have no derby.properties file setting it either, so it should be read
> committed.  How do you tell from the table what level we're at?  Is
> there some description of the fields somewhere?  It wouldn't be that
> Connection.setAutocommit() can change the isolation level, would it?
> -Lars

View raw message