conn.rollback() in a procedure should not close the CallStatementResultSet - WAS Re: svn commit: r627673 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java

[Daniel John Debrunner <dj...@apache.org>]

Mamta Satoor wrote:
> I will soon make a checkin for the resultset from the procedure which
> shows that in trunk, after checkin 602991 for DERBY-1585, a procedure
> does not return a resultset if there was a rollbck inside the
> procedure with resultset creation before rollback. 

Thanks for finding the commit that caused it Mamta. Having made that 
change I had to look into why it changed the behaviour as that was not a 
intended part of the fix.

DERBY-1585 fixed the issue that unprocessed dynamic result sets (e.g. in 
a trigger) were not closed until gc, leading to an error trying to drop 
a table due to an open result set.

The fix (602991) changed it so that at the close of 
CallStatementResultSet any unprocessed dynamic result sets were closed. 
Thus the order is for a normal execution is:

    callStmtResultSet.open();
       calls the Java method for the procedure
            method creates & returns JDBC ResultSets
    embed statement processes dynamic result sets
    callStmtResultSet.close();
        no dynamic results to close since embed statement handled them

(and for a trigger embed statement is not involved so the 
callStmtResultSet.close closes any dynamic result sets)

Now if the procedure's method calls rollback() then this is the order:

callStmtResultSet.open();
       calls the Java method for the procedure
            method creates & sets JDBC ResultSets in parameter arrays
            rollback called()
                callStmtResultSet.close()
                    close all dynamic result sets <<<< NEW (DERBY-1585)
       embed statement processes dynamic result sets
       callStmtResultSet.close();
        no dynamic results to close since embed statement handled them

So the change occurred because the rollback caused the close of the 
CallStatementResultSet to be called which now closes all outstanding 
dynamic result sets and clears all references to them, making them 
invisible to any following embed statement processing, resulting in no 
dynamic results being returned.

Prior to 602991 even though the underlying language result of any 
dynamic jdbc result set was closed, the jdbc result set did not appear 
closed (actually due to something similar to DERBY-3404), thus embed 
statement processed it as an open result set and returned it. Any use on 
it failed (it was closed) as a different mechanism was used to check it 
was closed.

So I understand why DERBY-1585 (602991) changed the behaviour but I 
don't think the complete logic is correct. Much like a commit in a 
procedure was incorrectly closing the CallStatementResultSet (recently 
fixed by Mamta) I believe the rollback should not be closing the 
CallStatementResultSet. It's still being used and thus should remain 
open. Now a rollback due to an exception that causes the CALL statement 
to throw an exception should close the CallStatementResultSet, but if 
the CALL statement is returning then its CallStatementResultSet should 
not be closed.

Dan.

Re: conn.rollback() in a procedure should not close the CallStatementResultSet - WAS Re: svn commit: r627673 - /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangProcedureTest.java

[Mamta Satoor <ms...@gmail.com>]

I will look at special handling of resultsets that do not return rows
to stay open at the time of rollback unless the rollback is caused by
an exception, in which case, we want to close all the resulsets
whether they return rows or not.

Mamta

On 2/15/08, Daniel John Debrunner <dj...@apache.org> wrote:
> Mamta Satoor wrote:
> > I will soon make a checkin for the resultset from the procedure which
> > shows that in trunk, after checkin 602991 for DERBY-1585, a procedure
> > does not return a resultset if there was a rollbck inside the
> > procedure with resultset creation before rollback.
>
> Thanks for finding the commit that caused it Mamta. Having made that
> change I had to look into why it changed the behaviour as that was not a
> intended part of the fix.
>
> DERBY-1585 fixed the issue that unprocessed dynamic result sets (e.g. in
> a trigger) were not closed until gc, leading to an error trying to drop
> a table due to an open result set.
>
> The fix (602991) changed it so that at the close of
> CallStatementResultSet any unprocessed dynamic result sets were closed.
> Thus the order is for a normal execution is:
>
>    callStmtResultSet.open();
>       calls the Java method for the procedure
>            method creates & returns JDBC ResultSets
>    embed statement processes dynamic result sets
>    callStmtResultSet.close();
>        no dynamic results to close since embed statement handled them
>
> (and for a trigger embed statement is not involved so the
> callStmtResultSet.close closes any dynamic result sets)
>
> Now if the procedure's method calls rollback() then this is the order:
>
> callStmtResultSet.open();
>       calls the Java method for the procedure
>            method creates & sets JDBC ResultSets in parameter arrays
>            rollback called()
>                callStmtResultSet.close()
>                    close all dynamic result sets <<<< NEW (DERBY-1585)
>       embed statement processes dynamic result sets
>       callStmtResultSet.close();
>        no dynamic results to close since embed statement handled them
>
> So the change occurred because the rollback caused the close of the
> CallStatementResultSet to be called which now closes all outstanding
> dynamic result sets and clears all references to them, making them
> invisible to any following embed statement processing, resulting in no
> dynamic results being returned.
>
> Prior to 602991 even though the underlying language result of any
> dynamic jdbc result set was closed, the jdbc result set did not appear
> closed (actually due to something similar to DERBY-3404), thus embed
> statement processed it as an open result set and returned it. Any use on
> it failed (it was closed) as a different mechanism was used to check it
> was closed.
>
> So I understand why DERBY-1585 (602991) changed the behaviour but I
> don't think the complete logic is correct. Much like a commit in a
> procedure was incorrectly closing the CallStatementResultSet (recently
> fixed by Mamta) I believe the rollback should not be closing the
> CallStatementResultSet. It's still being used and thus should remain
> open. Now a rollback due to an exception that causes the CALL statement
> to throw an exception should close the CallStatementResultSet, but if
> the CALL statement is returning then its CallStatementResultSet should
> not be closed.
>
> Dan.
>