[jbossts-issues] [JBoss JIRA] (JBTM-2264) Error enlisting second xa resource on the same oracle instance but other schema

[Tom Jenkinson (JIRA)]

    [ https://issues.jboss.org/browse/JBTM-2264?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13008493#comment-13008493 ] 

Tom Jenkinson commented on JBTM-2264:
-------------------------------------

I should also mention, there is something strange that happens with Oracle when the patch is applied (I checked it affects BTM too). You can replicate the issue with the following raw XA and SQL code:
{code}
		final OracleXADataSource xaDataSource1 = new OracleXADataSource();
		((OracleXADataSource) xaDataSource1).setNetworkProtocol("tcp");
		((OracleXADataSource) xaDataSource1).setServerName(DB_HOST);
		((OracleXADataSource) xaDataSource1).setPortNumber(1521);
		((OracleXADataSource) xaDataSource1).setDatabaseName(DB_SID);
		((OracleXADataSource) xaDataSource1).setDriverType("thin");
		((OracleXADataSource) xaDataSource1).setUser(DB_USER1);
		((OracleXADataSource) xaDataSource1).setPassword(DB_USER1);
		final OracleXADataSource xaDataSource2 = new OracleXADataSource();
		((OracleXADataSource) xaDataSource2).setNetworkProtocol("tcp");
		((OracleXADataSource) xaDataSource2).setServerName(DB_HOST);
		((OracleXADataSource) xaDataSource2).setPortNumber(1521);
		((OracleXADataSource) xaDataSource2).setDatabaseName(DB_SID);
		((OracleXADataSource) xaDataSource2).setDriverType("thin");
		((OracleXADataSource) xaDataSource2).setUser(DB_USER2);
		((OracleXADataSource) xaDataSource2).setPassword(DB_USER2);
		XAConnection xaConnection1 = xaDataSource1.getXAConnection();
		XAResource xaResource1 = new IsSameRMOverrideXAResource(
				xaConnection1.getXAResource());
		Connection connection1 = xaConnection1.getConnection();
		XAConnection xaConnection2 = xaDataSource2.getXAConnection();
		Uid gtrid = new Uid();
		XidImple xid1 = new XidImple(gtrid, true, 1);
		xaResource1.start(xid1, XAResource.TMNOFLAGS);
		PreparedStatement preparedStatement1 = connection1
				.prepareStatement("INSERT INTO jta_test (some_string) VALUES ('test')");
		preparedStatement1.execute();
		System.out.println(xaResource1 + " sql for " + xaConnection1);
		XAResource xaResource2 = new IsSameRMOverrideXAResource(
				xaConnection2.getXAResource());
		Connection connection2 = xaConnection2.getConnection();
		XidImple xid2 = new XidImple(gtrid, true, 1);
		xaResource2.start(xid2, XAResource.TMNOFLAGS);
		PreparedStatement preparedStatement2 = connection2
				.prepareStatement("INSERT INTO jta_test (some_string) VALUES ('test')");
		System.out.println(xaResource2 + " sql for " + xaConnection2);
		preparedStatement2.execute();
		xaResource1.end(xid1, XAResource.TMSUCCESS);
		int prepare1 = xaResource1.prepare(xid1);
		xaResource2.end(xid2, XAResource.TMSUCCESS);
		int prepare2 = xaResource2.prepare(xid2);
		if (prepare1 != XAResource.XA_RDONLY) xaResource1.rollback(xid1);
		if (prepare2 != XAResource.XA_RDONLY) xaResource2.rollback(xid2);
{code}

That first prepare returns XAResource.XA_RDONLY. If you alter the code to use a different gtrid for each branch the resource manager returns XA_OK as expected (but obviously that is not what happens when the TM is involved as the gtrid is fixed for the TX). It appears that internally Oracle may be optimizing the SQL into the second prepare as when I reordered the prepare calls xid2 instead returned XA_RDONLY. It might be a setting on our database I am not sure. Either way you should verify that the app is performing the updates that you expect.

> Error enlisting second xa resource on the same oracle instance but other schema
> -------------------------------------------------------------------------------
>
>                 Key: JBTM-2264
>                 URL: https://issues.jboss.org/browse/JBTM-2264
>             Project: JBoss Transaction Manager
>          Issue Type: Bug
>          Components: JTA
>    Affects Versions: 5.0.3
>            Reporter: Evgeniy Smelik
>            Assignee: Tom Jenkinson
>         Attachments: sscce.zip, sscce.zip, test.log
>
>
> I've got an exception {{java.sql.SQLException: ConnectionImple.registerDatabase - ARJUNA017017: enlist of resource failed}} while preparing statement in the second connection within the same oracle instance but other schema.
> Whole stack trace:
> {noformat}
> oracle.jdbc.xa.OracleXAException
> 	at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1110)
> 	at oracle.jdbc.xa.client.OracleXAResource.start(OracleXAResource.java:240)
> 	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:741)
> 	at com.arjuna.ats.internal.jdbc.ConnectionImple.registerDatabase(ConnectionImple.java:983)
> 	at com.arjuna.ats.internal.jdbc.ConnectionImple.prepareStatement(ConnectionImple.java:179)
> 	at SimpleJdbcTest.insert(SimpleJdbcTest.java:46)
> 	at SimpleJdbcTest.main(SimpleJdbcTest.java:36)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> 	at java.lang.reflect.Method.invoke(Method.java:606)
> 	at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)
> java.sql.SQLException: ConnectionImple.registerDatabase - ARJUNA017017: enlist of resource failed
> 	at com.arjuna.ats.internal.jdbc.ConnectionImple.registerDatabase(ConnectionImple.java:1003)
> 	at com.arjuna.ats.internal.jdbc.ConnectionImple.prepareStatement(ConnectionImple.java:179)
> 	at SimpleJdbcTest.insert(SimpleJdbcTest.java:46)
> 	at SimpleJdbcTest.main(SimpleJdbcTest.java:36)
> {noformat}
> (Detail log and SSCCE are attached).
> I use jboss transaction manager in standaloine application just to test jboss JTA implementation. The same code works well if one and second data sources use own (different) database instances.
> I note that atomikos and bitronix JTA implementation works correctly in the same environment irrespectively single oracle instance is used or not.
> I found similar problem [here|http://stackoverflow.com/questions/23617179/jboss-6-1-unable-to-get-connection-from-pool].



--
This message was sent by Atlassian JIRA
(v6.3.1#6329)