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-4373) different results with network server vs. embedded on select from a temporary table with resultset cursor hold over commit
Date Sat, 02 Oct 2010 00:27:32 GMT

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

Mamta A. Satoor commented on DERBY-4373:

It appears that the embedded behavior is correct. Following is the explanation of the behavior
of the embedded and network server(one thing to note is that the Derby resultsets by default
are held open over commit. In the repro, all the resultsets are created with the default holdability).

The relevant part of the attached repro can be broken down to following steps
		1)autocommit is set to false
		2)declare global temp table with on commit delete rows
		3)create resultset rs1tmp
		6)create resultset rs1
		9)create resultset rs5tmp

As we can see in the above sequence of steps, the second resultset rs1 is opened(step 6) before
commit and the user never explicitly closes that resultset before the commit(step 8). 

In the embedded mode, during the commit(step 8), we check if there are any open resultsets
over a temporary table and if yes, then even though the temporary table has been defined to
delete the rows on commit, it will not delete the rows because of that open resultset. Because
of that, after the commit(step 8), when a new resultset is created(step 9) on that temporary
table, it still finds all the rows intact in the temporary table.

Network server on the other hand tries to do optimization with the closing of the resultset.
For the steps above, when the first resultset rs1tmp is created(step 3), network server reads
all the data and since it is a forward only resultset, it closes the resultset even before
client requested close(step 5) of that resultset. Next, when the resultset rs1 is created(step
6), network server reads all the data for that resultset and it closes this resultset too
(without the explicit request from the client to close it). Next when commit(step 8) is issued
by the client, we check if there are any open resultsets over the temporary table and of course,
in case of network server, we don't find any because network server went ahead and closed
them and hence all the rows from the temporary table get deleted. It seems like that the network
server should not close the resultset implicitly if they are supposed to be held open over
commit. I will get that logic a try and see what happens. I will highly appreciate if there
is any feedback on my analysis or the approach to fix the problem.

> different results with network server vs. embedded on select from a temporary table with
resultset cursor hold over commit
> --------------------------------------------------------------------------------------------------------------------------
>                 Key: DERBY-4373
>                 URL: https://issues.apache.org/jira/browse/DERBY-4373
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions:
>            Reporter: Myrna van Lunteren
>         Attachments: repro_d4373.java
> Found this during review of conversion of declareGlobalTempTableJavaJDBC30 to junit (DERBY-2895)
- when I tried to run the test with network server:
> We define a statement like so:
>         Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
>                     ResultSet.HOLD_CURSORS_OVER_COMMIT );
> and global temp table like so:
>             s1.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12
int) on commit delete rows not logged");
> Then, we insert 2 rows, open a result set that selects *, then do commit.
> With a new resultset, we do another select, which with network server gives 0 rows, but
with embedded, 2.

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

View raw message