db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kristian Waagan (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3900) SELECT ... FOR UPDATE cannot be used in many queries
Date Tue, 25 Nov 2008 11:43:44 GMT

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


Mime
View raw message