Sharding-JDBC读写分离下分布式XA事务异常
背景
项目中使用了Shareding-JDBC的读写分离，事务使用的是XA类型的分布式事务，测试环境偶发性报错：
报错详情
报错信息
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@609990d4]
2022-10-14 11:38:35.366 host:169.254.128.72 WARN com.atomikos.datasource.xa.XAResourceTransaction - XA resource 'resource-1-master': resume for XID '3136392E3235342E3132382E37322E746D313636353731383731313933373030313131:3136392E3235342E3132382E37322E746D323137' raised -7: the XA resource has become unavailable
com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:583)
at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:568)
at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.start(MysqlXAConnection.java:509)
at com.mysql.jdbc.jdbc2.optional.SuspendableXAConnection.start(SuspendableXAConnection.java:172)
at org.apache.shardingsphere.transaction.xa.spi.SingleXAResource.start(SingleXAResource.java:88)
at com.atomikos.datasource.xa.XAResourceTransaction.resume(XAResourceTransaction.java:297)
at com.atomikos.icatch.jta.TransactionImp.enlistResource(TransactionImp.java:298)
at org.apache.shardingsphere.transaction.xa.manager.atomikos.AtomikosTransactionManager.enlistResource(AtomikosTransactionManager.java:59)
at org.apache.shardingsphere.transaction.xa.XAShardingTransactionManager.getConnection(XAShardingTransactionManager.java:88)
at org.apache.shardingsphere.shardingjdbc.jdbc.adapter.AbstractConnectionAdapter.createConnection(AbstractConnectionAdapter.java:164)
at org.apache.shardingsphere.shardingjdbc.jdbc.adapter.AbstractConnectionAdapter.createConnections(AbstractConnectionAdapter.java:138)
at org.apache.shardingsphere.shardingjdbc.jdbc.adapter.AbstractConnectionAdapter.getConnections(AbstractConnectionAdapter.java:127)
at org.apache.shardingsphere.shardingjdbc.jdbc.adapter.AbstractConnectionAdapter.getConnection(AbstractConnectionAdapter.java:96)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.MasterSlavePreparedStatement.<init>(MasterSlavePreparedStatement.java:64)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.MasterSlavePreparedStatement.<init>(MasterSlavePreparedStatement.java:51)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.MasterSlaveConnection.prepareStatement(MasterSlaveConnection.java:77)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
at com.sun.proxy.$Proxy124.prepareStatement(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at sun.reflect.GeneratedMethodAccessor530.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy198.update(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
at com.sun.proxy.$Proxy127.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy161.insertDataModel(Unknown Source)
at net.cnki.author.remuneration.service.impl.TestTwoServiceImpl.saveIn(TestTwoServiceImpl.java:90)
at net.cnki.author.remuneration.service.impl.TestTwoServiceImpl$$FastClassBySpringCGLIB$$c290ce6.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at net.cnki.author.remuneration.service.impl.TestTwoServiceImpl$$EnhancerBySpringCGLIB$$9666366c.saveIn(<generated>)
at net.cnki.author.remuneration.service.impl.TestServiceImpl.lambda$testTransaction$0(TestServiceImpl.java:111)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:562)
... 58 common frames omitted使用的Shareding-JDBC包：
<!--引入 Sharding-JDBC，引入后，即可配置读写分离-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<!-- 使用 Sharding-JDBC 下的 XA 事务 -->
<!-- 因为使用Sharding-JDBC配置读写分离数据源后，Spring自带的事务会失效，需配合使用 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-transaction-xa-core</artifactId>
<version>4.0.0-RC1</version>
</dependency>复现方式
使用多线程批量插入数据的方式来复现：
AServiceImpl.a()-->BService.insert()
AServiceImpl.a():
@Override
public boolean testMultiThreadTransaction() {
boolean isOk=true;
try {
ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor(100, 300, 600,
TimeUnit.SECONDS, new ArrayBlockingQueue<>(300), new ThreadPoolExecutor.CallerRunsPolicy());
for (int j = 0; j < 4000; j++) {
System.out.println("执行第"+j);
threadPoolExecutor.execute(()->{
try {
testTwoService.saveIn();
} catch (Exception e) {
log.error("testTransaction,for (int j = 0; j < 200; j++)异常，详情：{}",e);
e.printStackTrace();
}
});
}
threadPoolExecutor.shutdown();
threadPoolExecutor.awaitTermination(Long.MAX_VALUE, TimeUnit.DAYS);
} catch (Exception e) {
log.error("testTransaction,异常，详情：{}",e);
e.printStackTrace();
}
return isOk;
}BService.insert():
@Override
@Transactional(rollbackFor = Exception.class)
public boolean saveIn(){
boolean isOk=true;
try{
TransactionTypeHolder.set(TransactionType.XA);
long flag = Math.round(Math.random()*100);
testMapper.insertDataModel(UUIDUtils.getUUID(),flag);
}catch (Exception ex){
log.error("事务异常：{}",ex);
ex.printStackTrace();
isOk=false;
System.out.println("异常："+ex.getMessage());
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
}
return isOk;
}解决方法
配置PlatformTransactionManager和JdbcTemplate
package net.cnki.author.remuneration.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
@Configuration
@EnableTransactionManagement
public class TransactionConfiguration {
@Bean
public PlatformTransactionManager txManager(final DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean
public JdbcTemplate jdbcTemplate(final DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
