>>>>> "KW" == Kristian Waagan <[EMAIL PROTECTED]> writes:
KW> The behavior of Derby compared with MySQL (started with
KW> --default-storage-engine=innodb) and PostgreSQL is still different
KW> though. PostgreSQL and MySQL does not allow the update no matter what
KW> transaction isolation level is used. Can you say anything about which
KW> behavior is correct/expected?
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.
--
Øystein
