UserTransaction not rollsback MySql changes, but Oracle.
Hi,
I have the requirement to implement distributed transactions, since i am having three different datasources
Oracle, MySql. In every transaction i will be updating in both the Oracle and MySql databases.
So i decided to use the Distributed Transactions (XA) with JTA (i.e., javax.transaction.UserTransaction)
on the JBoss application server.
I have configured XA Datasources for Oracle as below (i.e., in oracle-xa-ds.xml )
i) oracle-xa-ds.xml (This i have placed in D:\jboss-4.0.2\server\default\deploy directory)
<xa-datasource>
<jndi-name>testXAOracleDS</jndi-name>
<track-connection-by-tx>true</track-connection-by-tx>
<isSameRM-override-value>false</isSameRM-override-value>
<xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
<xa-datasource-property name="URL">jdbc:oracle:thin:@10.77.6.113:1526:ARN</xa-datasource-property>
<xa-datasource-property name="User">irmuser</xa-datasource-property>
<xa-datasource-property name="Password">irmuser</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/>
<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
<type-mapping>Oracle9i</type-mapping>
<track-statements>false</track-statements>
</xa-datasource>
<depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager
ii) and mysql-xa-ds.xml (also placed at D:\jboss-4.0.2\server\default\deploy directory)
<xa-datasource>
<jndi-name>testXAMySqlDS</jndi-name>
<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
<xa-datasource-property name="URL">jdbc:mysql://10.77.6.113:3306/test</xa-datasource-property>
<user-name>root</user-name>
test
<track-connection-by-tx>true</track-connection-by-tx>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
<type-mapping>mySQL</type-mapping>
</xa-datasource>
I am not using any EJB stuff, I have simple DAO objects which are plain java class, queries the database using sql Connection, Statment ..etc.
where i am initiating the UserTransaction.
Here is my application code
//getting the UserTransaction with the help of utility called DAOUtil,
//where jndi lookup is performed for "java:comp/UserTransaction"
UserTransaction utx = DAOUtil.getUserTransaction();
//Declare the java.sql.Connection objects
Connection oraConn = null;
Connection mysqlConn =null;
try{
//Begin the UserTransaction
utx.begin();
//Get the oraConn performing jndi lookup for "java:testXAOracleDS"
oraConn = DAOUtil.getOraConnection();
//Get the mysqlConn performing jndi lookup for "java:testXAMySqlDS"
mysqlConn = DAOUtil.getMySqlConnection();
//Now insert service record into oracle database using the oraConn instance
//This reads the DTO and inserts the record
ServiceDAO.addService(serviceDTO, oraConn);
//Now insert service record into mysql database using the mysqlConn instance
ServiceDAO.addService(serviceDTO, mysqlConn);
//Now for testing purpose, Voluntarily throwing the exception as below
if(true){
throw new Exception("Throwing exception voluntarily....");
}
//commit the UserTransaction
utx.commit(); //Commit
}catch(Exception e){
if (utx != null) {
try {
logger.error("Exception occured rolling back the transaction ..."+e.getMessage());
utx.rollback(); //Rollback
} catch (SystemException sysex) {
logger.error(sysex);
}
}
}
If you look at the above code, i am throwing the exception voluntarily,
so it is coming to catch block and utx.rollback() is invoked....
Here database changes are rolledback from the Oracle database, but not from MySql database.
Can you please help ?? Did i miss any configuration ?? I am using the JBoss TransactionManager only.
i.e., jboss-service.xml have the following mbean configuration.
<!--
| The fast in-memory transaction manager.
-->
<mbean code="org.jboss.tm.TransactionManagerService"
name="jboss:service=TransactionManager"
xmbean-dd="resource:xmdesc/TransactionManagerService-xmbean.xml">
300
<!-- set to false to disable transaction demarcation over IIOP -->
true
<depends optional-attribute-name="XidFactory">jboss:service=XidFactory
I ran the following script to make Oracle XA compliant
<ORACLE_HOME>\javavm\install\initxa.sql
But i saw over net that MySql Connector is already supports XA directly (i.e.,mysql-connector-java-5.0.3.jar)
Appreciate your help in this regard.
Thanks in advance.
