db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DERBY-3900) SELECT ... FOR UPDATE cannot be used in many queries
Date Sun, 23 Nov 2008 19:05:44 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3900?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12650042#action_12650042

knutanders edited comment on DERBY-3900 at 11/23/08 11:03 AM:

I think we should leave this issue open.

SELECT ... WITH RS doesn't acquire update locks, so it doesn't have the exact same transactional
properties as FOR UPDATE. If you have a transaction that first reads a set of records, then
processes the information, and finally updates the records, you could end up with deadlocks
and rollbacks if other, similar transactions are executed concurrently. If you additionally
use FOR UPDATE when you read the records in the beginning of the transaction, no other transaction
can read the same records with FOR UPDATE, and you don't get deadlocks. This could in some
cases give significant performance benefits. Setting an exclusive table lock before reading
the rows could in some cases give you a similar benefit, but that could have a much higher
concurrency penalty if only a small part of the table is actually touched.

GlassFish is one application that uses SELECT ... FOR UPDATE WITH RS to improve the performance
of certain transactions. See https://glassfish.dev.java.net/issues/show_bug.cgi?id=4523. However,
since Derby doesn't support the FOR UPDATE clause in joins, it needs to fall back to a less
efficient approach for some queries. With the functionality suggested by the reporter, higher
performance could also be achieved for those queries that involve joins.

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

View raw message