Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1181162765.1136537482051.JavaMail.jira@ajax.apache.org>
Date: Fri, 6 Jan 2006 09:51:22 +0100 (CET)
From: "Dag H. Wanvik (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-787) cursor closed as a sideeffect of
 closing another cursor with the same name on another connection
In-Reply-To: <136425708.1136219703252.JavaMail.jira@ajax.apache.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-787?page=comments#action_12361952 ] 

Dag H. Wanvik commented on DERBY-787:
-------------------------------------

What is is happening here is that the updateRow operation generates
identical "UPDATE .. WHERE CURRENT OF SQLCUR0" statements for the two
connections.

The statement cache contains a compiled plan (from connection one),
when connection two tries to do the updateRow, resulting in using the
wrong plan (which refers to the result set of the first connection,
which happens to be closed when connection two tries to do its
updateRow).

I have checked the ISO wording about cursors, and from what I can
understand, cursor names should be scoped locally to a connection, but
I cannot find any logic in the compiler statement caching to handle
this.

A partial solution might possibly be to generate cursor names which
are unique per connection for the updatable cursor case, but that
would seem to be just side-stepping the issue. If the user sets the
cursor name explicitly (JDBC Statement#setCursorName) to the same name
in several connections, the scenario would still fail.

I guess one could make the compiler *not* cache statements involving
cursors (similar to what is done for statements referring temporary
tables), but that would force recompilation at every execution of an
{update,delete}Row, which is undesirable as well.

A possibly better approach would be to have the compiler distinguish
the two similar looking statements when it compares them during the
cache lookup operation, by extending the recorded information so that
a comparison would only match - when the statement contains a cursor -
if the cached plan refers a cursor from the current connection.


> cursor closed as a sideeffect of closing another cursor with the same name on another connection
> ------------------------------------------------------------------------------------------------
>
>          Key: DERBY-787
>          URL: http://issues.apache.org/jira/browse/DERBY-787
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.1.2.1
>  Environment: Java 1.4
>     Reporter: Andreas Korneliussen
>  Attachments: CursorIsClosedIssue.java
>
> I was writing some tests for the Scrollable updatable ResultSet feature, and  found some tests failing with 
> ERROR XCL07: Cursor 'SQLCUR0' is closed. Verify that autocommit is OFF.
> in ResultSet.updateRow(). 
> The test does the following:
> 1. set up a connection, and run a query which selects one tuple
> 2. update the tuple using updateXXX and updateRow
> 3. rollback the transaction and close the connection
> Then, repeat the process, however this time use a different string in the query.  This time updateRow() may fail with the error above. 
> The problem has been reproduced on forward only, updatable resultsets.
> Workaround:
> It does not seem to fail if I
> a, set another cursorname on the statement object,
> b, use the same query string.
> I will attach the program to reproduce the problem. Below is the output:
> ~:/<3>db-derby-10.1.2.1-bin/lib> java -cp /home/ak136785/devel/derbytesting/derbytest/build/classes/:derby.jar derbytest.CursorIsClosedIssue
> 1,1,19,Tuple 1
> 2,2,21,Tuple 2
> ERROR XCL07: Cursor 'SQLCUR0' is closed. Verify that autocommit is OFF.
>         at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>         at org.apache.derby.impl.sql.execute.CurrentOfResultSet.getCursor(Unknown Source)
>         at org.apache.derby.impl.sql.execute.CurrentOfResultSet.openCore(Unknown Source)
>         at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(Unknown Source)
>         at org.apache.derby.impl.sql.execute.NormalizeResultSet.openCore(Unknown Source)
>         at org.apache.derby.impl.sql.execute.UpdateResultSet.setup(Unknown Source)
>         at org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown Source)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedResultSet.updateRow(Unknown Source)
>         at derbytest.CursorIsClosedIssue.runTest(CursorIsClosedIssue.java:80)
>         at derbytest.CursorIsClosedIssue.main(CursorIsClosedIssue.java:103)

-- 
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