I think what creates confusion here is that there is two different
uses of "FOR UPDATE" in SQL:
1. The standard "DECLARE CURSOR ... FOR UPDATE" which declares
updatability of a cursor. In JDBC, one will use ResultSets
instead of SQL Cursors and Connection.prepareStatement() allows
you to specify the updatability of the result set.
2. The non-standard "SELECT ... FOR UPDATE" which is implemented in
many databases. Traditionally, it seems like the intention is to
indicate that the records selected by such a select statement may
be updated by later statements of the same transaction. In most
databases this will cause the records to be locked exclusively by
the select statement. This way, later update/delete statements
will not have to upgrade shared locks to exclusive and potential
lock conflicts at that stage are avoided.
Derby seems to have mixed these two cases and requires "SELECT ... FOR
UPDATE" for the result set to be updatable. As Kristian's example
shows the locking behavior is not the same as you would expect from
other databases that have this extension. In other words, "SELECT
... FOR UPDATE" is only used to indicate updatability, not to indicate
what may be updated by succeeding statements.
In my opinion, the best thing would be to deprecate the use of "SELECT
... FOR UPDATE". For JDBC programmers, I am not sure the "SELECT
... FOR UPDATE" extension to the standard is necessary. Using
updatable result set should in most cases be preferred to using two
statements to select and update a record. I also think it is a
mistake to reuse this non-standard syntax for another purpose, i.e.,
to specify updatability, when there are standard ways to achieve this
through JDBC. It is even worse that Derby requires this non-standard
syntax in order to get updatable result sets.
I agree. See Derby-231, "FOR UPDATE" required for updatable result set
to work. All major databases seem to support "select...for update", but
don't _require_ it. Derby is the exception. Deprecation might be
impractical if source code compatibility with other databases is important.
Ed
