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注解就能完成跨库的分布式事务处理，但是要