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);
    }
}