[
https://issues.apache.org/jira/browse/DERBY-3900?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12650559#action_12650559
]
Marco commented on DERBY-3900:
------------------------------
Thanks a lot for your replies! I have opened an issue in the DataNucleus JIRA:
http://www.jpox.org/servlet/jira/browse/NUCRDBMS-88
@Dag H. Wanvik
> A global switch ... isn't very flexible.
Today, many people use persistence layers (JDO, JPA, Hibernate, etc.). When
using such a framework, updatable ResultSets are normally not necessary at all,
since the persistence engines perform explicit UPDATE/INSERT/DELETE commands.
Thus, it's not a problem at all that the flexibility is reduced and IMHO it's
not required to modify the behaviour on a per-query-base.
> You are right of course, that only using WITH RS could cause more deadlocks
I have the impression that Derby seems to need quite some time to detect
deadlocks (why?). Therefore, getting even more of them would not be desirable.
Thus, I'd favour a modification of the "FOR UPDATE" behaviour, if it would lead
to the same result with less deadlocks.
> 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.
