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: pass (asf.osuosl.org: domain of ksunithaghm@gmail.com designates
 64.233.162.204 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
        b=i9kdngTnuozcAshxMjSzhnsoVvhwSQ7H+fjQhHfcWkx8W/KoyxaIY/aycSp91y+Mf6adk14mSN7Gm4Bd/sPEVTKGOFmRaaA9vMDBFbm8O9FQCm3k8XPow46WFquPSz90KfzzAP80AXs3JOd97BsW0lVkkt/Hk0zwvB4DXNbCKDg=
Message-ID: <433C29CC.2040306@gmail.com>
Date: Thu, 29 Sep 2005 10:52:12 -0700
From: Sunitha Kambhampati <ksunithaghm@gmail.com>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Read+write deadlock in default Derby
References: <1127989721.17797.251.camel@pc977.sb.statsbiblioteket.dk>
In-Reply-To: <1127989721.17797.251.camel@pc977.sb.statsbiblioteket.dk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Lars Clausen wrote:

>Hi!
>
>I was under the impression that by default Derby does not take locks
>when reading, so deadlocks should only occur when two updates collide. 
>However, I had the exception below this morning:
>  
>
>Caused by: SQL Exception: A lock could not be obtained due to a deadlock, cycle
>of locks and waiters is:
>Lock : ROW, DOMAINS, (346,20)
>  Waiting XID : {3556682, S} , APP, SELECT domains.name, configurations.name
>FROM domains, configurations, harvest_configs WHERE harvest_id = ?  AND
>configurations.config_id = harvest_configs.config_id  AND
>configurations.domain_id = domains.domain_id
>  Granted XID : {3556738, X}
>Lock : ROW, CONFIGURATIONS, (410,270)
>  Waiting XID : {3556738, X} , APP, UPDATE configurations SET comments = ?,
>template_id = ( SELECT template_id FROM ordertemplates WHERE name = ? ),
>maxobjects = ?, maxrate = ?, overridelimits = ?WHERE name = ? AND domain_id = ?
>  Granted XID : {3556682, S}
>. The selected victim is XID : 3556682.
><
>  
>
<snip>

>The first lock shown (SELECT domains.name, ...) is inside an entirely
>read-only function, so it is strange that it should deadlock.  We are
>using default settings for Derby.  Is there any way this could actually
>be caused by that read, or should I really go around looking for an
>unfinished transaction somewhere before that select?
>
>  
>
Read acquire shared locks except for read uncommitted.  From the manual 
http://db.apache.org/derby/docs/10.1/devguide/cdevconcepts842304.html

"When a statement reads data without making any modifications, its 
transaction obtains a /shared lock/ on the data. Another transaction 
that tries to read the same data is permitted to read, but a transaction 
that tries to update the data will be prevented from doing so until the 
shared lock is released. How long this shared lock is held depends on 
the isolation level of the transaction holding the lock. Transactions 
using the TRANSACTION_READ_COMMITTED isolation level release the lock 
when the transaction steps through to the next row. Transactions using 
the TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ isolation 
level hold the lock until the transaction is committed, so even a SELECT 
can prevent updates if a commit is never issued. Transactions using the 
TRANSACTION_READ_UNCOMMITTED isolation level do not request any locks."

My guess is -it seems like there is  a conflict between the locks 
necessary on configuration table.

To help debug this, please check out the faq -  
http://db.apache.org/derby/faq.html#debug_lock_timeout .  This will 
print the lock table info in derby.log and you can see what locks are 
held by which transaction.

Hope this helps,
Sunitha.