[jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable result set to work

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable result set to work

Apache Derby Developers mailing list
    [ http://issues.apache.org/jira/browse/DERBY-231?page=comments#action_12319651 ]

Daniel John Debrunner commented on DERBY-231:
---------------------------------------------

The FOR UPDATE support is probably more from a 'de-facto' standard than the SQL spec. Most, if not all, other databases support  a FOR UPDATE on a SELECT statement. It is heavily used by application servers (e.g. EJB implementations). Removing it would affect a lot of existing applications.

> "FOR UPDATE" required for updatable result set to work
> ------------------------------------------------------
>
>          Key: DERBY-231
>          URL: http://issues.apache.org/jira/browse/DERBY-231
>      Project: Derby
>         Type: Improvement
>   Components: SQL
>     Versions: 10.0.2.1
>     Reporter: Dag H. Wanvik
>     Priority: Minor
>  Attachments: fff
>
> To get an updatable result set, the JDBC 3.0 spec, section 14.2.4
> "Modifying ResultSet Objects" states:
>     "ResultSet objects with concurrency CONCUR_UPDATABLE can be updated
>      using ResultSet objects".
> In addition, Derby requires the SQL SELECT statement to have a "FOR
> UPDATE" clause for updates to be allowed. This may be a usability issue, as
> many examples, e.g. in "JDBC API tutorial and reference and reference"
> book and the JDBC 3.0 Specification (14.2.4.1) do not include a "FOR
> UPDATE" clause in the SQL SELECT.
> Mamta Satoor says:
> "Derby implements the JDBC updatable resultset by using the existing
>  updatable cursor implementation. And in order to do that, it requires
>  that the SELECT statement should include the FOR UPDATE clause. One
>  can change the Derby implementation so that it does not require FOR
>  UPDATE clause to piggyback on updatable cursor implementation."
> Dan DeBrunner says:
> "Technically I wonder if this is covered by the JDBC standard, I see
>  nothing in the JDBC 3.0 that states any requirements for the SQL
>  statement for an updateable result set. I know the JDBC tutorial book
>  has some guidelines as to what will typically work, but isn't it up to
>  the database engine to define what works here?
>  Having said that I think that not requiring the FOR UPDATE would be a
>  useful improvement."

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

Reply | Threaded
Open this post in threaded view
|

Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable result set to work

oysteing
>>>>> "DJD(" == Daniel John Debrunner (JIRA) <[hidden email]> writes:

    DJD(>     [ http://issues.apache.org/jira/browse/DERBY-231?page=comments#action_12319651 ]
    DJD(> Daniel John Debrunner commented on DERBY-231:
    DJD(> ---------------------------------------------

    DJD(> The FOR UPDATE support is probably more from a 'de-facto'
    DJD(> standard than the SQL spec. Most, if not all, other
    DJD(> databases support a FOR UPDATE on a SELECT statement. It is
    DJD(> heavily used by application servers (e.g. EJB
    DJD(> implementations). Removing it would affect a lot of existing
    DJD(> applications.

One problem is that some application servers (e.g., Sun's) assume a
de-facto standard implementation of "SELECT FOR ... UPDATE" which is
incompatible with how Derby implements it.  That is, the Sun
App. Server uses FOR UPDATE for its "lock-when-loaded" consistency
level.  For databases that does not support this implementation (DB2,
Sybase, and Derby), this consistency level is not available.  I asked
why the could not use REPEATABLE READ.  Their argument was that
REPEATABLE READ would enforce the same locking on all beans within a
transaction.  With FOR UPDATE they can do bean-specific locking.

I think this shows that there is often a need for database
applications to have more control over concurrency mechanisms than
what is covered by the SQL standard.

--
?ystein