Return-Path: 
 <derby-dev-return-38632-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 98500 invoked from network); 15 Mar 2007 01:54:44 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 15 Mar 2007 01:54:44 -0000
Received: (qmail 91685 invoked by uid 500); 15 Mar 2007 01:54:52 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 91654 invoked by uid 500); 15 Mar 2007 01:54:52 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 91645 invoked by uid 99); 15 Mar 2007 01:54:52 -0000
Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 14 Mar 2007 18:54:52 -0700
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=UNPARSEABLE_RELAY
X-Spam-Check-By: apache.org
Received-SPF: pass (herse.apache.org: local policy)
Received: from [192.18.1.36] (HELO gmp-ea-fw-1.sun.com) (192.18.1.36)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 14 Mar 2007 18:54:40 -0700
Received: from d1-emea-09.sun.com ([192.18.2.119])
	by gmp-ea-fw-1.sun.com (8.13.6+Sun/8.12.9) with ESMTP id l2F1sI90005051
	for <derby-dev@db.apache.org>; Thu, 15 Mar 2007 01:54:18 GMT
Received: from conversion-daemon.d1-emea-09.sun.com by d1-emea-09.sun.com
 (Sun Java System Messaging Server 6.2-6.01 (built Apr  3 2006))
 id <0JEX00E01939EZ00@d1-emea-09.sun.com>
 (original mail from Dag.Wanvik@Sun.COM) for derby-dev@db.apache.org; Thu,
 15 Mar 2007 01:54:18 +0000 (GMT)
Received: from khepri05.norway.sun.com ([129.159.112.194])
 by d1-emea-09.sun.com
 (Sun Java System Messaging Server 6.2-6.01 (built Apr  3 2006))
 with ESMTPSA id <0JEX004TV9AH1120@d1-emea-09.sun.com>; Thu,
 15 Mar 2007 01:54:18 +0000 (GMT)
Date: Thu, 15 Mar 2007 02:54:17 +0100
From: Dag.Wanvik@Sun.COM (Dag H. Wanvik)
Subject: Re: Question on SELECT .. WITH <isolation> semantics/locking
In-reply-to: <45F83389.2020508@sbcglobal.net>
Sender: Dag.Wanvik@Sun.COM
To: derby-dev@db.apache.org
Cc: mikem_app@sbcglobal.net
Message-id: <x1oxhcsn8e5y.fsf@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <x1oxmz2fn816.fsf@sun.com> <45F83389.2020508@sbcglobal.net>
User-Agent: Gnus/5.1008 (Gnus v5.10.8) Emacs/22.0.50 (usg-unix-v)
X-Virus-Checked: Checked by ClamAV on apache.org

Mike Matrigali <mikem_app@sbcglobal.net> writes:

> So I think this is a documentation issue, or an interpretation
> issue as when I read it I think it describes what you are seeing.
>   I believe it is meant
> to say that the semantics of that statement will be repeatable
> read, while the semantics of the next statement are not affected.
> The semantics of repeatable read mean that locks are held until
> end of transaction not statement, so for the statement that you
> set repeatable read locks will be held until end transaction.

I agree this is the most reasonable reading of the docs, so I think we
are in good shape. 

Thanks, Mike!

Dag

> I am not sure how to actually describe what it means with
> respect to sql standard to have multiple statements in a single
> transaction having different isolation levels and really describing
> the guarantee.
>
> Dag H. Wanvik wrote:
>> Derby has a non-standard syntax to specify isolation level for a
>> single SELECT statement:
>> Ref.man: SELECT statement: Syntax:
>> <Query> [ORDER BY <clause>] [FOR UPDATE <clause>] WITH {RR|RS|CS|UR}
>> The manual states:
>> "You can set the isolation level in a SELECT statement using the
>> WITH
>> {RR|RS|CS|UR} syntax."
>> and the following example has this comment:
>> "-- set the isolation level to RR for this statement only"
>> When i try "WITH RS" (=repeatable read) on a SELECT inside a
>> transaction with isolation level READ COMMITTED, i can see that shared
>> locks (or update locks if FOR UPDATE is used) are kept when moving off
>> rows as as one would expect for RS when a cursor visits the rows in
>> the query.
>> However, when I close the cursor/result set, it seems the locks are
>> still in place - it seems they are kept till the end of the
>> transaction. The wording of the semantics seems to indicate that there
>> is no such guarantee ("isolation level in statement"). Is this by
>> design, or is it a bug?
>> It seems this behavior could be a useful in certain scenarios
>> (although one could keep the cursor open to achieve the effect) so I
>> am wondering if this is by design. If so, maybe the wording in the
>> manual should be a bit clearer.
>> If someone can shed light on this I would appreciate it.
>> Thanks,
>> Dag
>> 
>

-- 
Dag H. Wanvik
Sun Microsystems, Database Technology Group (DBTG)
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax:  +47 73842101