Re: Question about holdability and XA

[Mamta Satoor]

Hi Kathey,

 

Your table of holdability for JDK13 and Local Transaction should have "conn holdability (Default HOLD_CURSORS_OVER_COMMIT)". (At least, that is what I think. If I am wrong, feel free to bring it up because I haven't worked on holdability for quite some time). But with my suggested change, the table will look like as follows

                        Global Transaction                             Local Transaction
JDK13    CLOSE_CURSORS_AT_COMMIT        conn holdability (Default HOLD_CURSORS_OVER_COMMIT)
JDK14+   CLOSE_CURSORS_AT_COMMIT       conn holdability (Default HOLD_CURSORS_OVER_COMMIT)
 

The difference between jdk13 and jdk14+ is that in jdk13, there is no direct jdbc api Connection.setHoldability to set the holdability to CLOSE_CURSORS_AT_COMMIT. Derby user in jdk13 can use reflection to change the Connection holdability from HOLD_CURSORS_OVER_COMMIT to CLOSE_CURSORS_AT_COMMIT and vice verse. eg of this reflection usage can be found in functionTests.tests.jdbcapi.resultset.java starting at line 155 and it looks as follows

   //Use reflection to set the holdability to false so that the test can run in jdk14 and lower jdks as well
   try {
    Method sh = con.getClass().getMethod("setHoldability", CONN_PARAM);
    sh.invoke (con, CONN_ARG);
   } catch (Exception e) {System.out.println("shouldn't get that error " + e.getMessage());}//for jdks prior to jdk14
 

 

Also, here is some sample ij testing using Sun's jdk131 to demonstrate how holdability works in jdk13

ij> connect 'jdbc:derby:c:/dellater/db1';
ij> create table t1(c11 int);
0 rows inserted/updated/deleted
ij> insert into t1 values (1), (2), (3), (4), (5);
5 rows inserted/updated/deleted
ij> autocommit off;
ij> -- following cursor c1 will have its holdability set to HOLD_CURSORS_OVER_COMMIT which is what the Connection object's holdability by default is. I have confirmed this by issuing a commit later on and then doing next on c1. "next" works even though commit was issued because c1 will not be closed because of the commit
get cursor c1 as 'select * from t1';

ij> next c1;
C11
-----------
1
ij> -- this NoHoldForConnection is an ij command which in jdk13 will use reflection to change the holdability of the connection object to CLOSE_CURSORS_AT_COMMIT.

NoHoldForConnection;
ij> -- since we set the connection holdability to CLOSE_CURSORS_AT_COMMIT using reflection, following cursor c2 will have holdability set to CLOSE_CURSORS_AT_COMMIT. Again, I have confirmed this by issuing a commit later on in the test and then doing next on c2 fails because it got closed as part of commit.

get cursor c2 as 'select * from t1';
ij> next c2;
C11
-----------
1
ij> -- the following commit will leave cursor c1 open because it has holdability set to HOLD_CURSORS_OVER_COMMIT. But cursor c2 will be closed because it has its holdability set to CLOSE_CURSORS_AT_COMMIT

commit;
ij> next c1;
C11
-----------
2
ij> next c2;
ERROR XCL16: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
ERROR XCL16: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
        at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:311)
        at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:459)
        at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:346)
        at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:299)
        at org.apache.derby.impl.tools.ij.ij.NextStatement (ij.java:1226)
        at org.apache.derby.impl.tools.ij.ij.ijStatement(ij.java:564)
        at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:289)
        at org.apache.derby.impl.tools.ij.Main.go(Main.java :203)
        at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:169)
        at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
        at org.apache.derby.tools.ij.main(ij.java:56)
ij> next c1;
C11
-----------
3
ij> exit;

I hope this helps and doesn't muddy the water even more,

Mamta

 

 

On 6/10/05, Kathey Marsden <[EMAIL PROTECTED]> wrote:

Daniel John Debrunner wrote:

>>So if you start a global transaction it should it always be set to
>>CLOSE_CURSORS_AT_COMMIT automatically?
>>or should  the user need to set the holdability?
>>
>>
>
>Automatically set, just like autocommit is set to false for a global
>transaction, regardless of the setting when the connection is attached
>to a local transaction.
>
>
>

So I think based on what you say my table should look like this:

       Holdability for Brokered Connections obtained from an XAConnection


        Global Transaction            Local Transaction

JDK13    CLOSE_CURSORS_AT_COMMIT       CLOSE_CURSORS_AT_COMMIT ?
JDK14+   CLOSE_CURSORS_AT_COMMIT       conn holdability (Default HOLD_CURSORS_OVER_COMMIT)


Is that correct?
If so I think maybe DERBY-346 is really a manifestation of an embedded bug (maybe DERBY-8) but we probably still have a bug for local transactions with the client.

In the protocol the holdability for the connection  does not get sent to
the server.  Instead the holdability is encoded in the PKGNAMCSN that is
sent on prepare.  So I think I have to change the client to set the
statement holdability based on the rules above and set it in the prepare
for BrokeredConnection30 (JDK14+) connections.  If I do this I think it
should all work ok and I would bypass the embedded bug.

Thanks

Kathey