Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 98180 invoked from network); 2 Jan 2009 20:32:41 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 2 Jan 2009 20:32:41 -0000 Received: (qmail 60070 invoked by uid 500); 2 Jan 2009 20:32:40 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 60038 invoked by uid 500); 2 Jan 2009 20:32:40 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 60029 invoked by uid 99); 2 Jan 2009 20:32:40 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Jan 2009 12:32:40 -0800 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of cruxic@gmail.com designates 72.14.220.158 as permitted sender) Received: from [72.14.220.158] (HELO fg-out-1718.google.com) (72.14.220.158) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Jan 2009 20:32:33 +0000 Received: by fg-out-1718.google.com with SMTP id l27so4343631fgb.43 for ; Fri, 02 Jan 2009 12:32:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:in-reply-to:mime-version:content-type:references; bh=h3CoQrZqdEOqlJk+aa5uLfjT3+1vy/buAr/rgAbj6iE=; b=hH85Pz0La0WdbeaKMWbxhGDBsHI6eQX+KAE68vAdwuT32gPK6TLcw0LHYr1pFWXmoR IzpoHzi1BYdA7EI3K5fp7rUGTXALhU/dS/yD1MrWE+89i7j8WH8ksBcp7/hEjzztr3nF qHMcIwqFQMRJuyUmIxKOIsRnOcZIe+KnYp5QM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version :content-type:references; b=Si9Wx7jzripxFPc14nVtNk+GiAfOOBQO5xPz1ccy2MrazS4euPS1lZHRvXv9xp9Udl XxfR/Q/bwHfsX+XaTnXghbid2N7e5ElZ//xcndRdcuBhY6cDvkwSNsM42lYVvvgVnVxL md9rc+/zAkmOzGSj93u5TZpn1hyupJuAQ30jU= Received: by 10.86.72.3 with SMTP id u3mr10665971fga.1.1230928332469; Fri, 02 Jan 2009 12:32:12 -0800 (PST) Received: by 10.86.99.5 with HTTP; Fri, 2 Jan 2009 12:32:12 -0800 (PST) Message-ID: Date: Fri, 2 Jan 2009 12:32:12 -0800 From: "Adam B" To: "Derby Discussion" Subject: Re: timeouts and long-running transactions In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_207906_14535969.1230928332451" References: X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_207906_14535969.1230928332451 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks for the info Knut! After enabling those diagnostic properties I found that the big transaction was, in fact, updating the table in question. On Fri, Jan 2, 2009 at 5:55 AM, Knut Anders Hatlen wrote: > Adam B writes: > > > Hello all, > > > > I'd like to confirm that the behavior I'm seeing is expected: > > > > We have a multithreaded application against an embedded derby database > > (10.4). While one thread is chugging away doing many thousands of > inserts > > inside a transaction other threads are getting SQLExceptions about "a > lock > > could not be obtained withing the time requested". The exceptions > > consistently happen trying to do a SELECT from a table that is not > modified by > > the big transaction in the other thread so I don't think we are dealing > with a > > deadlock. If it matters, we are using the default isolation level: > > TRANSACTION_READ_COMMITTED. > > > > I did some searching and found this in the derby documentation: > > > > Even if a transaction is not involved in a deadlock, it might have to > wait > > a considerable amount of time to obtain a lock because of a > long-running > > transaction or transactions holding locks on the tables it needs. > > [ > http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts89097.html] > > > > Is it true that a long-running transaction will essentially lock the > entire > > database? > > No, it will only lock rows in the tables that it accesses. SELECT > operations against tables that the long-running transaction doesn't > touch, should not run into lock conflicts with the long-running > transaction. > > Running your application with derby.locks.monitor=true, > derby.locks.deadlockTrace=true and derby.language.logStatementText=true > will give you more information about which transactions are involved in > the lock conflict and which statements they have executed. > > Hope this helps, > > -- > Knut Anders > ------=_Part_207906_14535969.1230928332451 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks for the info Knut!  After enabling those diagnostic properties I found that the big transaction was, in fact, updating the table in question.


On Fri, Jan 2, 2009 at 5:55 AM, Knut Anders Hatlen <Knut.Hatlen@sun.com> wrote:
Adam B <cruxic@gmail.com> writes:

> Hello all,
>
> I'd like to confirm that the behavior I'm seeing is expected:
>
> We have a multithreaded application against an embedded derby database
> (10.4).  While one thread is chugging away doing many thousands of inserts
> inside a transaction other threads are getting SQLExceptions about "a lock
> could not be obtained withing the time requested".  The exceptions
> consistently happen trying to do a SELECT from a table that is not modified by
> the big transaction in the other thread so I don't think we are dealing with a
> deadlock.  If it matters, we are using the default isolation level:
> TRANSACTION_READ_COMMITTED.
>
> I did some searching and found this in the derby documentation:
>
>     Even if a transaction is not involved in a deadlock, it might have to wait
>     a considerable amount of time to obtain a lock because of a long-running
>     transaction or transactions holding locks on the tables it needs.
>     [http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts89097.html]
>
> Is it true that a long-running transaction will essentially lock the entire
> database?

No, it will only lock rows in the tables that it accesses. SELECT
operations against tables that the long-running transaction doesn't
touch, should not run into lock conflicts with the long-running
transaction.

Running your application with derby.locks.monitor=true,
derby.locks.deadlockTrace=true and derby.language.logStatementText=true
will give you more information about which transactions are involved in
the lock conflict and which statements they have executed.

Hope this helps,

--
Knut Anders

------=_Part_207906_14535969.1230928332451--