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 Question on SELECT .. WITH <isolation> semantics/locking
Date Wed, 14 Mar 2007 15:45:25 GMT

Derby has a non-standard syntax to specify isolation level for a
single SELECT statement:


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.


View raw message