db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jørgen Løland <Jorgen.Lol...@Sun.COM>
Subject Re: Updating rows with an open cursor...what is the expected behavior?
Date Wed, 13 Jun 2007 08:32:51 GMT
Army wrote:
> The remaining question is whether or not this is correct.  I'm hoping 
> Knut Anders is right when he wrote:
> knut> there's no guarantee (I think) as to when the rows are actually read.
> knut> But I'll leave it to the scholars to find the chapter and verse in 
> the
> knut> appropriate spec... ;)
> That's what I'm looking for :) 

SQL-2003 states the following for cursors and updates (Chp 4.32.2. 
Operations on and using cursors):

If a cursor is open, and the SQL-transaction in which the cursor was 
opened makes a significant [1] change to SQL-data, then whether that 
change is visible through that cursor before it is closed is determined 
as follows:
- If the cursor is insensitive, then significant changes are not visible.
- If the cursor is sensitive, then significant changes are visible.
- If the cursor is asensitive, then the visibility of significant 
changes is implementation-dependent.

[1] significant meaning update performed by other commands than <update 
statement: posisioned> that would have had affect if commited before the 
cursor was opened.

Hence, the behavior described by Army is correct iff the cursor is 
defined as asensitive. Further more:

Chp 14.1 (declare cursor):
5) If <cursor sensitivity> is not specified, then ASENSITIVE is implicit.

My understanding is therefore that the differences between index and 
scan is allowed by SQL 2003. An interesting question is what Derby does 
if the cursor is specified to be either sensitive or insensitive...

  Note that I was able to reproduce this
> behavior with a JDBC program, inlined at the end of this email.  That 
> program uses the Connection.createStatement() method, which means that 
> result sets from that statement should default to TYPE_FORWARD_ONLY (as 
> So it seems like the question is: should a "forward only" result set be 
> sensitive to updates to rows that it hasn't read yet?  Or is this 
> documented somewhere in JDBC as "not guaranteed" (or whatever the 
> correct term is)?  Unless there is doc saying that such a thing leads to 
> undefined behavior (per Knut Anders' suggestion), it seems odd (to me) 
> that the "sensitivity" of a forward-only result set apparently depends 
> on the underlying Derby scan type...

As defined in SQL 2003, ASENSITIVE is the default sensitivity.

Jørgen Løland

View raw message