• 業(yè)務(wù)說明：這里我們創(chuàng)建三個服務(wù)，一個訂單服務(wù)，一個庫存服務(wù)，一個賬戶服務(wù)。
• 當(dāng)用戶下單時，會在訂單服務(wù)中創(chuàng)建一個訂單，然后通過遠(yuǎn)程調(diào)用庫存服務(wù)來扣減下單商品的庫存；再通過遠(yuǎn)程調(diào)用賬戶服務(wù)來扣減用戶賬戶里面的余額；最后在訂單服務(wù)中修改訂單狀態(tài)為已完成。該操作跨越三個數(shù)據(jù)庫，有兩次遠(yuǎn)程調(diào)用，很明顯會有分布式事務(wù)問題

6.1.數(shù)據(jù)庫表準(zhǔn)備

a.建三個數(shù)據(jù)庫

CREATE DATABASE seata_order;
CREATE DATABASE seata_storage;
CREATE DATABASE seata_account;

b.undo_log回滾日志表

• 1.按照上述的3個數(shù)據(jù)庫分別對應(yīng)的undo_log回滾日志表：
• 2.seata官網(wǎng)
• 3.undo_log建表SQL如下：AT模式專用的，其他模式都不需要

-- for AT mode you must to init this sql for you business database. the seata server not need it.
CREATE TABLE IF NOT EXISTS `undo_log`
(`branch_id`     BIGINT       NOT NULL COMMENT 'branch transaction id',`xid`           VARCHAR(128) NOT NULL COMMENT 'global transaction id',`context`       VARCHAR(128) NOT NULL COMMENT 'undo_log context,such as serialization',`rollback_info` LONGBLOB     NOT NULL COMMENT 'rollback info',`log_status`    INT(11)      NOT NULL COMMENT '0:normal status,1:defense status',`log_created`   DATETIME(6)  NOT NULL COMMENT 'create datetime',`log_modified`  DATETIME(6)  NOT NULL COMMENT 'modify datetime',UNIQUE KEY `ux_undo_log` (`xid`, `branch_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT ='AT transaction mode undo table';
ALTER TABLE `undo_log` ADD INDEX `ix_log_created` (`log_created`);

c.按照3個庫分別對應(yīng)業(yè)務(wù)表：

CREATE TABLE t_order(
`id` BIGINT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT(11) DEFAULT NULL COMMENT '用戶id',
`product_id` BIGINT(11)DEFAULT NULL COMMENT '產(chǎn)品id',
`count` INT(11) DEFAULT NULL COMMENT '數(shù)量',
`money` DECIMAL(11,0) DEFAULT NULL COMMENT '金額',
`status` INT(1) DEFAULT NULL COMMENT '訂單狀態(tài): 0:創(chuàng)建中; 1:已完結(jié)'
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SELECT * FROM t_order;

CREATE TABLE t_account(
`id` BIGINT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'id',
`user_id` BIGINT(11) DEFAULT NULL COMMENT '用戶id',
`total` DECIMAL(10,0) DEFAULT NULL COMMENT '總額度',
`used` DECIMAL(10,0) DEFAULT NULL COMMENT '已用賬戶余額',
`residue` DECIMAL(10,0) DEFAULT '0' COMMENT '剩余可用額度'
)ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO t_account(`id`,`user_id`,`total`,`used`,`residue`)VALUES('1','1','1000','0','1000');
SELECT * FROM t_account;

CREATE TABLE t_storage(
`id` BIGINT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`product_id` BIGINT(11) DEFAULT NULL COMMENT '產(chǎn)品id',
`total` INT(11) DEFAULT NULL COMMENT '總庫存',
`used` INT(11) DEFAULT NULL COMMENT '已用庫存',
`residue` INT(11) DEFAULT NULL COMMENT '剩余庫存'
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO t_storage(`id`,`product_id`,`total`,`used`,`residue`)VALUES('1','1','100','0','100');SELECT * FROM t_storage;

d.最終的效果：

庫表圖：

所有的SQL：

• 1.建seata_order庫 + 建t_order表 + undo_log表：

#orderCREATE DATABASE seata_order;USE seata_order;CREATE TABLE t_order(`id` BIGINT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,`user_id` BIGINT(11) DEFAULT NULL COMMENT '用戶id',`product_id` BIGINT(11)DEFAULT NULL COMMENT '產(chǎn)品id',`count` INT(11) DEFAULT NULL COMMENT '數(shù)量',`money` DECIMAL(11,0) DEFAULT NULL COMMENT '金額',`status` INT(1) DEFAULT NULL COMMENT '訂單狀態(tài): 0:創(chuàng)建中; 1:已完結(jié)')ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;SELECT * FROM t_order;-- for AT mode you must to init this sql for you business database. the seata server not need it.CREATE TABLE IF NOT EXISTS `undo_log`(`branch_id`     BIGINT       NOT NULL COMMENT 'branch transaction id',`xid`           VARCHAR(128) NOT NULL COMMENT 'global transaction id',`context`       VARCHAR(128) NOT NULL COMMENT 'undo_log context,such as serialization',`rollback_info` LONGBLOB     NOT NULL COMMENT 'rollback info',`log_status`    INT(11)      NOT NULL COMMENT '0:normal status,1:defense status',`log_created`   DATETIME(6)  NOT NULL COMMENT 'create datetime',`log_modified`  DATETIME(6)  NOT NULL COMMENT 'modify datetime',UNIQUE KEY `ux_undo_log` (`xid`, `branch_id`)) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT ='AT transaction mode undo table';ALTER TABLE `undo_log` ADD INDEX `ix_log_created` (`log_created`);

• 2.建seata_storage庫 + 建t_storage表 + undo_log表：

#storageCREATE DATABASE seata_storage;USE seata_storage;CREATE TABLE t_storage(`id` BIGINT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,`product_id` BIGINT(11) DEFAULT NULL COMMENT '產(chǎn)品id',`total` INT(11) DEFAULT NULL COMMENT '總庫存',`used` INT(11) DEFAULT NULL COMMENT '已用庫存',`residue` INT(11) DEFAULT NULL COMMENT '剩余庫存')ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO t_storage(`id`,`product_id`,`total`,`used`,`residue`)VALUES('1','1','100','0','100');SELECT * FROM t_storage;-- for AT mode you must to init this sql for you business database. the seata server not need it.CREATE TABLE IF NOT EXISTS `undo_log`(`branch_id`     BIGINT       NOT NULL COMMENT 'branch transaction id',`xid`           VARCHAR(128) NOT NULL COMMENT 'global transaction id',`context`       VARCHAR(128) NOT NULL COMMENT 'undo_log context,such as serialization',`rollback_info` LONGBLOB     NOT NULL COMMENT 'rollback info',`log_status`    INT(11)      NOT NULL COMMENT '0:normal status,1:defense status',`log_created`   DATETIME(6)  NOT NULL COMMENT 'create datetime',`log_modified`  DATETIME(6)  NOT NULL COMMENT 'modify datetime',UNIQUE KEY `ux_undo_log` (`xid`, `branch_id`)) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT ='AT transaction mode undo table';ALTER TABLE `undo_log` ADD INDEX `ix_log_created` (`log_created`);

• 3.建seata_account庫 + 建t_account表 + undo_log表：

#accountcreate database seata_account;use seata_account;CREATE TABLE t_account(`id` BIGINT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'id',`user_id` BIGINT(11) DEFAULT NULL COMMENT '用戶id',`total` DECIMAL(10,0) DEFAULT NULL COMMENT '總額度',`used` DECIMAL(10,0) DEFAULT NULL COMMENT '已用余額',`residue` DECIMAL(10,0) DEFAULT '0' COMMENT '剩余可用額度')ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;INSERT INTO t_account(`id`,`user_id`,`total`,`used`,`residue`)VALUES('1','1','1000','0','1000');SELECT * FROM t_account;-- for AT mode you must to init this sql for you business database. the seata server not need it.CREATE TABLE IF NOT EXISTS `undo_log`(`branch_id`     BIGINT       NOT NULL COMMENT 'branch transaction id',`xid`           VARCHAR(128) NOT NULL COMMENT 'global transaction id',`context`       VARCHAR(128) NOT NULL COMMENT 'undo_log context,such as serialization',`rollback_info` LONGBLOB     NOT NULL COMMENT 'rollback info',`log_status`    INT(11)      NOT NULL COMMENT '0:normal status,1:defense status',`log_created`   DATETIME(6)  NOT NULL COMMENT 'create datetime',`log_modified`  DATETIME(6)  NOT NULL COMMENT 'modify datetime',UNIQUE KEY `ux_undo_log` (`xid`, `branch_id`)) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT ='AT transaction mode undo table';ALTER TABLE `undo_log` ADD INDEX `ix_log_created` (`log_created`);

6.2.微服務(wù)編碼和落地實(shí)現(xiàn)：

整個業(yè)務(wù)流程：下訂單 -> 減庫存 ->扣余額 -> 改訂單狀態(tài)

a.MyBatis一鍵生成：

config.properties

#t_pay表包名
package.name=com.atguigu.cloud# mysql8.0
jdbc.driverClass = com.mysql.cj.jdbc.Driver
jdbc.url= jdbc:mysql://localhost:3306/db2024?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
jdbc.user = root
jdbc.password =123456# seata_order
#jdbc.driverClass = com.mysql.cj.jdbc.Driver
#jdbc.url = jdbc:mysql://localhost:3306/seata_order?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
#jdbc.user = root
#jdbc.password =123456# seata_storage
#jdbc.driverClass = com.mysql.cj.jdbc.Driver
#jdbc.url = jdbc:mysql://localhost:3306/seata_storage?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
#jdbc.user = root
#jdbc.password =123456# seata_account
#jdbc.driverClass = com.mysql.cj.jdbc.Driver
#jdbc.url = jdbc:mysql://localhost:3306/seata_account?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
#jdbc.user = root
#jdbc.password =123456

generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfigurationPUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN""http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"><generatorConfiguration><properties resource="config.properties"/><context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat"><property name="beginningDelimiter" value="`"/><property name="endingDelimiter" value="`"/><plugin type="tk.mybatis.mapper.generator.MapperPlugin"><property name="mappers" value="tk.mybatis.mapper.common.Mapper"/><property name="caseSensitive" value="true"/></plugin><jdbcConnection driverClass="${jdbc.driverClass}"connectionURL="${jdbc.url}"userId="${jdbc.user}"password="${jdbc.password}"></jdbcConnection><javaModelGenerator targetPackage="${package.name}.entities" targetProject="src/main/java"/><sqlMapGenerator targetPackage="${package.name}.mapper" targetProject="src/main/java"/><javaClientGenerator targetPackage="${package.name}.mapper" targetProject="src/main/java" type="XMLMAPPER"/><table tableName="t_pay" domainObjectName="Pay"><generatedKey column="id" sqlStatement="JDBC"/></table><!--  seata_order --><!--<table tableName="t_order" domainObjectName="Order"><generatedKey column="id" sqlStatement="JDBC"/></table>--><!--seata_storage--><!--<table tableName="t_storage" domainObjectName="Storage"><generatedKey column="id" sqlStatement="JDBC"/></table>--><!--seata_account--><!--<table tableName="t_account" domainObjectName="Account"><generatedKey column="id" sqlStatement="JDBC"/></table>--></context>
</generatorConfiguration>

b.修改公共cloud-api-commons模塊：

新增庫存和賬戶兩個Feign服務(wù)接口

• 1.StorageFeignApi.java

package com.atguigu.cloud.apis;
import com.atguigu.cloud.resp.ResultData;
import org.springframework.cloud.openfeign.FeignClient;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;/*** @auther zzyy* @create 2023-12-01 17:41*/
@FeignClient(value = "seata-storage-service")
public interface StorageFeignApi
{/*** 扣減庫存*/@PostMapping(value = "/storage/decrease")ResultData decrease(@RequestParam("productId") Long productId, @RequestParam("count") Integer count);
}

• 2.AccountFeignApi.java

package com.atguigu.cloud.apis;import com.atguigu.cloud.resp.ResultData;
import org.springframework.cloud.openfeign.FeignClient;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;/*** @auther zzyy* @create 2023-12-01 17:42*/
@FeignClient(value = "seata-account-service")
public interface AccountFeignApi
{//扣減賬戶余額@PostMapping("/account/decrease")ResultData decrease(@RequestParam("userId") Long userId, @RequestParam("money") Long money);
}

d.新建訂單Order微服務(wù)

e.新建庫存Storag微服務(wù)

c.新建賬戶Account微服務(wù)

6.3.測試

b.

---