Mysql学习

一、介绍

Seate控制分布式事务：
Seata 是一款开源的分布式事务解决方案，致力于提供高性能和简单易用的分布式事务服务。Seata 将为用户提供了 AT、TCC、SAGA 和 XA 事务模式，为用户打造一站式的分布式解决方案。

二、部署

2.1 下载

[下载地址]https://github.com/seata/seata/releases

2.2 配置

我们需要配置到nacos中，因此需要如下配置来修改。

2.2.1 数据库

建立seate数据库所需的表

create table `global_table` (
 `xid` varchar(128) not null,
 `transaction_id` bigint,
 `status` tinyint not null,
 `application_id` varchar(32),
 `transaction_service_group` varchar(32),
 `transaction_name` varchar(128),
 `timeout` int,
 `begin_time` bigint,
 `application_data` varchar(2000),
 `gmt_create` datetime,
 `gmt_modified` datetime,
 primary key (`xid`),
 key `idx_gmt_modified_status` (`gmt_modified`, `status`),
 key `idx_transaction_id` (`transaction_id`)
);

create table `branch_table` (
 `branch_id` bigint not null,
 `xid` varchar(128) not null,
 `transaction_id` bigint ,
 `resource_group_id` varchar(32),
 `resource_id` varchar(256) ,
 `lock_key` varchar(128) ,
 `branch_type` varchar(8) ,
 `status` tinyint,
 `client_id` varchar(64),
 `application_data` varchar(2000),
 `gmt_create` datetime,
 `gmt_modified` datetime,
 primary key (`branch_id`),
 key `idx_xid` (`xid`)
);

create table `lock_table` (
 `row_key` varchar(128) not null,
 `xid` varchar(96),
 `transaction_id` long ,
 `branch_id` long,
 `resource_id` varchar(256) ,
 `table_name` varchar(32) ,
 `pk` varchar(36) ,
 `gmt_create` datetime ,
 `gmt_modified` datetime,
 primary key(`row_key`)
);

还需要下面这张表，初始化在你当前的业务数据库中，用于AT 模式XID记录。与server端无关（注：业务数据库）

CREATE TABLE `undo_log` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `branch_id` bigint(20) NOT NULL,
 `xid` varchar(100) NOT NULL,
 `context` varchar(128) NOT NULL,
 `rollback_info` longblob NOT NULL,
 `log_status` int(11) NOT NULL,
 `log_created` datetime NOT NULL,
 `log_modified` datetime NOT NULL,
 `ext` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2.2.2 配置文件

1.registry.conf

registry {
  # file 、nacos 、eureka、redis、zk、consul、etcd3、sofa
  type = "nacos"
  loadBalance = "RandomLoadBalance"
  loadBalanceVirtualNodes = 10

  nacos {
    application = "seata-server"
    serverAddr = "127.0.0.1:8848"
    group = "SEATA_GROUP"
    namespace = ""
    cluster = "default"
    username = "nacos"
    password = "nacos"
  }
}
 
config {
  # file、nacos 、apollo、zk、consul、etcd3
  type = "nacos"
  nacos {
    serverAddr = "127.0.0.1:8848"
    namespace = ""
    group = "SEATA_GROUP"
    username = "nacos"
    password = "nacos"
  }
}

2.file.conf

store {
  ## store mode: file、db、redis
  mode = "db"
  ## database store property
  db {
    ## the implement of javax.sql.DataSource, such as DruidDataSource(druid)/BasicDataSource(dbcp)/HikariDataSource(hikari) etc.
    datasource = "druid"
    ## mysql/oracle/postgresql/h2/oceanbase etc.
    dbType = "mysql"
    driverClassName = "com.mysql.cj.jdbc.Driver"
    url = "jdbc:mysql://127.0.0.1:3307/seata?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true"
    user = "root"
    password = "123456"
    minConn = 5
    maxConn = 100
    globalTable = "global_table"
    branchTable = "branch_table"
    lockTable = "lock_table"
    queryLimit = 100
    maxWait = 5000
  }
}

2.3 启动

进入seate文件夹下，运行bin/seate-server.xx，注意，先运行nacos。
完成后，查看nacos服务是否发现seate-server服务，并且需要注意分组是否需要修改。

三、使用

3.1 maven

<dependency>
  <groupId>com.alibaba.cloud</groupId>
  <artifactId>spring-cloud-starter-alibaba-seata</artifactId>
  <version>2.1.1.RELEASE</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.47</version>
</dependency>
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.1.0</version>
</dependency>

3.2 配置文件

1. 把下载的seate项目中的file.conf， registry.conf复制到全部项目中resource目录下。
   file.conf文件中此处添加:

   service {
     ## vgroup_mapping.server-order-seata-service-group = "default"
     # 这个地方的my-group我们下面会在yml中配置。
     vgroup_mapping.my-group = "default"
     # 是我们seate刚才启动的地址和端口号（默认8091）
     default.grouplist = "10.205.17.142:8091"
   }

   service是和store同级配置（mode=‘file’不需要修改）

2. application.yml

   spring:
   application:
   name: server-order
   datasource:
   driver-class-name: com.mysql.jdbc.Driver
   username: root
   password: root
   url: jdbc:mysql://127.0.0.1:3306/order
   cloud:
   alibaba:
   seata:
   tx-service-group: my-group

   此处的my-group是需要和file.conf添加内容一样的。
   其他项目同样需要配置。

3.3 代码使用

配置数据库连接代理（此处使用的mybatis-plus）(涉及项目都需要配置)

@Configuration
public class DataSourceProxyConfig {

  @Bean
  @ConfigurationProperties(prefix = "spring.datasource")
  public DataSource dataSource() {
    return new DruidDataSource();
  }

  @Bean
  public DataSourceProxy dataSourceProxy(DataSource dataSource) {
    return new DataSourceProxy(dataSource);
  }

  @Bean
  public SqlSessionFactory sqlSessionFactoryBean(DataSourceProxy dataSourceProxy) throws Exception {
    SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
    sqlSessionFactoryBean.setDataSource(dataSourceProxy);

    //如果你使用的mybatis，下面隔开内容不需要配置
    //------------------------------------------------------------------------------
    //实体类所在包
    sqlSessionFactoryBean.setTypeAliasesPackage("com.example.springbootorder.pojo");
    MybatisConfiguration configuration = new MybatisConfiguration();
    configuration.setDefaultScriptingLanguage(MybatisXMLLanguageDriver.class);
    configuration.setJdbcTypeForNull(JdbcType.NULL);
    //-----------------------------------------------------------------------------


    sqlSessionFactoryBean.setConfiguration(configuration);
    return sqlSessionFactoryBean.getObject();
  }
}

业务中使用只需要在事务发起的地方加一个@GlobalTransactional

/**
* 分布式调用
*/
@GetMapping(value = "seate")
@GlobalTransactional
public String createOrder(@RequestParam(value = "userId") String userId){
  int i = ThreadLocalRandom.current().nextInt(9000000) + 1000000;
  UserOrder userOrder = new UserOrder();
  userOrder.setOrderNo(String.valueOf(i));
  userOrder.setUserId(userId);
  userOrder.setMoney(10);
  userOrderDao.insert(userOrder);
  String s = userFeign.reduceQuota(10, userId);
  return s;
}