db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Edward Rayl <er...@bellsouth.net>
Subject Re: SELECT ... FOR UPDATE not working?
Date Fri, 19 Aug 2005 12:42:33 GMT

>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


Mime
View raw message