I am trying to use 2 persistence units within the same transaction in a JEE 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
Any ideas?
