db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: [jira] Updated: (DERBY-231) "FOR UPDATE" required for updatable result set to work
Date Fri, 04 Nov 2005 15:31:08 GMT
Andreas Korneliussen wrote:


> Based on the comments from you and Bernt, I would suggest a new comment
> like:
> 
> NOTE: THIS IS NOT COMPATIBLE WITH THE ISO/ANSI SQL STANDARD.
> 
> According to the SQL-standard:
> If updatability is not specified, a SELECT * FROM T will be implicitely
> read only in the context of a cursor which is insensitive, scrollable or
> have an order by clause. Otherwise it is implicitely updatable.
> 
> In Derby, we make a SELECT * FROM T updatable if the concurrency mode is
> ResultSet.CONCUR_UPDATE. If we do make all SELECT * FROM T  updatable
> by default, we cannot use an index on any single-table select, unless it
> was declared FOR READ ONLY. This would be pretty terrible, so we are
> breaking the ANSI rules. Later, we may have an ANSI compatibility mode
> so we can pass the NIST tests.


Looks good, I would remove the last sentence, there would be no need for
a compatibility mode and not something I would like to see. Derby is
supporting a subset of what the standard allows. We could support the
correct behaviour with no correctness issues with backwards
compatibility, as long as the performance issues were addressed.

Bernt wrote:
> Dan wrote:
>>but it would be good to come to clear agreement on if SELECT * FROM
>>> T can be updated with a positioned update/delete and a read-only
>>> ResultSet. (Even if Derby doesn't support it today, it would be nice
>>> to know or not if it is meant to be supported).
> 
> 
> It definitely would mean lower performance due to locking issues.

Though some work was already done on this to ensure a simple FOR UPDATE
with no column list added for locking behaviour did not degrade
performance. I'd have to research the details, but it's in the code.


Dan.


Mime
View raw message