db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Updating rows with an open cursor...what is the expected behavior?
Date Tue, 12 Jun 2007 18:34:03 GMT
Army <qozinx@gmail.com> writes:

> 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".

Just guessing, but if you do an index scan, you automatically get the
rows in the correct order and you only need one pass to return the
results. When the first row is returned, the underlying scan has
probably only seen that one row, so any updates to other rows will be
seen. If you do a table scan, you need to sort the results before
returning them. I'm not sure exactly how this is done, but I guess you
would need to go through the entire table before you return the first
row. Therefore, the underlying scan has already seen all the rows (and
perhaps buffered them somewhere) and you don't see any updates that were
performed at a later stage.

> 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 don't think it's a bug, since there's no guarantee (I think) as to
when the rows are actually read. But I'll leave it to the scholars to
find the chapter and verse in the appropriate spec... ;)

But even if you defined one of the behaviours to be correct, I think you
would run into trouble. If it is correct to see the update, what if the
value is changed to something that makes it come out of order (for
instance, smaller than the previous row returned). Should it be
discarded, or should it be returned out of order? Both outcomes seem

And if you say it's correct not to see the update, then you would need
to buffer all the rows when the statement is executed, or perhaps you
would perform some intra-transaction locking to get some kind of
transaction-internal repeatable read. This doesn't seem ideal, at least
not from a performance perspective.

> 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.

Sounds reasonable, since then the index scan would read more rows at a
time, and it probably sees the row before it is updated. Not sure why it
returns the rows in the wrong order, though.

> 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

Knut Anders

View raw message