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: Updating rows with an open cursor...what is the expected behavior?
Date Tue, 12 Jun 2007 18:24:06 GMT

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?

I think this has to do with sensitive vs. insensitive scan.  Maybe 
someone can point to doc on this.

I am just going to answer for the behavior from the underlying derby
store.  The derby store interface provides no ability to track whether
an update for a specific scan has a affected a row it is about to see.
So a scan on store is always going to let the engine see the rows in
the raw order that they are on disk and if an update "moves" a row to
a place later in the conglomerate then the same scan may see it again.

So definitely in an index scan if you update the key of the scan then
you may or may not see the row again depending on a number of factors
about the current store scan:
1) did the key update move the row to the right of the tree (using right
    since order of keys may be combination of ascending and descending 

2) store level group commit.  Store returns a number of rows to the
    execution engine.  If your update moves the row to a little ahead
    but not as far as the group then language won't see the row again.

3) network level group.  I believe with drda there is another level of
    row caching, but I don't know this area very well.

In the case of current heap implementation I believe changes were made
with respect to locking such that rows never move while a table level
intent lock is held, and also changes were made to return an error in
the case of held cursor across a commit if the rows did move in the
case of scrollable updatable cursor.

I don't know how these ordering changes might affect the various 
alternate storage implementations being proposed.

> 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

View raw message