Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <4368E9CF.6030401@sbcglobal.net>
Date: Wed, 02 Nov 2005 08:31:11 -0800
From: Mike Matrigali <mikem_app@sbcglobal.net>
Reply-To: mikem_app@sbcglobal.net
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Parsing lockTable dumps
References: <1130851171.4229.34.camel@pc977.sb.statsbiblioteket.dk>
 <wjo8irvc77vc.fsf@sun.com>
 <1130929925.20959.9.camel@pc977.sb.statsbiblioteket.dk>
 <wjo84q6v6xen.fsf@sun.com>
In-Reply-To: <wjo84q6v6xen.fsf@sun.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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:
>>>
>>>
>>>>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.
> 
> 
> 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.
In read uncommitted mode no read locks are obtained.  exclusive locks
are held until end transaction in all isolation modes including read
uncommitted.
> 
>    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.
the exact timing of when the lock is released is dependent on the
exact query you are running.  Definitely when you close the resultset
there are no locks outstanding.  In almost all cases when you do
the rs.next() the lock on the previous row will be gone.  The harder
to describe cases are complicated joins involving multiple tables and
multiple indexes.

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