Some history on updatable result sets. I think all this comes from
pre-derby code - I am not sure if it is represented in the comments but
might help with understanding existing code decisions. Also this is
just from memory of changes I was not involved with.
I think we use to try and avoid index scans because of the kinds of
issues you are seeing.
1) At one point the code did not allow updates to a result set unless the
select statement had a for update clause.
2) If a select had a for update clause then we did not allow an index scan
if any column in the for update clause was in the index. Note this
meant if no column listed then all assumed updatable and thus no index
scan allowed.
Then we found too many applications assumed all select's could be
updatable, so we defaulted a result set to updatable. I don't remember
if this was an actual standard or just a defacto standard by actual J2SE
app server usage. But with rules 1
and 2 it meant that almost always we could not use an index which led
to unacceptable performance. So we allowed indexes to be used when
no for update clause is specified. I am actually not sure what we
do today if you specify the for update clause.
Army wrote:
While investigating DERBY-2805 I was trying to understand the purpose of
"ResultSetNode.markOrderingDependent()" as it is used in the
language/optimizer layer of code. In particular, see
IndexRowToBaseRowNode:
/**
* Notify the underlying result set tree that the result is
* ordering dependent. (For example, no bulk fetch on an index
* if under an IndexRowToBaseRow.)
*/
void markOrderingDependent()
{
/* NOTE: We use a different method to tell a FBT that
* it cannot do a bulk fetch as the ordering issues are
* specific to a FBT being under an IRTBR as opposed to a
* FBT being under a PRN, etc.
*/
source.disableBulkFetch();
}
As part of some testing I ran statements in ij which effectively do the
following:
1. Get a SELECT cursor on the table that specifies an ORDER BY
2. In the middle of iterating through the table, update one of the rows.
3. Continue iterating through the cursor.
To my surprise, the results of the query differs depending on whether
Derby does an Index Scan or a Table Scan on the underlying table. In
the case of an Index Scan the open cursor will see the updated row; but
in the case of a Table Scan the cursor will *not* see the updated row.
A quick look at the query plan shows that in both cases Derby is doing
"share row locking".
To see this in ij, do the following (I haven't tried this in JDBC yet...):
autocommit off;
create table str(c1 int, c2 int, c3 int);
insert into str values (1, 1, 2), (1, 2, 3), (1, 3, 4), (1, 4, 5);
create index str1 on str(c1, c2);
commit;
-- Force index scan.
get cursor c1 as
'select c2, c3 from str --DERBY-PROPERTIES index=str1
where c1 = 1 order by c2';
next c1; -- returns "1, 2"
update str set c2 = 4 where c2 = 2;
next c1; -- returns "3, 4"
next c1; -- returns "4, 3" ==> updated row is picked up
next c1; -- returns "4, 5"
next c1; -- no current row
close c1;
rollback;
-- Force table scan.
get cursor c1 as
'select c2, c3 from str --DERBY-PROPERTIES index=null
where c1 = 1 order by c2';
next c1; -- returns "1, 2"
update str set c2 = 4 where c2 = 2;
next c1; -- returns "2, 3" ==> updated row is *not* picked up
next c1; -- returns "3, 4"
next c1; -- returns "4, 5"
next c1; -- no current row
close c1;
rollback;
Is there a reason we get different results for the two query plans, or
is this a bug? If it's a bug, then what is the correct behavior for
this kind of thing?
Note that if I comment out the call to "disableBulkFetch()" in the code
shown at the top of this mail, then the order of the rows changes for
the Index Scan (they are no longer in correct order), but the open
cursor still picks up the updated row.
I plan to see if I can reproduce this behavior using a JDBC program, but
if anyone has any thoughts/insight in the meantime, I'd appreciate the
pointers. And apologies in advance if I'm missing something obvious...
Army
