db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: Question on SELECT .. WITH <isolation> semantics/locking
Date Thu, 15 Mar 2007 01:54:17 GMT
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!


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

View raw message