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