Using UserTransaction from a Mbean doesn't commit against XA

[alexoscar]

I am not sure if I post this to JMX forum or here, hoping this is the right place. I've defined an Xa Oracle Datasource and I'm using UserTransaction to commit, but it is not happening (I am not getting any error or something) Here's the simplified code below (the actual code involves a distributed transaction, that's why I need to use UserTransaction, If I change the ut.commit() below with the m_db_conn.commit(); it would commit, so please don't suggest that)

try {
 InitialContext iniCtx = new InitialContext();
 DataSource ds = (DataSource) iniCtx.lookup("java:/OracleDS/XA_TX/MyDB");
 UserTransaction ut = (UserTransaction) iniCtx.lookup("UserTransaction");
 Connection m_db_conn = ds.getConnection();
 m_db_conn.setAutoCommit(false);
 String storedProc = "begin insert into test values (to_char(sysdate,'SSSSS')) ; end;";
 CallableStatement update_stmt = m_db_conn.prepareCall(storedProc);
 ut.begin();
 update_stmt.execute();
 ut.commit();
 System.out.println("{{{Insert DB}}}}");
 } catch (Exception e) { System.out.println("Error testUT "+ e.getMessage()); }

And here's the -ds.xml file

<datasources>
 <xa-datasource>
 <jndi-name>OracleDS/XA_TX/MyDB</jndi-name>
 <isSameRM-override-value>false</isSameRM-override-value>
 <xa-datasource-property name="URL">jdbc:oracle:thin:@myacme.com:1521:orcl</xa-datasource-property>
 <track-connection-by-tx>true</track-connection-by-tx>
 <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
 <xa-datasource-property name="User">scott</xa-datasource-property>
 <xa-datasource-property name="Password">tiger</xa-datasource-property>
 <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
 <!-- valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name -->
 <!-- Checks the Oracle error codes and messages for fatal errors -->
 <!-- exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name -->
 <!-- Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa -->
 <no-tx-separate-pools/>
 </xa-datasource>

</datasources>

[raist_majere]

I've never used what you're trying to do, but in a book I have says that you get access to JTA looking up "java:comp/UserTransaction", not "UserTransaction" as you have in your code. Try it, but don't know if this is the problem you have...

[fns]

Hi Alex,

I guess that your problem is related to the Xid (transaction identifier) that is generated by the default factory used by JBoss. In fact, the problem is that Oracle's XA driver only work with their own Xid implementation (see http://docs.jboss.org/admin-devel/Chap4.html#0_63556 ). I haven't needed to do anything like that before, but as far as I understood, you will have to find out wich class can generate those Xids. After that, you will have to develop a MBean that can use this class to generate the Xids.

best regards,

Fábio
PS. I would be glad if you could tell me if that worked out.

[alexoscar]

Fabio,
Yes I was looking at the same doucment and maybe I misread the last part 'UserTransaction'. So my assumption was I can get a UserTransaction object using the JNDI lookup and use it.
The following URL is not Jboss specific but I took as an good example of use of UserTransaction
http://www.codenotes.com/articles/articleAction.aspx?articleID=77

Maybe my XA resources need to explicitly enlisted to the Transaction Manager ? Or, simply Can't I use the UserTransaction in an Mbean, period.
I don't know enough on the subject so any feedback, direction would be greatly appreciated.

[adrian.brock]

READ THIS FIRST for how to give us some relevent information.

Three notes:
1) Commit/rollback should always be in a finally block
2) You shouldn't be playing with the auto commit flag of connections
that are going to be part of a managed transaction.
3) OracleXA doesn't like being used both inside and outside a transaction
so I'd start the user transaction before retrieving the connection.