XAException in Websphere Application Server

Question

I'm stuck with a perplexing problem and I am hoping someone can help me out. I am getting this exception when trying some code that is deployed to our IBM Websphere Application Server instance. The funny part is that this works locally (running on Tomcat using Atomikos for JTA)

Any ideas what is going on here?

Error Message

[9/8/13 12:33:53:726 EDT] 0000023e WSRdbXaResour E   DSRA0304E:  XAException occurred. XAException contents and details are: 
The XA Error is            : -3
The XA Error message is    : A resource manager error has occured in the transaction branch.
The Oracle Error code is   : 2045
The Oracle Error message is: Internal XA Error
The cause is               : null.
[9/8/13 12:33:53:757 EDT] 0000023e WSRdbXaResour E   DSRA0302E:  XAException occurred.  Error code is: XAER_RMERR (-3).  Exception is: <null>

System details

• WAS Version: 8.5.0.0
• Oracle Driver Version: 11.2.0.3.0
• Oracle Database Version: 11.2.0.3.0
• OS: AIX 6.1 ppc64
• Spring version: 3.2.3
• Hibernate version: 4.1.9
• Javassist version: 3.17.1-GA

Stack trace (Snipped at first call to one of my services)

[9/8/13 12:33:53:634 EDT]     FFDC Exception:oracle.jdbc.xa.OracleXAException SourceId:com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl.start ProbeId:639 Reporter:com.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl@12c29f91
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.ibm.ws.rsadapter.spi.WSRdbXaResourceImpl.start(WSRdbXaResourceImpl.java:1536)
    at com.ibm.ejs.j2c.XATransactionWrapper.start(XATransactionWrapper.java:1478)
    at com.ibm.ws.Transaction.JTA.JTAResourceBase.start(JTAResourceBase.java:153)
    at com.ibm.tx.jta.impl.RegisteredResources.startRes(RegisteredResources.java:1001)
    at com.ibm.ws.tx.jta.RegisteredResources.enlistResource(RegisteredResources.java:1114)
    at com.ibm.ws.tx.jta.TransactionImpl.enlistResource(TransactionImpl.java:2186)
    at com.ibm.tx.jta.embeddable.impl.EmbeddableTranManagerSet.enlist(EmbeddableTranManagerSet.java:154)
    at com.ibm.ejs.j2c.XATransactionWrapper.enlist(XATransactionWrapper.java:732)
    at com.ibm.ejs.j2c.ConnectionManager.lazyEnlist(ConnectionManager.java:2678)
    at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.lazyEnlist(WSRdbManagedConnectionImpl.java:2591)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.beginTransactionIfNecessary(WSJdbcConnection.java:740)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:2789)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:2742)
    at sun.reflect.GeneratedMethodAccessor112.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
    at java.lang.reflect.Method.invoke(Method.java:611)
    at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at $Proxy139.prepareStatement(Unknown Source)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1854)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1831)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
    at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2516)
    at org.hibernate.loader.Loader.doList(Loader.java:2502)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
    at org.hibernate.loader.Loader.list(Loader.java:2327)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1247)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
    at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:254)
    at ca.statcan.icos.admin.service.WorkAssignmentBusinessService.getOperationalSupervisors(WorkAssignmentBusinessService.java:180)
<snip>

Thanks in advance

Answer 1

It turns out that the problem wasn't with WebSphere or Oracle at all, but our configuration. We use Spring AOP for transactions. We also have several web projects that we merge before deploying to WebSphere. During the merge we ended up with several (9 to be exact) applicationContext.xml files that setup AOP. Thus for every call to a method wrapped in a transaction we had 9 transactions created.

Eventually, we hit Oracle's limit of the number of global transactions that can participate in a single transaction and we got this error.

Word to the wise ... be careful with AOP managed transactions :)

Answer 2

In the most cases IBM support suggests to grant special permissions to an Oracle user when you have a deal with Oracle and WAS XA datasource.

This is required grants:

 grant select on pending_trans$ to <user>; 
 grant select on dba_2pc_pending to <user>;
 grant select on dba_pending_transactions to <user>;
 grant execute on dbms_system to <user>;  (If using Oracle 10.2.0.3 or lower JDBC driver)
 grant execute on dbms_xa to <user>; (If using Oracle 10.2.0.4 or higher JDBC driver)