[
https://issues.apache.org/jira/browse/DERBY-3900?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12650541#action_12650541
]
Kristian Waagan commented on DERBY-3900:
----------------------------------------
I haven't given the switch much thought, I only noticed that the reporter
mentioned it.
The idea is to keep the current behavior when the switch is "off" I guess, and
then many queries would be denied. It has been speculated this is because of
the difficulty of implementing updatable result sets for joins etc.
If the switch is turned "on", the result sets won't be updatable, but exclusive
locks will be set on the rows touched.
I have no idea how easily such behavior can be achieved. I believe other people
are in a better position to say something about the implementation
cost/difficulty.
Your point about the lack of flexibility of a global switch is absolutely
valid. Another option is to add (non-standard?) per query SQL syntax, or maybe
something for a connection/session.
It seems other DBMSs are using SELECT ... FOR UPDATE [NOWAIT] for the purpose
described by this Jira issue.
> SELECT ... FOR UPDATE cannot be used in many queries
> ----------------------------------------------------
>
> Key: DERBY-3900
> URL: https://issues.apache.org/jira/browse/DERBY-3900
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Marco
>
> The documentation in
> http://db.apache.org/derby/docs/10.4/ref/rrefsqlj41360.html#rrefsqlj41360__sqlj15384
> says that SELECT ... FOR UPDATE cannot be used in many situations (e.g. when
> ORDER BY is present or when JOINs are used). I can very well understand that
> the current implementation using updatable cursors is very hard to implement
> when multiple tables are used and therefore these restrictions are probably
> necessary.
> However, besides that functionality, "FOR UPDATE" is extremely useful for
> transactional integrity: For example, we - http://www.jfire.org - use
> transaction isolation level read committed, because it provides good
> transaction safety combined with good performance. When modifying records, we
> first select the appropriate table rows with a SELECT FOR UPDATE in order to
> guarantee that the data we just read cannot be manipulated by another
> transaction while we are working with it.
> I do not see any reason why this locking behaviour should not be possible for
> certain queries. Therefore, I recommend to introduce a configuration setting
> (maybe a system property? or an option passed to the JDBC-URL?) that disables
> updatable queries completely (we don't need them anyway and probably it
> improves performance when not using them). With this option set, the SELECT
> ... FOR UPDATE should solely affect locks on rows - and work with all SELECT
> expressions - no matter whether they use JOIN, UNION, ORDER BY etc..
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
