EJB3 - using 2 persistence units within a transaction (Exception: Local transaction already has 1 non-XA Resource)

Question

I am trying to use 2 persistence units within the same transaction in a Java EE application deployed on Glassfish.

The 2 persistence units are defined in persistence.xml, as follows:

<persistence-unit name="BeachWater">
<jta-data-source>jdbc/BeachWater</jta-data-source>
...
<persistence-unit name="LIMS">
<jta-data-source>jdbc/BeachWaterLIMS</jta-data-source>
...

These persistence units correspond to JDBC resources and connection pools which I had defined in Glassfish as follows (include one here as both are identical apart from names & database connection info):

JDBC Resource:
JNDI Name: jdbc/BeachWaterLIMS
Pool Name: BEACHWATER_LIMS

Connection Pool:
Name: BEACHWATER_LIMS
Datasource Classname: com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource
Resource Type: javax.sql.ConnectionPoolDataSource

There are 3 stateless session beans, LimsServiceBean, AnalysisServiceBean and AnalysisDataTransformationServiceBean.

Here are the relevant snippets from LimsServiceBean:

@PersistenceContext(unitName = "LIMS")
EntityManager em;
...
public ArrayList<Sample> getLatestLIMSData() {
    Query q = em.createNamedQuery("Sample.findBySubTypeStatus");
    return new ArrayList<Sample>(q.getResultList());
}

From AnalysisServiceBean:

@PersistenceContext(unitName = "BeachWater")
EntityManager em;
...
public ArrayList<AnalysisType> getAllAnalysisTypes() {
    Query q = em.createNamedQuery("AnalysisType.findAll");
    return new ArrayList<AnalysisType>(q.getResultList());
}

And from AnalysisDataTransformationServiceBean:

@EJB
private AnalysisService analysisService;

@EJB
private LimsService limsService;

public void transformData() {
    List<AnalysisType> analysisTypes = analysisService.getAllAnalysisTypes();
    ArrayList<Sample> samples = limsService.getLatestLIMSData();

This call to limsService.getLatestLIMSData() caused the following exception:

     [exec] Caused by: javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean; nested exception is: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b60e-fcs (12/23/2008))): oracle.toplink.essentials.exceptions.DatabaseException
 [exec] Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: java.lang.IllegalStateException: Local transaction already has 1 non-XA Resource: cannot add more resources.

Having consulted this page, http://msdn.microsoft.com/en-us/library/ms378484.aspx (among many others), I tried changing the definition of the connection pools to:

Connection Pool:
Name: BEACHWATER_LIMS
Datasource Classname: com.microsoft.sqlserver.jdbc.SQLServerXADataSource
Resource Type: javax.sql.XADataSource

Ping via the Glassfish admin console succeeds, but call to analysisService.getAllAnalysisTypes() now throws an exception:

Caused by: javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean; nested exception is: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b60e-fcs (12/23/2008))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: javax.transaction.SystemException

The resource manager is doing work outside a global transaction javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Could not find stored procedure 'master..xp_sqljdbc_xa_init_ex'."

Any ideas?

Skellum

Possible duplicate of How to prevent "Local transaction already has 1 non-XA Resource" exception? – Barmecide

Answer 1

Change configuration of connection pool in Glassfish:

Connection Pool:
Name: BEACHWATER_LIMS
Datasource Classname: com.microsoft.sqlserver.jdbc.SQLServerXADataSource
Resource Type: javax.sql.XADataSource

Follow the steps on Senthil Balakrishnan's blog, "How to make MSSQL Server XA Datasource Work?" here, http://www.senthilb.com/2010/01/how-to-make-xa-datasource-work-in-mssql.html.

Restart Glassfish.

Skellum

It's important to provide an archived link: web.archive.org/web/20180823061808/http://www.senthilb.com/2010/… – Herod

Answer 2

In order to use two persistence units (and thus two datasources) within a transaction, you need indeed to use XA connections and to configure your pools accordingly (at least one of them, GlassFish supports the last agent optimization that allows to enlist one non XA resource, see http://docs.sun.com/app/docs/doc/820-7695/beanm?a=view). That was for the first error.

For the second error, it seems hard to say anything with the current level of details. Could you provide the stack trace (activate finer logging if required)?

Flattery

Thanks Pascal. I've since discovered more detail in the logs: The resource manager is doing work outside a global transaction javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Could not find stored procedure 'master..xp_sqljdbc_xa_init_ex'." I found a suggested solution here, senthilb.com/2010/01/… and have requested the relevant changes on the server. Will update here when I retest. – Skellum

@Skellum Thanks for the feedback. Don't hesitate to leave a comment after the update so that I get notified. But I have the feeling that you're on the right track! – Flattery

See answer added. Works perfectly now. Thanks again for your contribution. – Skellum

I was having a similar problem with WLS 10.3 and 2 datasources, Oracle and Informix. Very helpful. Thanks. – Boustrophedon