Oracle JDBC XA question

[spiderbug]

Hello,
While using the Oracle (Java)XA interfaces, I came across this
situation-

1. Start a transaction, enlist the connection in it (Effectively get
an XAConnection from an XADataSource, get the XAresource and call
start on it with some Xid).
2. Perform some DB operations/queries on the java.sql.Connection
obtained from the XAConnection.
3. End the transaction (not commit it, just xaresource.end) - with
flag TM_SUCCESS
4. Perform some other operations/queries on the same JDBC connection
as in 2.

Operation (4) fails with the error - java.sql.SQLException: Not in a
transaction. Now my question is - since I've already demarcated the
transactional boundaries with the start and end calls on the
XAresource, why cannot I perform any other operations outside the
transaction even if I've not committed the tx? The JTA spec says these
two calls are supposed to do just that. Is it an Oracle specific
problem or am I missing a point here?

Thanks in advance
~spiderbug~

 

[Lee Fesperman]

Hello,
While using the Oracle (Java)XA interfaces, I came across this
situation-

1. Start a transaction, enlist the connection in it (Effectively get
an XAConnection from an XADataSource, get the XAresource and call
start on it with some Xid).
2. Perform some DB operations/queries on the java.sql.Connection
obtained from the XAConnection.
3. End the transaction (not commit it, just xaresource.end) - with
flag TM_SUCCESS
4. Perform some other operations/queries on the same JDBC connection
as in 2.

Operation (4) fails with the error - java.sql.SQLException: Not in a
transaction. Now my question is - since I've already demarcated the
transactional boundaries with the start and end calls on the
XAresource, why cannot I perform any other operations outside the
transaction even if I've not committed the tx? The JTA spec says these
two calls are supposed to do just that. Is it an Oracle specific
problem or am I missing a point here?

It's not totally explicit, however XA transaction boundaries are really demarcated by
start and commit/rollback. The DBMS is obligated to maintain the XA transaction
information until then. Your scheme would force the DBMS to support two separate
transactions for a connection. That's a messy and not really useful capability.

Containers (app servers, ...) won't allow this scenario to happen.

 

[spiderbug]

It's not totally explicit, however XA transaction boundaries are really demarcated by

start and commit/rollback. The DBMS is obligated to maintain the XA transaction
information until then. Your scheme would force the DBMS to support two separate
transactions for a connection. That's a messy and not really useful capability.

Containers (app servers, ...) won't allow this scenario to happen.

Thanks a lot for replying, Lee. As far as the JDBC and JTA specs both
say (I'm not sure about what the X/Open spec says) the transaction
boundaries are demarcated by the start and end calls.

<quote>Your scheme would force the DBMS to support two separate
transactions for a connection. </quote>
That's what the XAresource interface is supposed to do, IMO. In fact
the JTA spec quite clearly says this in an example - but not
concurrently. Of course, here 'only one transaction' means only one
transaction at a single time - but what about one after the other,
without commiting the first and use the same XAresource? Also, since
the connection and the xaresource are tightly bound as far as marking
transactional boundaries are concerned, there cannot be a case where
the connection is operating in a different transaction than what the
XAresource has been informed of through the start call.
Hope the above paragraph was not confusing!! - Am a bit confused
myself about the whole thing.

Regards
~spiderbug~

 

[Lee Fesperman]

Thanks a lot for replying, Lee. As far as the JDBC and JTA specs both
say (I'm not sure about what the X/Open spec says) the transaction
boundaries are demarcated by the start and end calls.

<quote>Your scheme would force the DBMS to support two separate
transactions for a connection. </quote>
That's what the XAresource interface is supposed to do, IMO. In fact
the JTA spec quite clearly says this in an example - but not
concurrently. Of course, here 'only one transaction' means only one
transaction at a single time - but what about one after the other,
without commiting the first and use the same XAresource? Also, since
the connection and the xaresource are tightly bound as far as marking
transactional boundaries are concerned, there cannot be a case where
the connection is operating in a different transaction than what the
XAresource has been informed of through the start call.
Hope the above paragraph was not confusing!! - Am a bit confused
myself about the whole thing.

Good! Your response made me dig a little. I had mostly just consulted the XA spec, which
was vague in that area. Looking deeper, I checked our (FirstSQL/J ORDBMS) XA code, which
does support mixing global and local transactions in this manner, and checked the JTA
spec.

Section 3.4.7 "Local and Global Transactions" in my JTA spec (1.01B) encourages a
resource adapter (JDBC Driver & RDBMS) to support local transactions on a connection
after being 'disassociated' from a global transaction with end(). OTOH, the section does
also permits an RDBMS to disallow it and throw a native exception. Thus, Oracle seems to
be compliant with JTA in this area.

I hope that clears up the confusion I promulgated with my earlier response.

 

[Guy Pardon]

Hi,

Hello,
While using the Oracle (Java)XA interfaces, I came across this
situation-

1. Start a transaction, enlist the connection in it (Effectively get
an XAConnection from an XADataSource, get the XAresource and call
start on it with some Xid).
2. Perform some DB operations/queries on the java.sql.Connection
obtained from the XAConnection.
3. End the transaction (not commit it, just xaresource.end) - with
flag TM_SUCCESS
4. Perform some other operations/queries on the same JDBC connection
as in 2.

Operation (4) fails with the error - java.sql.SQLException: Not in a
transaction. Now my question is - since I've already demarcated the
transactional boundaries with the start and end calls on the
XAresource, why cannot I perform any other operations outside the
transaction even if I've not committed the tx? The JTA spec says these
two calls are supposed to do just that. Is it an Oracle specific
problem or am I missing a point here?

Thanks in advance
~spiderbug~

Oracle deviates from the standard XA/JTA specs in the sense that it
rejects any other transaction on the connection until you effectively
commit or rollback the previously started XA transaction.

More specifically: Oracle requires the following on the
XAConnection/XAResource:

start ( xid1 );
end ( xid1 );
//optional prepare ( xid1)
commit ( xid1 ) or rollback ( xid 1 )

//only now start another tx
start ( xid 2 );
....

Whereas the XA/JTA specs explicitly state that the following is legal:

start ( xid1 );
end ( xid1 );
start ( xid2 );
commit ( xid1 );

I am afraid this is something you'll have to live with;-)

Best,
Guy

http://www.atomikos.com: The Transaction Management Company

 

[spiderbug]

Good! Your response made me dig a little. I had mostly just consulted the XA spec, which

was vague in that area. Looking deeper, I checked our (FirstSQL/J ORDBMS) XA code, which
does support mixing global and local transactions in this manner, and checked the JTA
spec.

Section 3.4.7 "Local and Global Transactions" in my JTA spec (1.01B) encourages a
resource adapter (JDBC Driver & RDBMS) to support local transactions on a connection
after being 'disassociated' from a global transaction with end(). OTOH, the section does
also permits an RDBMS to disallow it and throw a native exception. Thus, Oracle seems to
be compliant with JTA in this area.

I hope that clears up the confusion I promulgated with my earlier response.

Hmm - thanks for your response...But even if the resource adapter
decides to not allow mixing of transactions, it should support
subsequent transactions one after the other and without committing the
previous one (committing, not ending - it definitely has to be ended).
xares.start(xid1)
xares.end(xid1)
xares.start(xid2)
xares.end(xid2)
.....prepare..commit....etc etc..
Anyway I checked a few other XA drivers - they all differ in their
behaviour as far as mixing is concerned. So I guess that's it. But
even then, Oracle's error messages leave a lot to be desired.

Regards
~spiderbug~

 

[Lee Fesperman]

Hmm - thanks for your response...But even if the resource adapter
decides to not allow mixing of transactions, it should support
subsequent transactions one after the other and without committing the
previous one (committing, not ending - it definitely has to be ended).
xares.start(xid1)
xares.end(xid1)
xares.start(xid2)
xares.end(xid2)
....prepare..commit....etc etc..

Agreed. In the JTA spec (prior section: 3.4.6), it states that resource adapter must be
allow mixing of global transaction branches (Xid's) on the same XAResource in the manner
above.

Anyway I checked a few other XA drivers - they all differ in their
behaviour as far as mixing is concerned. So I guess that's it. But
even then, Oracle's error messages leave a lot to be desired.

It seems that a number of RDBMS vendors are not compliant in their XA implementation.
That's the sense I've gotten talking to Transaction Manager vendors.