【Sharding-JDBC】（五）整合mybatis-plus 解决分布式事务（基于XA）

素颜马尾好姑娘i 2023-07-01 04:28 1阅读 0赞

## 官网解释： ##

## 功能 ##

*  支持数据分片后的跨库XA事务
 *  两阶段提交保证操作的原子性和数据的强一致性
 *  服务宕机重启后，提交/回滚中的事务可自动恢复
 *  SPI机制整合主流的XA事务管理器，默认Atomikos，可以选择使用Narayana和Bitronix
 *  同时支持XA和非XA的连接池
 *  提供spring-boot和namespace的接入端

## 不支持项 ##

*  服务宕机后，在其它机器上恢复提交/回滚中的数据

## 一、准备环境：  ##

![20200119102045613.png][]

**ps:两个order\_db分布在不同的服务器上，192.168.87.133节点的order\_db库中有三张表，t\_dict,t\_order\_1,t\_order\_2，192.168.87.134节点上的order\_db库中有两张表，t\_order\_1,t\_order\_2。**

## 二、[(三)Sharding-JDBC 整合mybatis-plus 水平分库、分表][Sharding-JDBC _mybatis-plus]改造 ##

**application.yml:**

#服务端口
    server:
      port: 56081
    #服务名
    spring:
      application:
        name: sharding-jdbc-examples
      http:
        encoding:
          enabled: true
          charset: utf-8
          force: true
      main:
        allow-bean-definition-overriding: true
      #shardingsphere相关配置
      shardingsphere:
        datasource:
          names: m1,m2   #配置库的名字，随意
          m1:   #配置目前m1库的数据源信息
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.jdbc.Driver
            url: jdbc:mysql://192.168.87.133:3306/order_db?useUnicode=true
            username: root
            password: 123456
          m2:
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.jdbc.Driver
            url: jdbc:mysql://192.168.87.134:3306/order_db?useUnicode=true
            username: root
            password: 123456
        sharding:
          tables:
            t_order:
              key-generator:
                column: user_id
                type: SNOWFLAKE
              actual-data-nodes: m$->{1..2}.t_order_$->{1..2}   # 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作m1数据源，否则操作m2。
              database‐strategy:  #分库策略
                inline:
                  sharding‐column: user_id
                  algorithm‐expression: m$->{user_id % 2 + 1}
              table‐strategy: #分表策略
                inline:
                  sharding‐column: order_id
                  algorithm‐expression: t_order_$->{order_id % 2 + 1}
          defaultDataSourceName: m1
        props:
          sql:
            show: true   #打印sql
    
    
    #日志打印
    logging:
      level:
        root: info
        org.springframework.web: info
        com.lucifer.sharding.dao: debug
        druid.sql: debug

**ps:由于t\_dist这张表只出现在192.168.87.133这个服务器节点的order\_db库中，因此需要配置属性defaultDataSourceName，设置默认数据库。否则会出现找不到数据库名为null的数据库名。**

**测试方法：t\_dict这个表只存在192.168.87.133的order\_db库中的，而t\_order\_\*这个存在于两个库中，既有分库策略也有分表策略。**

@Test
        public void add() {
            Dict dict = new Dict();
            dict.setCode("111");
            dict.setType("性别");
            dict.setValue("男");
            dictDao.insert(dict);
    
            //伪造异常
            int a=1/0;
    
            Order order = new Order();
            order.setPrice(BigDecimal.valueOf(0.1));
            order.setStatus("0");
            orderDao.insert(order);
    
        }

### **1.首先看下未使用事务的情况：** ###

2020-01-19 10:30:36.538  INFO 6980 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO t_dict   (dict_id, code, type, value) VALUES (?, ?, ?, ?) ::: [1218722393623670785, 111, 性别, 男]
    2020-01-19 10:30:36.572 DEBUG 6980 --- [           main] com.lucifer.sharding.dao.DictDao.insert  : <==    Updates: 1
    
    java.lang.ArithmeticException: / by zero
    
    	at com.lucifer.sharding.ShardingJdbcExamplesApplicationTests.add(ShardingJdbcExamplesApplicationTests.java:33)
    	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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

**控制台输出，可以看出在执行完m1库（192.168.87.133的order\_db库中的t\_dict）sql操作完后，报错。因此这个时候只有一条sql执行了。数据未回滚。**

![20200119103332917.png][]

### 2.使用事务的情况 ###

**   2.1.pom.xml:引入两个jar包**

<dependency>
                <groupId>io.shardingsphere</groupId>
                <artifactId>sharding-transaction-2pc-xa</artifactId>
                <version>3.1.0</version>
            </dependency>
            <dependency>
                <groupId>io.shardingsphere</groupId>
                <artifactId>sharding-transaction-spring</artifactId>
                <version>3.1.0</version>
            </dependency>

**或使用下面这个jar包**

<dependency>
                <groupId>io.shardingsphere</groupId>
                <artifactId>sharding-transaction-spring-boot-starter</artifactId>
                <version>3.1.0</version>
            </dependency>

** 2.2.springboot主配置类的SpringBootApplication注解去掉JtaAutoConfiguration这个配置类这个注入。**

/**
     * @author Lucifer
     */
    @MapperScan("com.lucifer.sharding.dao")
    @SpringBootApplication(exclude = JtaAutoConfiguration.class)
    public class ShardingJdbcExamplesApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(ShardingJdbcExamplesApplication.class, args);
        }
    
    }

** 2.3测试方法： @ShardingTransactionType(value = TransactionType.XA)**

**                           @Transactional(rollbackFor =Exception.class) 两个注解需要配合使用。**

@ShardingTransactionType(value = TransactionType.XA)
        @Transactional(rollbackFor = Exception.class)
        @Test
        public void add() {
            Dict dict = new Dict();
            dict.setCode("111");
            dict.setType("性别");
            dict.setValue("男");
            dictDao.insert(dict);
    
            int a=1/0;
    
            Order order = new Order();
            order.setPrice(BigDecimal.valueOf(0.1));
            order.setStatus("0");
            orderDao.insert(order);
    
        }

**控制台输出： **

2020-01-19 10:55:36.735  INFO 4660 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO t_dict   (dict_id, code, type, value) VALUES (?, ?, ?, ?) ::: [1218728685880786946, 111, 性别, 男]
    2020-01-19 10:55:36.757 DEBUG 4660 --- [           main] com.lucifer.sharding.dao.DictDao.insert  : <==    Updates: 1
    2020-01-19 10:55:36.776  INFO 4660 --- [           main] o.s.t.c.transaction.TransactionContext   : Rolled back transaction for test: [DefaultTestContext@563e4951 testClass = ShardingJdbcExamplesApplicationTests, testInstance = com.lucifer.sharding.ShardingJdbcExamplesApplicationTests@278fe428, testMethod = add@ShardingJdbcExamplesApplicationTests, testException = java.lang.ArithmeticException: / by zero, mergedContextConfiguration = [WebMergedContextConfiguration@4066c471 testClass = ShardingJdbcExamplesApplicationTests, locations = '{}', classes = '{class com.lucifer.sharding.ShardingJdbcExamplesApplication, class com.lucifer.sharding.ShardingJdbcExamplesApplication}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@140c9f39, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@7690781, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@be35cd9, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@193f604a], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.web.ServletTestExecutionListener.activateListener' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.populatedRequestContextHolder' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.resetRequestContextHolder' -> true]]
    
    java.lang.ArithmeticException: / by zero
    
    	at com.lucifer.sharding.ShardingJdbcExamplesApplicationTests.add(ShardingJdbcExamplesApplicationTests.java:39)

**数据库t\_dict也回滚了**。

![20200119110349309.png][]

**测试异常在后的情况：**

@ShardingTransactionType(value = TransactionType.XA)
        @Transactional(rollbackFor = Exception.class)
        @Test
        public void add() {
            Dict dict = new Dict();
            dict.setCode("111");
            dict.setType("性别");
            dict.setValue("男");
            dictDao.insert(dict);
    
            Order order = new Order();
            order.setPrice(BigDecimal.valueOf(0.1));
            order.setStatus("0");
            orderDao.insert(order);
    
            int a=1/0;
    
        }

**根据控制台输出，知道t\_dict执行成功， 而order的数据库操作最终是落在m2库的t\_order\_1表中。**

2020-01-19 11:13:13.146 DEBUG 11876 --- [           main] com.lucifer.sharding.dao.DictDao.insert  : ==>  Preparing: INSERT INTO t_dict ( dict_id, code, type, value ) VALUES ( ?, ?, ?, ? ) 
    2020-01-19 11:13:13.164 DEBUG 11876 --- [           main] com.lucifer.sharding.dao.DictDao.insert  : ==> Parameters: 1218733118769360898(Long), 111(String), 性别(String), 男(String)
    
    2020-01-19 11:13:19.666  INFO 11876 --- [           main] ShardingSphere-SQL                       : Actual SQL: m2 ::: INSERT INTO t_order_1   (order_id, price, status, user_id) VALUES (?, ?, ?, ?) ::: [1218733137404653570, 0.1, 0, 425612793970950145]
    2020-01-19 11:13:21.182 DEBUG 11876 --- [           main] c.lucifer.sharding.dao.OrderDao.insert   : <==    Updates: 1

** 查看数据库发现均回滚了。**

![20200119111710409.png][]

![20200119111732759.png][]

[20200119102045613.png]: https://img-blog.csdnimg.cn/20200119102045613.png
[Sharding-JDBC _mybatis-plus]: https://blog.csdn.net/qq_37495786/article/details/103781170
[20200119103332917.png]: https://img-blog.csdnimg.cn/20200119103332917.png
[20200119110349309.png]: https://img-blog.csdnimg.cn/20200119110349309.png
[20200119111710409.png]: https://img-blog.csdnimg.cn/20200119111710409.png
[20200119111732759.png]: https://img-blog.csdnimg.cn/20200119111732759.png