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 (athena.apache.org: domain of bpendleton.derby@gmail.com
 designates 209.85.212.172 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:user-agent:mime-version:to:subject:references
         :in-reply-to:content-type:content-transfer-encoding;
        b=YBKtctzCO7ypAX4H3lzHBGgKpfdd3Gu4ye2KleHnVf50snCgLIdjJ7tl7Yu84C876O
         XSnG3giuZvB62704jsrOp20/DFJbj7r2sFPK+Uh2oY7LM4JaX6W+veTHRry/vFTErwio
         EBniMbBn25Lkvv9yyCUUWMm6Z6XmYp6rq2gxY=
Message-ID: <4CAD25FF.8080209@gmail.com>
Date: Wed, 06 Oct 2010 18:44:31 -0700
From: Bryan Pendleton <bpendleton.derby@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
 rv:1.9.1.12) Gecko/20100915 Thunderbird/3.0.8
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Locking problem
References: <4CAC6038.7020204@brighton.ac.uk> <4CAC8033.9030400@gmail.com>
 <4CACBF7F.80401@brighton.ac.uk>
In-Reply-To: <4CACBF7F.80401@brighton.ac.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

> I normally leave autocommit turned on, but in a few places in my code I
> wrap multiquery transactions like this:
>
> try {
> setAutoCommit(false);
> ... // do a bunch of related updates
> }
> finally {
> commit();
> setAutoCommit(true);
> }
>
> Is this the right way to do it, and would the behaviour I see have to be
> caused by a multi-query transaction of this sort (since the locks all
> have the same XID)? If so, it would narrow down the things I need to
> look at...

This seems like a reasonable approach to me.If you think you are somehow
accidentally leaving autocommit on-when-it-should-be-off, or vice versa,
you can sprinkle some Connection.getAutoCommit() calls around to verify
that the state is as you expect it to be.

And, yes, since the locks all have the same XID, it means that they were
all acquired without an intervening commit, so you should be analyzing
places in your program where you can process multiple rows in a single
transaction.

If you can reproduce the behavior that is problematic, another nice tool
is to turn on derby.language.logStatementText (see this page:
http://db.apache.org/derby/docs/10.6/ref/rrefproper43517.html), as that
will place a lot of useful information into the derby.log file about what's
going on, and hopefully you can read the log "backwards" from the time of
the locking problem and then figure out what was leading up to the problem
that resulted in all those other locks being held.

If you have both derby.language.logStatementText and derby.locks.deadlockTrace
enabled, your derby.log file should contain a pretty complete picture of
the circumstances that led up to the problem.

If you can post some of that information (i.e., if it isn't too sensitive),
I'm sure others will be glad to help you interpret what you see there.

thanks,

bryan