db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oystein.Grov...@Sun.COM (Øystein Grøvlen)
Subject Re: SELECT ... FOR UPDATE not working?
Date Fri, 19 Aug 2005 11:26:32 GMT
>>>>> "KW" == Kristian Waagan <Kristian.Waagan@Sun.COM> 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.


View raw message