EJB transaction - issue with two-phase commit

[Sethu Nambiar]

All,
I have a problem with the two-phase commit in my application.

Error: TransactionIm E WTRN0062E: An illegal attempt to use multiple resources that have only one-phase capability has occurred within a global transaction.

Brief history about my environment:
Server: WebSphere Platform 5.1 [BASE 5.1.1 a0426.01]
JDK - JDK 1.4.2
Wbsphere MQ

This is what I am doing in my code:
I have a MDB that recieves messages and execute in in two different database. The first database is Oracle and the second one is a proprietary databse and I have a JDBC2.0 compliant jdbc driver for it. Proprietary database jdbc driver is not capable of tow-phase commit.

For Oracle we are using XA datasource and our proprietary database jdbc driver doesn't support XA transaction and using our implementation of
javax.sql.ConnectionPoolDataSource.

ejb-jar.xml:
For this MDB transaction type is <transaction-type>Container</transaction-type>. and it has both database's datasource as a resource-ref. And the transaction attribute for the MDB is
<method-name>*</method-name>
<trans-attribute>Required</trans-attribute>

The QueueConnectionFactory for this MDB is XA enabled.

resource reference section in ejb-jar.xml
<resource-ref >
<res-ref-name>jdbc/OracleDataSource</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
<resource-ref >
<res-ref-name>jdbc/ProprietaryDataSource</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
The error happens when I execute the following statement in the proprietary database related code.
PreparedStatement stmt = con.preparedStatement(sql);
The complete error stack is given at the end of this topic.

What I have tried:
1. When I set the transaction attribute for MDB to NotSupported then everything works but we need to have Required attribute for this bean. So thats not an option for us.
2. I have removed the resource-ref for the proprietary database from the ejb-jar.xml, but still I get the same error.
3. If I use the direct connection by using DriverManager.getConnection() and execute the SQL in proprietary database then it works.

Quetion to all:
1. WHat determines the TransactionManager to enlist both of these database transaction in the global transaction? I guess if the proprietary database transaction is not enlisted in the global transaction then this error wouldn't have occurred.
2. How can I resolve this error? Any suggestions would be greatly appreciated.

[10/20/05 22:54:36:130 EDT] 8682e60 TransactionIm E WTRN0062E: An illegal attempt to use multiple resources that have only one-phase capability has occurred within a global transaction.
[10/20/05 22:54:36:130 EDT] 8682e60 LocalTransact E J2CA0030E: Method enlist caught com.ibm.ws.Transaction.IllegalResourceIn2PCTransactionException
at com.ibm.ws.Transaction.JTA.TransactionImpl.enlistResource(TransactionImpl.java:1761)
at com.ibm.ws.Transaction.JTA.TranManagerSet.enlistOnePhase(TranManagerSet.java:525)
at com.ibm.ejs.j2c.LocalTransactionWrapper.enlist(LocalTransactionWrapper.java:517)
at com.ibm.ejs.j2c.ConnectionEventListener.interactionPending(ConnectionEventListener.java:753)
at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.processInteractionPendingEvent(WSRdbManagedConnectionImpl.java:1505)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.beginTransactionIfNecessary(WSJdbcConnection.java:385)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1538)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1487)
at com.sethu.doExec(sql)
[ October 24, 2005: Message edited by: Sethu ]

[Roger Chung-Wee]

For two-phase commit to work, all resources must be XA-enabled. The only alternative that I know is where you have two DBs like Oracle which enable tunnelled access to the second DB via the first DB. I suggest migrating the proprietary database to Oracle.

[Sethu Nambiar]

Roger,
Thanks for your reply. But apparently migration is not an option because of several other things.

If the QueueConnectionFactory for the MDB is XA enabled would the transaction manager start an XA transaction? Or Is XA transaction is purely based on the type of data sources listed as a resource reference in the ejb descriptor? In other words what tells the transaction manager to start a XA transaction in the MDB?

really appreciate any suggestions.

thanks
Sethu

[Pradeep bhatt]

Weblogic server allows one of the resource to be a non XA compliant. Can you check if webspehere allows the same ?

[Scott Selikoff]

I've done 2-phase commit transactions in WebSphere many times, primarily between queues and regular databases, and there are some tricks to it.

First, the database itself must support XA transactions. I know in my case, for example, I had to manually install an update when using MS SQL server since the default version had this turned off. Second, the drivers for all the databases must be XA drivers. This is usually the easier item to setup since you can choose between XA and non-XA right in the Admin Console.

Third, WebSphere tries to be 'smart' about performing a 2-phase commit. Yes, it is possible for the transaction manager to start a non-XA transaction when you intended for it to be an XA-transaction because it is tries to avoid unnecessary 2-phase commits.

I don't remember whether its the access to the second db that triggers the 2-phase commit or merely the existence of the reference to 2 databases, but I'd bet on the first. I do remember there were cases where I had one set of code that used queues, another set of code that used SQL Server, and by themselves they were fine and supposedly supporting 2-phase commits, but put them together and all these 2-phase commit errors appeared that hadn't before even.

WebSphere is notorious for trying to be smarter than you and in the end leading to system instability.

[Sethu Nambiar]

Thanks Pradip for pointing to the right direction. Only Webpshere Application Server 6.x and later supports this mixing of 1-Phase commit and 2-Phase commit in one transaction. Unfortunatly I am using 5.1.1 version of websphere.

Here is the details of how to do it in websphere 6.x
http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/topic/com.ibm.websphere.express.doc/info/exp/lao/tasks/tla_ep.html

thanks
Sethu