db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-5545) Exception ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF exception occuring on rs.next() after long run.
Date Tue, 11 Sep 2012 15:33:07 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5545?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13453092#comment-13453092
] 

Dag H. Wanvik edited comment on DERBY-5545 at 9/12/12 2:31 AM:
---------------------------------------------------------------

I find Derby closes open result sets for all rollback actions. I do not think is wrong as
per the SQL semantics, although it may be unexpected: The savepoint sets a limit to how much
of *updates* will be rolled back: Any updates performed within the main transaction *before*
the save point will be kept even after the rollback(sp).
I guess Derby could keep track of result sets that were opened *before* the savepoint was
set and only close those opened *after*, but that's not the current behavior as you have observed.

The JDBC specification has been unclear on when to close result sets in the past, although
its clearer in the current version (4.1). I'll have a check there and also look at the defined
semantics for SQL cursors (which corresponds to JDBC result sets more or less) and see how
those are defined in the presence of savepoints.

Update: In SQL 2003, vol2, section 16.7 <rollback statement>, section General Rule 3)
<savepoint specified>, clause g) reads: 

"For every open cursor CR in any SQL-client module associated with the current SQL-transaction
that
was opened subsequent to the establishment of S, the following statement is implicitly executed:
CLOSE CR"

I take that to mean that the cursor should remain open iff it was established prior to the
savepoint, and, by analogy, the JDBC result set shoudl stay open too.

Now clause h) states:

"The status of any open cursors in any SQL-client module associated with the current SQL-transaction
that were opened by the current SQL-transaction before the establishment of S is implementation
defined.


So, the behavior is "implementation defined", which means Derby's implementation is OK with
the standard. I agree this could be improved to stay open though. I'll file this as an improvement
request.
                
      was (Author: dagw):
    I find Derby closes open result sets for all rollback actions. I do not think is wrong
as per the SQL semantics, although it may be unexpected: The savepoint sets a limit to how
much of *updates* will be rolled back: Any updates performed within the main transaction *before*
the save point will be kept even after the rollback(sp).
I guess Derby could keep track of result sets that were opened *before* the savepoint was
set and only close those opened *after*, but that's not the current behavior as you have observed.

The JDBC specification has been unclear on when to close result sets in the past, although
its clearer in the current version (4.1). I'll have a check there and also look at the defined
semantics for SQL cursors (which corresponds to JDBC result sets more or less) and see how
those are defined in the presence of savepoints.

Update: In section 16.7 <rollback statement>, section General Rule 3) <savepoint
specified>, clause g) reads: 

"For every open cursor CR in any SQL-client module associated with the current SQL-transaction
that
was opened subsequent to the establishment of S, the following statement is implicitly executed:
CLOSE CR"

I take that to mean that the cursor should remain open iff it was established prior to the
savepoint, and, by analogy, the JDBC result set shoudl stay open too.

Now clause h) states:

"The status of any open cursors in any SQL-client module associated with the current SQL-transaction
that were opened by the current SQL-transaction before the establishment of S is implementation
defined.


So, the behavior is "implementation defined", which means Derby's implementation is OK with
the standard. I agree this could be improved to stay open though. I'll file this as an improvement
request.
                  
> Exception ResultSet not open. Operation 'next' not permitted. Verify that autocommit
is OFF exception occuring on rs.next() after long run. 
> --------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5545
>                 URL: https://issues.apache.org/jira/browse/DERBY-5545
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.8.2.2
>         Environment: OSX 10.6
>            Reporter: Paul taylor
>              Labels: derby_triage10_9
>
> Im seeing this error occur after loading alot of data into the Database. I can confirm
that autocommit is set to off, and that it occurs on calling  rs.next() immediatlely after
running a query and assigning to  resultset rs. The cdoe is called many times (250,000) and
usually works, then suddenly it starts going wrong, I also using c3po database pooling. Im
wondering if the problem is linked to memory consumption although I have no OutOfMemoryError
occurring
>   
> Java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that
autocommit is OFF.
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedResultSet.checkIfClosed(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedResultSet.checkExecIfClosed(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
> 	at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859)

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message