Spring Boot / Atomikos / SQL Server Errors

[mrother-stoneriver]

I have a Spring Boot app packaged as a war file deployed to Wildfly. There are XA and nonXA data sources and JMS (Wildfly internal ActiveMQ). It deploys and runs fine with SQL Server or Oracle (configured in Wildfly standalone xml and obtained via JNDI).

Now we are trying to abstract the application server dependencies (transaction, data source, and jms). I have an external ActiveMQ serverup and running. I configured the jms, data sources and transaction management with Atomikos(5.0.7). When I deploy this configuration to Wildfly (testing only), it works file with oracle. However, I get errors when hitting SQL Server as the backend.

2021-02-03 09:37:09,235 DEBUG [com.atomikos.icatch.imp.CompositeTransactionManagerImp] (default task-1) suspend() for transaction TM1
2021-02-03 09:37:09,236 TRACE [com.atomikos.icatch.imp.CoordinatorImp] (default task-1) Coordinator TM1 entering state: ABORTING
2021-02-03 09:37:09,236 DEBUG [com.atomikos.datasource.xa.XAResourceTransaction] (default task-1) XAResource.end ( XID: 636F72657375697465544D313631323336323839363134323031373931:636F72657375697465544D3731 , XAResource.TMSUCCESS ) on resource springbatch.repositoryDataSource represented by XAResource instance  XAResourceID:1
2021-02-03 09:37:09,237 DEBUG [com.atomikos.datasource.xa.XAResourceTransaction] (default task-1) XAResource.rollback ( XID: 636F72657375697465544D313631323336323839363134323031373931:636F72657375697465544D3731 ) on resource dataSourceOne represented by XAResource instance  XAResourceID:1
2021-02-03 09:37:09,241 WARN  [com.atomikos.datasource.xa.XAResourceTransaction] (default task-1) XA resource 'springbatch.repositoryDataSource': rollback for XID 'XID: 636F72657375697465544D313631323336323839363134323031373931:636F72657375697465544D3731' raised -4: the supplied XID is invalid for this XA resource: javax.transaction.xa.XAException: The function ROLLBACK: failed. The status is: -4. Error: "*** SQL_XA DTC_ERROR Context: xa_rollback_entry, StatusCode=-4 (0xFFFFFFFC)"
    at deployment.coresuite-war-CSWC-SNAPSHOT.war//com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:653)
    at deployment.coresuite-war-CSWC-SNAPSHOT.war//com.microsoft.sqlserver.jdbc.SQLServerXAResource.rollback(SQLServerXAResource.java:803)
    at deployment.coresuite-war-CSWC-SNAPSHOT.war//com.atomikos.datasource.xa.XAResourceTransaction.rollback(XAResourceTransaction.java:382)

My data source configurations are

public DataSource dataSourceOne() throws NamingException {
    AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
    atomikosDataSourceBean.setXaDataSourceClassName(env.getRequiredProperty("db.driver.xa"));
    atomikosDataSourceBean.setUniqueResourceName("dataSourceOne");
    atomikosDataSourceBean.setMinPoolSize(10);
    atomikosDataSourceBean.setMaxPoolSize(100);
    atomikosDataSourceBean.setDefaultIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
    Properties properties = new Properties();
    properties.put("user",  env.getRequiredProperty("db.username"));
    properties.put("password",  env.getRequiredProperty("db.password"));
    String url = env.getRequiredProperty("db.url");
    properties.put("URL", env.getRequiredProperty("db.url"));
    atomikosDataSourceBean.setXaProperties(properties);
    return atomikosDataSourceBean;
}

public DataSource localDataSource() throws NamingException {
    AtomikosNonXADataSourceBean atomikosNonXADataSourceBean = new AtomikosNonXADataSourceBean();
    atomikosNonXADataSourceBean.setUniqueResourceName("dataSourceTwo");
    atomikosNonXADataSourceBean.setDriverClassName(env.getRequiredProperty("db.driver"));
    atomikosNonXADataSourceBean.setUser(env.getRequiredProperty("db.username"));
    atomikosNonXADataSourceBean.setPassword(env.getRequiredProperty("db.password"));
    atomikosNonXADataSourceBean.setUrl(env.getRequiredProperty("db.url"));
    atomikosNonXADataSourceBean.setMinPoolSize(10);
    atomikosNonXADataSourceBean.setMaxPoolSize(100);
    atomikosNonXADataSourceBean.setDefaultIsolationLevel(Isolation.READ_COMMITTED.value());
    return atomikosNonXADataSourceBean;
}
The problem appears to be only for the combination of Atomikos and SQL Server. Because it runs fine with the Wildfly resources for both database, the problem is not the configuration of SQL SErver. Does this require special parameters or configurations?

Additional stack trace:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
    at deployment.coresuite-war-CSWC-SNAPSHOT.war//com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
    at deployment.coresuite-war-CSWC-SNAPSHOT.war//com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:5448)
    at deployment.coresuite-war-CSWC-SNAPSHOT.war//com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1771)
    at deployment.coresuite-war-CSWC-SNAPSHOT.war//com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1029)
    at deployment.coresuite-war-CSWC-SNAPSHOT.war//org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
and

2021-02-03 16:26:24,297 WARN  [com.atomikos.datasource.xa.XAResourceTransaction] (default task-1) XA resource 'springbatch.repositoryDataSource': rollback for XID 'XID: 636F72657375697465544D313631323338373435313631383039323734:636F72657375697465544D3930' raised -4: the supplied XID is invalid for this XA resource: javax.transaction.xa.XAException: The function ROLLBACK: failed. The status is: -4. Error: "*** SQL_XA DTC_ERROR Context: xa_rollback_entry, StatusCode=-4 (0xFFFFFFFC)"

[GuyPardon]

Are you accessing the DB more than once in the same transaction?

[GuyPardon]

Revisiting this issue: there are several customer fixes we did for SQLServer. You may want to try our commercial edition for this as part of a free trial:

http://atomikos.com/Main/ExtremeTransactionsFreeTrial