db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: [jira] Commented: (DERBY-690) Add scrollable, updatable, insensitive result sets
Date Wed, 01 Mar 2006 17:41:28 GMT
Andreas Korneliussen wrote:

> Daniel John Debrunner wrote:
> 
>> Bernt M. Johnsen wrote:
>>
>>
>>
>>> We should also strive to make "insensitivity" as close to the SQL
>>> defintion as possible (SQL 2003 p. 96):
>>>
>>>   A change to SQL-data is said to be independent of a cursor CR if
>>>   and only if it is not made by an <update statement: positioned> or a
>>>   <delete statement: positioned> that is positioned on CR.
>>>
>>>   A change to SQL-data is said to be significant to CR if and only if
>>>   it is independent of CR, and, had it been committed before CR was
>>>   opened, would have caused the table associated with the cursor to
>>>   be different in any respect.
>>>
>>>   A change to SQL-data is said to be visible to CR if and only if it
>>>   has an effect on CR by inserting a row in CR, deleting a row from
>>>   CR, changing the value of a column of a row of CR, or reordering
>>>   the rows of CR.
>>>
>>>   [...]
>>>
>>>   - If the cursor is insensitive, then significant changes are not
>>> visible.
>>
>>
>>
>> Does JDBC's definition of INSENSITIVE line up with SQL's?
>>
>> JDBC 3.0 (14.1.1) (and JDBC 4.0 16.1.1)
>>
>>> The result set is insensitive to changes made to the underlying data
>>> source while
>>> it is open. It contains the rows that satisfy the query at either the
>>> time the query is
>>> executed or as the rows are retrieved.
>>
>>
>>
>> SQL seems to say that if an update happens while the cursor is open then
>> an insensitive cursor will not see it.
>>
>> JDBC says you might see it, due to the "as the rows are retrieved".
>>
> 
> 
> I interpreted the JDBC a bit differently: it just says which rows are in
> the resultset, not if you see the updates.
> 
> 
>  - JDBC 3.0 spec: "The result set is insensitive to changes made to the
>  underlying data source while it is open. It contains the rows that
>  satisfy the query at either the time the query is executed or as the
>  rows are retrieved."
> 
> To me, this says more about which rows are in the resultset - if they
> satisfy the query at execute time, or at retrieve time, they may go into
> the resultset.

Not sure what you are trying to say here.

Here's an example of what I'm saying.

I have a query,

SELECT ID,PRICE FROM T WHERE PRICE < 100.0

I open (execute) a scrollable insensitive JDBC result set on it,
isolation level read-committed or repeatable read.

Let's say at the time the query is executed, the complete set
of rows that qualify for the query is:

1, 10.0
8, 76.0
2, 45.0
7, 95.0
3, 45.0

That's just the rows that would qualify at that point in time,
I'm not saying they are in the ResultSet.

I fetch the first 3 rows into my ResultSet.

1, 10.0
8, 76.0
2, 45.0

and pause

Another transaction now executes a price change and commits.

UPDATE PRICE SET PRICE = PRICE + 10.0 where ID IN (3, 7)

Now, I think JDBC definition of insensitive
"The result set is insensitive to changes made to the underlying data
source while it is open. It contains the rows that satisfy the query at
either the time the query is executed or as the rows are retrieved."

 allows the driver to fetch rows on-demand, the "or as the rows are
retrieved" portion, thus I believe it's valid in JDBC to allow the
resulting fetches to only return a single row:

3,55.0

thus leading to a complete scrollable ResultSet that contains:

1, 10.0
8, 76.0
2, 45.0
3, 55.0

In this case the insensitive ResultSet did see "other changes" that
occurred after the execution.

Another valid implementation, which is what I think Derby does today, is
to pre-fetch all the rows, leading to a ResultSet that "satisfy the
query at [either] the time the query is executed". Namely

1, 10.0
8, 76.0
2, 45.0
7, 95.0
3, 45.0


Dan.






Mime
View raw message