Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of erayl@bellsouth.net designates
 205.152.59.70 as permitted sender)
Message-ID: <4305D3B9.6010608@bellsouth.net>
Date: Fri, 19 Aug 2005 08:42:33 -0400
From: Edward Rayl <erayl@bellsouth.net>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: Re: SELECT ... FOR UPDATE not working?
References: <4304FC35.7040008@Sun.com> <43050263.6040202@sbcglobal.net>
 <430516B1.3080200@Sun.com> <x1ofy86yyxp3.fsf@atum11.norway.sun.com>
In-Reply-To: <x1ofy86yyxp3.fsf@atum11.norway.sun.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


>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