Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1780902856.1227619664347.JavaMail.jira@brutus>
Date: Tue, 25 Nov 2008 05:27:44 -0800 (PST)
From: "Marco (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3900) SELECT ... FOR UPDATE cannot be used
 in many queries
In-Reply-To: <16632094.1223304944346.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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