db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3304) Explicit commit inside a java procedure makes a dynamic result sets passed out unavailable
Date Mon, 04 Feb 2008 17:55:14 GMT

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

Mamta A. Satoor commented on DERBY-3304:
----------------------------------------

Based on the research/comments on this issue and Dan's comment in DERBY-3037 "What is the
purpose of commit() closing the language result sets? If it's to close JDBC ResultSets that
are marked close at commit then a possibility is to only close language result sets that return
rows (returnRows() returns true). That would leave language result sets that do not return
rows open, but by definition (I think) those are the ones that are actively executing and
the very ones we don't want to close. :-) ", I thought of keeping the language resultsets
that do not return rows open when BaseActivation.reset() is trying to decide what resultsets
to close. This will ensure that we do not close the CallStatementResultSet(since it does not
return rows) while it is still being constructed when the java procedure issues commit/rollback.
The change involved is as follows
$ svn stat -q
M      java\engine\org\apache\derby\impl\sql\execute\BaseActivation.java
$ svn diff
Index: java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java   (revision 617013)
+++ java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java   (working copy)
@@ -334,7 +334,8 @@
        {
                // if resultset holdability after commit is false, close it
                if (resultSet != null) {
-                       if (!resultSetHoldability || !resultSet.returnsRows()) {

+                       //Do not close resultsets that do not return any rows.
+                       if ((!resultSetHoldability && resultSet.returnsRows()==true)){
                                // would really like to check if it is open,
                                // this is as close as we can approximate that.
                                resultSet.close();

So, as can be seen from diff above, rather than always closing the resultsets that do not
return rows, we want to leave them alone. 

This works good for CallStatementResultSet but this is causing a problem for DML like update
for instance below. 

The following ij script demonstrates the problem with the patch. The script creates 2 tables
with foreign key relationship. Then it tries to violate the foreign key by doing an update.
With my changes, Derby fails to generate any statistics for update. This is because the statistics
collection happens in the language resultset close method but since with my patch, we do not
close the resultset if it does not return row, we do not get a chance to collect the statistics.
Without my patch, the statistics get collected in language resultset close which is called
by BaseActivation.reset 

connect 'jdbc:derby:c:/dellater/db;create=true';
drop table basic;
drop table p;
create table p (ccharForBitData char(1) for bit data not null, unindexed smallint, cchar char(10)
not null, 
		constraint pk1 primary key (cchar, ccharForBitData));
insert into p values (x'22', 33, '22');
create table basic (cint int, cchar char(10),  ccharForBitData char(1) for bit data, unindexed
int);
insert into basic values (22, '22', x'22', 22);
alter table basic add constraint fk1 foreign key (cchar, ccharForBitData) references p;
maximumdisplaywidth 2000;
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
update p set ccharForBitData = x'22', cchar = CAST(unindexed as CHAR(10));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

To avoid this statistics collection problem, we could change language resultset close criteria
in BaseActivation.reset to follow Dag's suggestion "Maybe the test in BaseActivation.reset
can test for CallStatementResultSet?' but like Dag said, "Not OO, though". Will like to hear
if anyone has any comments?


> Explicit commit inside a java procedure makes a dynamic result sets passed out unavailable
> ------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3304
>                 URL: https://issues.apache.org/jira/browse/DERBY-3304
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.4.0.0
>            Reporter: Daniel John Debrunner
>            Assignee: Mamta A. Satoor
>         Attachments: Main.java
>
>
> Repro (Main.java) that shows changed behavior after svn 602991
> (the patch committed for this issue). It seems a regression: (originally from Dag H.
Wanvik attached to DERBY-1585)
> An explicit commit inside a stored procedure makes a dynamic result sets passed out unavailable,
even if the commit is executed *prior* to the result set; as in the repro.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message