You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Kathey Marsden <km...@sbcglobal.net> on 2005/06/10 23:04:44 UTC
Question about holdability and XA
I want to understand our behaviour with regard to default holdability
and XA. Maybe some sort of table.
Default Holdability for Brokered Connections obtained
from an XAConnection
In XA Transaction Outside XA Transaction
JDK13
JDK14
I actually think I may be seeing inconsistent behaviour in the case that
I am debugging, but I want to understand what the behaviour should be.
Thanks
Kathey
Re: Question about holdability and XA
Posted by Mamta Satoor <ms...@gmail.com>.
Just an FYI, I have made a JIRA entry for the jdk13 behavior
Derby-366 In jdk13, when a connection transitions from global transaction to
local transaction, its default holdability of HOLD_CURSORS_OVER_COMMIT is
not restored.
On 6/14/05, Kathey Marsden <km...@sbcglobal.net> wrote:
>
> Mamta Satoor wrote:
>
> > Hi Kathey,
> >
> > Just to summarize, the holdability table will look the same for
> > EmbedConnection as well as BrokeredConnection.
> >
> > 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)
> >
> > A Connection object will have it's holdability set to default which is
> > HOLD_CURSORS_OVER_COMMIT. When the connection joins a global
> > transaction, the holdability will get switched internally to
> > CLOSE_CURSORS_AT_COMMIT. When the connection leaves the global
> > transaction and becomes part of local transaction, the holdability is
> > restored to what it was before entering the global transaction. It
> > works this way in both jdk13 and jdk14+. One thing to keep in mind is
> > that PreparedStatement get the holdability from where they are
> > prepared and not where they are executed, for instance,
> > Start Local Transaction
> > Connection holdability (default - HOLD_CURSORS_OVER_COMMIT)
> > PreparedStatement (holdability will be HOLD_CURSORS_OVER_COMMIT)
> > ResultSet from PreparedStatement will have holdability as
> > HOLD_CURSORS_OVER_COMMIT
> > Start Global Transaction
> > Connection holdability switches to CLOSE_CURSORS_AT_COMMIT
> > Another ResultSet from PreparedStatement will attempt to have the
> > preparedStatement's holdability and that will cause exception because
> > Global transactions do not support holdable cursors.
> > Exit Global Transaction and become part of Local Transaction
> > Connection holdability back to HOLD_CURSORS_OVER_COMMIT
>
> Thanks Mamta for the summary. Good to understand the intended
> behviour while trying to get the client holdability to match the server.
>
> >
> > I have come across one bug with jdk13 though, where the holdability is
> > not getting restored back to HOLD_CURSORS_OVER_COMMIT when the
> > connection becomes part of local transaction after leaving global
> > transaction. I will enter a JIRA entry for it and start working on it.
>
> Of course this bug is probably why I am seeing xaSimplePostive.sql pass
> for jdk131 with client #:), but great to have that mystery solved too.
> .
> Kathey
>
>
>
Re: Question about holdability and XA
Posted by Kathey Marsden <km...@sbcglobal.net>.
Mamta Satoor wrote:
> Hi Kathey,
>
> Just to summarize, the holdability table will look the same for
> EmbedConnection as well as BrokeredConnection.
>
> 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)
>
> A Connection object will have it's holdability set to default which is
> HOLD_CURSORS_OVER_COMMIT. When the connection joins a global
> transaction, the holdability will get switched internally to
> CLOSE_CURSORS_AT_COMMIT. When the connection leaves the global
> transaction and becomes part of local transaction, the holdability is
> restored to what it was before entering the global transaction. It
> works this way in both jdk13 and jdk14+. One thing to keep in mind is
> that PreparedStatement get the holdability from where they are
> prepared and not where they are executed, for instance,
> Start Local Transaction
> Connection holdability (default - HOLD_CURSORS_OVER_COMMIT)
> PreparedStatement (holdability will be HOLD_CURSORS_OVER_COMMIT)
> ResultSet from PreparedStatement will have holdability as
> HOLD_CURSORS_OVER_COMMIT
> Start Global Transaction
> Connection holdability switches to CLOSE_CURSORS_AT_COMMIT
> Another ResultSet from PreparedStatement will attempt to have the
> preparedStatement's holdability and that will cause exception because
> Global transactions do not support holdable cursors.
> Exit Global Transaction and become part of Local Transaction
> Connection holdability back to HOLD_CURSORS_OVER_COMMIT
Thanks Mamta for the summary. Good to understand the intended
behviour while trying to get the client holdability to match the server.
>
> I have come across one bug with jdk13 though, where the holdability is
> not getting restored back to HOLD_CURSORS_OVER_COMMIT when the
> connection becomes part of local transaction after leaving global
> transaction. I will enter a JIRA entry for it and start working on it.
Of course this bug is probably why I am seeing xaSimplePostive.sql pass
for jdk131 with client #:), but great to have that mystery solved too.
.
Kathey
Re: Question about holdability and XA
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Kathey,
Just to summarize, the holdability table will look the same for
EmbedConnection as well as BrokeredConnection.
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)
A Connection object will have it's holdability set to default which is
HOLD_CURSORS_OVER_COMMIT. When the connection joins a global transaction,
the holdability will get switched internally to CLOSE_CURSORS_AT_COMMIT.
When the connection leaves the global transaction and becomes part of local
transaction, the holdability is restored to what it was before entering the
global transaction. It works this way in both jdk13 and jdk14+. One thing to
keep in mind is that PreparedStatement get the holdability from where they
are prepared and not where they are executed, for instance,
Start Local Transaction
Connection holdability (default - HOLD_CURSORS_OVER_COMMIT)
PreparedStatement (holdability will be HOLD_CURSORS_OVER_COMMIT)
ResultSet from PreparedStatement will have holdability as
HOLD_CURSORS_OVER_COMMIT
Start Global Transaction
Connection holdability switches to CLOSE_CURSORS_AT_COMMIT
Another ResultSet from PreparedStatement will attempt to have the
preparedStatement's holdability and that will cause exception because Global
transactions do not support holdable cursors.
Exit Global Transaction and become part of Local Transaction
Connection holdability back to HOLD_CURSORS_OVER_COMMIT
I have come across one bug with jdk13 though, where the holdability is not
getting restored back to HOLD_CURSORS_OVER_COMMIT when the connection
becomes part of local transaction after leaving global transaction. I will
enter a JIRA entry for it and start working on it.
thanks,
Mamta
On 6/12/05, Kathey Marsden <km...@sbcglobal.net> wrote:
>
> Mamta Satoor wrote:
>
> >
> > 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)
>
> I should have clarified that this was for a BrokeredConnection obtained
> from XAConnection. Your example shows a regular EmbedConnection.
> Should a BrokeredConnection obtained from an XAConnection default to
> HOLD_CURSORS_OVER_COMMIT in a local transaction?
>
> Thanks
>
> Kathey
>
>
>
>
>
>
>
>
>
Re: Question about holdability and XA
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Kathey Marsden wrote:
> Mamta Satoor wrote:
>
>
>>
>>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)
>
>
> I should have clarified that this was for a BrokeredConnection obtained
> from XAConnection. Your example shows a regular EmbedConnection.
> Should a BrokeredConnection obtained from an XAConnection default to
> HOLD_CURSORS_OVER_COMMIT in a local transaction?
Yes.
Dan.
Re: Question about holdability and XA
Posted by Kathey Marsden <km...@sbcglobal.net>.
Mamta Satoor wrote:
>
> 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)
I should have clarified that this was for a BrokeredConnection obtained
from XAConnection. Your example shows a regular EmbedConnection.
Should a BrokeredConnection obtained from an XAConnection default to
HOLD_CURSORS_OVER_COMMIT in a local transaction?
Thanks
Kathey
Re: Question about holdability and XA
Posted by Mamta Satoor <ms...@gmail.com>.
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 <km...@sbcglobal.net> 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
>
>
>
>
Re: Question about holdability and XA
Posted by Kathey Marsden <km...@sbcglobal.net>.
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
Re: Question about holdability and XA
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Kathey Marsden wrote:
> Daniel John Debrunner wrote:
>
>
>>Derby embedded does not support holdability for global transactions. I
>>think there was some earlier discussion of a possible bug where a global
>>transaction did end up with holdability true.
>>
>>
>>
>
> 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.
Dan.
Re: Question about holdability and XA
Posted by Kathey Marsden <km...@sbcglobal.net>.
Daniel John Debrunner wrote:
>Derby embedded does not support holdability for global transactions. I
>think there was some earlier discussion of a possible bug where a global
>transaction did end up with holdability true.
>
>
>
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?
Re: Question about holdability and XA
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Kathey Marsden wrote:
> I want to understand our behaviour with regard to default holdability
> and XA. Maybe some sort of table.
>
> Default Holdability for Brokered Connections obtained
> from an XAConnection
>
> In XA Transaction Outside XA Transaction
> JDK13
> JDK14
>
>
> I actually think I may be seeing inconsistent behaviour in the case that
> I am debugging, but I want to understand what the behaviour should be.
Derby embedded does not support holdability for global transactions. I
think there was some earlier discussion of a possible bug where a global
transaction did end up with holdability true.
Dan.