db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Question on SELECT .. WITH <isolation> semantics/locking
Date Wed, 14 Mar 2007 17:40:25 GMT
I don't know much about what the language setting does, I didn't
realize we still had these non standard settings.
 From what I know about the underlying lock implimentation if you
set repeatable read on a statement then locks are going to be
held until end transaction.

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

View raw message