You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Andreas Korneliussen (JIRA)" <de...@db.apache.org> on 2005/10/27 17:49:02 UTC
[jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable result set to work
[ http://issues.apache.org/jira/browse/DERBY-231?page=comments#action_12356096 ]
Andreas Korneliussen commented on DERBY-231:
--------------------------------------------
Currently, the updatemode for the resultset node is determined in the bind() phase of the query compilation. If the updatability clause is unspecified, the updatemode is set to READ_ONLY.
To get rid of the requirement of having "for update" in the query string, I plan doing the following :
1. In the bind() phase, the nodes do currently not have any information about the concurrency mode for the java.sql.Statement object. I would like to provide a mechanism to bring it down to the compiler by adding a field to the StatementContext object.
2. If the concurrency mode for the java.sql.Statement object is CONCUR_READ_ONLY, the updatemode will be set to READ_ONLY. If the query string contains "for update" an error will be thrown.
3. If the concurrency mode for the Statement object is CONCUR_UPDATABLE, the updatemode will be UPDATABLE if the resultset can be updatable (i.e contains no joins, order by etc). Otherwise it will be READ_ONLY and a warning will be given.
4. Lockmodes: I do not plan to make any distinction between "for update" and unspecified updatability clause when it comes to determining the lock mode.
It seems important that the update mode is determined in the bind() phase, because the compiler can do some optimizations if the cursor is not updatable (i.e not produce a target resultset).
> "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
> Assignee: Andreas Korneliussen
> 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
Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable result set to work
Posted by Mamta Satoor <ms...@gmail.com>.
On 10/27/05, Daniel John Debrunner <dj...@debrunners.com> wrote:
>
> Andreas Korneliussen (JIRA) wrote:
>
> > [
> http://issues.apache.org/jira/browse/DERBY-231?page=comments#action_12356096]
> >
> > Andreas Korneliussen commented on DERBY-231:
> > --------------------------------------------
> >
> > Currently, the updatemode for the resultset node is determined in the
> bind() phase of the query compilation. If the updatability clause is
> unspecified, the updatemode is set to READ_ONLY.
> >
> > To get rid of the requirement of having "for update" in the query
> string, I plan doing the following :
> >
> > 1. In the bind() phase, the nodes do currently not have any information
> about the concurrency mode for the java.sql.Statement object. I would like
> to provide a mechanism to bring it down to the compiler by adding a field to
> the StatementContext object.
>
> How this interacts with the statement cache needs to be considered. The
> current statement cache is lookup by current schema and text of the
> query string. You may be going in a direction where the same text
> 'SELECT * FROM T' leads to different plans depending on the updatable
> state of the result set.
>
> >
I am glad that work is being done to get rid of "FOR UPDATE" in the query
string. When I implemented updatable resultset(My itch at that point was to
provide an implementation for updatable resultset in its simplest/quickest
form which menat requiring FOR UPDATE), the statement caching was the reason
that I decided to require FOR UPDATE clause so I could distinguish between a
read only resutlset and updatable resultset. So just to reiterate Dan's
point, do look for statement caching issue.
Mamta
Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable
result set to work
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Dag H. Wanvik wrote:
> Hi,
>
> Andreas> > 2) Applications use the FOR UPDATE clause to control locking for
> Andreas> > future updates with read only ResultSets.
> Andreas> >
> Andreas>
> Andreas> Note currently it throws an exception if the statement is not updatable
> Andreas> i.e contains a join or order by.
>
> I guess what you mean here is that the FOR UPDATE is not in general
> available as a means for locking for future updates.
>
> To Dan's point, my tests indicate that the current Derby
> implementation for forward-only updatable result sets only sets a row
> update lock while on the current row.
I think that's only true for read committed isolation level, in higher
levels the update lock will be there until end of transaction. So I
guess this use is only useful with those isolation levels.
Dan.
Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable
result set to work
Posted by Andreas Korneliussen <An...@Sun.COM>.
Just to clarify: my intention is to *not* change the lockmode for "for
update" as part of this specific issue.
-- Andreas
Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable
result set to work
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> the current Derby semantics (releasing
> the update lock once a next() is executed), isn't very helpful.
Even in read-committed isolation level, it still seems like it
might be useful to be able to control the lock-mode on the current
row. If I have an read-committed transaction which reads through
many rows, and chooses to update a subset of them, being able
to communicate to the DB that I'd like an update-mode lock when
I read the record, to be either (a) released when I do Next or
(b) promoted to exclusive-mode if I happen to update this record,
seems like a useful behavior to me.
That is, I think that update-mode locks can be useful in
reducing deadlock, and I think that's true even when running at
read-committed isolation level.
thanks,
bryan
Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable
result set to work
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi,
>>>>> "Oyvind" == Oyvind Bakksjo <Oy...@Sun.COM> wrote:
> > In contrast, Oracle's "FOR UPDATE" places locks on the entire result
> > set for the duration of the transaction (see below). The usefulness as
> > a way to control locking would be more useful if the Derby locking was
> > closer to what Oracle does, at the expense of concurrency.
> >
> > Dag
>
> Just a little pick at the wording... What's "useful" behaviour depends
> on the application and its needs. If you don't need update locks on the
> entire result set, the "usefulness" of such a behaviour is negative,
> since it only reduces concurrency and, as such, overall performance.
Obviously it is dependent on the application. My point is that if the
application wants to lock all rows of a result set, with actually
updating them (necessarily), the current Derby semantics (releasing
the update lock once a next() is executed), isn't very helpful. I was
under the impression that was the kind of usage Dan was aiming at when
he argued that FOR UPDATE should be permissible is even if the result
set has concurrency read-only.
Dag
Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable
result set to work
Posted by Oy...@Sun.COM.
Dag H. Wanvik wrote:
> Hi,
>
> Andreas> > 2) Applications use the FOR UPDATE clause to control locking for
> Andreas> > future updates with read only ResultSets.
> Andreas> >
> Andreas>
> Andreas> Note currently it throws an exception if the statement is not updatable
> Andreas> i.e contains a join or order by.
>
> I guess what you mean here is that the FOR UPDATE is not in general
> available as a means for locking for future updates.
>
> To Dan's point, my tests indicate that the current Derby
> implementation for forward-only updatable result sets only sets a row
> update lock while on the current row.
>
> In contrast, Oracle's "FOR UPDATE" places locks on the entire result
> set for the duration of the transaction (see below). The usefulness as
> a way to control locking would be more useful if the Derby locking was
> closer to what Oracle does, at the expense of concurrency.
>
> Dag
Just a little pick at the wording... What's "useful" behaviour depends
on the application and its needs. If you don't need update locks on the
entire result set, the "usefulness" of such a behaviour is negative,
since it only reduces concurrency and, as such, overall performance.
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable
result set to work
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi,
Andreas> > 2) Applications use the FOR UPDATE clause to control locking for
Andreas> > future updates with read only ResultSets.
Andreas> >
Andreas>
Andreas> Note currently it throws an exception if the statement is not updatable
Andreas> i.e contains a join or order by.
I guess what you mean here is that the FOR UPDATE is not in general
available as a means for locking for future updates.
To Dan's point, my tests indicate that the current Derby
implementation for forward-only updatable result sets only sets a row
update lock while on the current row.
In contrast, Oracle's "FOR UPDATE" places locks on the entire result
set for the duration of the transaction (see below). The usefulness as
a way to control locking would be more useful if the Derby locking was
closer to what Oracle does, at the expense of concurrency.
Dag
> SELECT...FOR UPDATE
> SYNTAX
> SELECT cols FROM tables [WHERE...] FOR UPDATE [OF cols] [NOWAIT];
>
> Locks row(s) from all table(s) that are selected by the query.
>
> The row locks are released when the transaction that contains the
> SELECT...FOR UPDATE is committed or rolled-back.
>
> OF cols: Lock only rows from tables which have a column listed in the
> OF clause. Rows are not locked in tables that don't have a column
> appearing in the OF clause. If no OF clause is listed, rows from all
> tables are locked.
Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable
result set to work
Posted by Andreas Korneliussen <An...@Sun.COM>.
> How this interacts with the statement cache needs to be considered. The
> current statement cache is lookup by current schema and text of the
> query string. You may be going in a direction where the same text
> 'SELECT * FROM T' leads to different plans depending on the updatable
> state of the result set.
>
I would need to check further into that, however I think that by making
the new field for concurrency mode part of the identity of the
GenericStatement, the cache would be correct.
>
>>2. If the concurrency mode for the java.sql.Statement object is CONCUR_READ_ONLY, the updatemode will be set to READ_ONLY. If the query string contains "for update" an error will be thrown.
>
>
> That would be incorrect.
> 1) Derby still needs to support positioned UPDATE and DELETE, in that
> case it is fine to have a FOR UPDATE clause with a read only ResultSet.
>
You are right, it should not throw an error there.
I will modify the suggestion:
2. If the concurrency mode for the java.sql.Statement object is
CONCUR_READ_ONLY, the updatemode will be set to READ_ONLY if the
updateclause is unspecified or "for read only". If the query string
contains "for update" the updatemode is UPDATABLE, however the
java.sql.ResultSet.updateXXX methods throws an exception (if called)
since the resultset is not updatable.
> 2) Applications use the FOR UPDATE clause to control locking for
> future updates with read only ResultSets.
>
Note currently it throws an exception if the statement is not updatable
i.e contains a join or order by.
-- Andreas
>
> Dan.
>
Re: [jira] Commented: (DERBY-231) "FOR UPDATE" required for updatable
result set to work
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Andreas Korneliussen (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-231?page=comments#action_12356096 ]
>
> Andreas Korneliussen commented on DERBY-231:
> --------------------------------------------
>
> Currently, the updatemode for the resultset node is determined in the bind() phase of the query compilation. If the updatability clause is unspecified, the updatemode is set to READ_ONLY.
>
> To get rid of the requirement of having "for update" in the query string, I plan doing the following :
>
> 1. In the bind() phase, the nodes do currently not have any information about the concurrency mode for the java.sql.Statement object. I would like to provide a mechanism to bring it down to the compiler by adding a field to the StatementContext object.
How this interacts with the statement cache needs to be considered. The
current statement cache is lookup by current schema and text of the
query string. You may be going in a direction where the same text
'SELECT * FROM T' leads to different plans depending on the updatable
state of the result set.
>
> 2. If the concurrency mode for the java.sql.Statement object is CONCUR_READ_ONLY, the updatemode will be set to READ_ONLY. If the query string contains "for update" an error will be thrown.
That would be incorrect.
1) Derby still needs to support positioned UPDATE and DELETE, in that
case it is fine to have a FOR UPDATE clause with a read only ResultSet.
2) Applications use the FOR UPDATE clause to control locking for
future updates with read only ResultSets.
Dan.