Spring Boot Jpa使用Sharding Jdbc实现分布式事务-两阶段提交-XA

1. 引入Maven依赖

Spring Boot用的2.3.0.RELEASE版本

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.0.0-alpha</version>
</dependency>

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-transaction-xa-core</artifactId>
    <version>5.0.0-alpha</version>
</dependency>

2.配置Sharding多数据源

spring:
  shardingsphere:
    enabled: true
    props:
      sql-show: true
    //两个数据源
    dataSource:
      names: ds0, ds1
      common:
        type: com.zaxxer.hikari.HikariDataSource
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://{ip1}:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=true&rewriteBatchedStatements=true&autoReconnect=true
        username: 
        password: 
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://{ip2}:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=true&rewriteBatchedStatements=true&autoReconnect=true
        username: 
        password: 
     //分片规则   
     rules:
       sharding:
        key-generators:
          //自定义主键生成算法名称
          my-test-key:
            type: SNOWFLAKE
            props:
              worker-id: 123
        //分库路由算法,根据实体类中的id值决定sql该使用哪个ds数据源名字
        sharding-algorithms:
          //自定义分库算法名称
          my-test-db-route:
            type: INLINE
            props:
              algorithm-expression: ds$->{id % 2}
        tables:
          //需要分库的表名(逻辑表名)
          users:
            //实际数据库表名(数据源.数据表名)
            actual-data-nodes: ds$->{0..1}.users
            //该表主键生成策略配置
            key-generate-strategy:
              column: id
              key-generator-name: my-test-key
            //该表分库策略配置
            database-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: my-test-db-route
            //分表策略配置,本次不需要
#            table-strategy:
#              standard:
#                sharding-column: create_time
#                sharding-algorithm-name: month-table

3.@ShardingTransactionType(TransactionType.XA)注解

    //只需要加上@ShardingTransactionType注解就可以实现分布式事务了
	@Transactional
    @ShardingTransactionType(TransactionType.XA)
    public void useTransation() {
    	//数据库中已有id为1的记录,会路由到ds1库中修改该记录
        UserDo userDo1 = UserDo.builder()
        		.id((long) 1)
                .nickName("abc")
                .build();
        this.userDoRepository.update(userDo1);

		//数据库中已有id为2的记录,会路由到ds0库中插入记录,插入id为2的记录会失败,事务回滚
        UserDo userDo2 = UserDo.builder()
        		.id((long) 2)
                .nickName("abc")
                .build();
        this.userDoRepository.save(userDo2);
    }

4.看看日志显示事务回滚

DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.i.i.CompositeTransactionManagerImp   : createCompositeTransaction ( 300000 ): created new ROOT transaction with id .tm165166936468300002

//开始分布式事务
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.icatch.imp.CompositeTransactionImp   : registerSynchronization ( com.atomikos.icatch.jta.Sync2Sync@683a18a5 ) for transaction .tm165166936468300002
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] .r.t.b.j.i.JtaTransactionCoordinatorImpl : Hibernate RegisteredSynchronization successfully registered with JTA platform
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] org.hibernate.engine.spi.ActionQueue     : Executing identity-insert immediately

//省略修改id为1的sql的执行,直接看导致失败的插入id为2的sql的执行
...

//逻辑sql
INFO [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] ShardingSphere-SQL                       : Logic SQL: insert into users (nick_name, id) values (?, ?)
INFO [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)

//实际sql,根据id为2路由到ds0数据源
INFO [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] ShardingSphere-SQL                       : Actual SQL: ds0 ::: insert into users (nick_name, id) values (?, ?) ::: [abc, 2]

DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] com.atomikos.icatch.jta.TransactionImp   : enlistResource ( org.apache.shardingsphere.transaction.xa.spi.SingleXAResource@39463856 ) with transaction .tm165166936468300002
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.icatch.imp.CompositeTransactionImp   : addParticipant ( XAResourceTransaction: 3139322E3136382E302E3139352E746D313635313636393336343638333030303032:3139322E3136382E302E3139352E746D33 ) for transaction .tm165166936468300002

//分布式事务start
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.datasource.xa.XAResourceTransaction  : XAResource.start ( 3139322E3136382E302E3139352E746D313635313636393336343638333030303032:3139322E3136382E302E3139352E746D33 , XAResource.TMNOFLAGS ) on resource resource-1-ds0 represented by XAResource instance org.apache.shardingsphere.transaction.xa.spi.SingleXAResource@39463856
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.icatch.imp.CompositeTransactionImp   : registerSynchronization ( com.atomikos.icatch.jta.Sync2Sync@bffca20 ) for transaction .tm165166936468300002

//执行sql报错
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : could not execute statement [n/a]

java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2' for key 'users.index'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at org.apache.shardingsphere.driver.executor.PreparedStatementExecutor$2.executeSQL(PreparedStatementExecutor.java:95)
	at org.apache.shardingsphere.driver.executor.PreparedStatementExecutor$2.executeSQL(PreparedStatementExecutor.java:91)
	...
	...
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)

WARN [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1062, SQLState: 23000
ERROR [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : Duplicate entry '2' for key 'users.index'
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] o.h.r.j.i.LogicalConnectionManagedImpl   : Initiating JDBC connection release from afterStatement
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] o.h.e.t.internal.TransactionImpl         : On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false

//跨库分布式事务回滚
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.icatch.imp.CompositeTransactionImp   : setRollbackOnly() called for transaction .tm165166936468300002
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.icatch.imp.CompositeTransactionImp   : setRollbackOnly() called for transaction .tm165166936468300002
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.datasource.xa.XAResourceTransaction  : XAResource.end ( 3139322E3136382E302E3139352E746D313635313636393336343638333030303032:3139322E3136382E302E3139352E746D33 , XAResource.TMSUCCESS ) on resource resource-1-ds0 represented by XAResource instance org.apache.shardingsphere.transaction.xa.spi.SingleXAResource@39463856
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.datasource.xa.XAResourceTransaction  : XAResource.rollback ( 3139322E3136382E302E3139352E746D313635313636393336343638333030303032:3139322E3136382E302E3139352E746D33 ) on resource resource-1-ds0 represented by XAResource instance org.apache.shardingsphere.transaction.xa.spi.SingleXAResource@39463856

//事务回滚完成
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.icatch.imp.CompositeTransactionImp   : rollback() done of transaction .tm165166936468300002
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] o.h.r.j.i.LogicalConnectionManagedImpl   : Initiating JDBC connection release from afterTransaction
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] com.atomikos.icatch.jta.Sync2Sync        : afterCompletion ( STATUS_ROLLEDBACK ) called  on Synchronization: org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization@3c04529a
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] com.atomikos.icatch.jta.Sync2Sync        : afterCompletion ( STATUS_ROLLEDBACK ) called  on Synchronization: org.apache.shardingsphere.transaction.xa.jta.datasource.XATransactionDataSource$1@78bd603b
DEBUG [,800c88db05514763,800c88db05514763,true] 48780 --- [nio-7200-exec-3] c.a.icatch.imp.CompositeTransactionImp   : rollback() done of transaction .tm165166936468300002

注意

对于Spring Boot Jpa来说，只要在原来本地事务处理的方法上加上@ShardingTransactionType注解就能完成跨库的分布式事务处理，但是要注意@ShardingTransactionType需要同Spring的@Transactional配套使用，事务才会生效。