15《Spring Boot 入门教程》多数据源与分布式事务

一个项目中使用多个数据源的需求，我们在日常工作中时常会遇到。

以商城系统为例，有一个 MySQL 的数据库负责存储交易数据。公司还有一套 ERP 企业信息化管理系统，要求订单信息同步录入 ERP 数据库，便于公司统一管理，而该 ERP 系统采用的数据库为 SQL Server 。

此时，就可以在 Spring Boot 项目中配置多个数据源。另外，使用多数据源后，需要采用分布式事务来保持数据的完整性。

本小节我们使用 Spring Boot 开发一个商城系统的订单生成功能，订单信息同时进入 MySQL 与 SQL Server 数据库。

首先创建 MySQL 数据库 shop ，并新建订单表 order ，表结构如下：

order 表结构

然后创建 SQL Server 数据库 erpshop ，并新建订单表 erp_order ，表结构如下。注意 id 是自增长的唯一标识，out_id 是对应订单在 MySQL 数据库中的唯一标识，以便在两个库中比对订单。

erp_order 结构

接下来，我们开始实现 Spring Boot 后端项目，数据持久层采用 MyBatis 框架，同时访问两个数据源。

Spring Boot 版本选择 2.2.5 ，Group 为 com.imooc ， Artifact 为 spring-boot-multidb，生成项目后导入 Eclipse 开发环境。

我们引入热部署依赖、 Web 依赖、数据库访问相关依赖及测试相关依赖，具体如下：

实例：

由于我们要同时访问两个数据库，所以需要在配置文件中添加两个数据源的配置信息。注意配置多数据源时， url 配置需要使用spring.datasource.db1.jdbc-url=xxx的形式。

实例：

多个数据源的情况下， 我们需要通过配置类，将数据源注册为组件放入 Spring 容器中。

实例：

通过这个配置类， Spring 容器中就有两个数据源组件，这两个组件分别采用spring.datasource.db1和spring.datasource.db2开头的配置信息。所以通过这两个组件，就能分别 *** 作 MySQL 数据源 1 和 SQL Sever 数据源 2 。

多数据源情况下， MyBatis 中的关键组件 SqlSessionFactory 和 SqlSessionTemplate 也需要单独配置，我们需要为两个数据源分别配置一套组件。

实例：

通过上面的配置类，com.imooc.springbootmultidb.mapper1包中的 DAO 数据访问接口会自动调用 sqlSessionTemplate1 组件实现具体数据库 *** 作，而 sqlSessionTemplate1 *** 作的数据源已经通过配置类设置为 db1 。同时， DAO 数据访问接口对应的映射文件已经指定到classpath:mapper1/目录去寻找。这样数据源 – DAO 数据访问接口 – 映射文件三者的对应关系就建立起来了。

数据源 2 的配置方法是一样的， com.imooc.springbootmultidb.mapper2包中的 DAO 数据访问接口会自动调用 sqlSessionTemplate2 组件，其 *** 作的数据源即为 db2 ，其对应的映射文件指定到classpath:mapper2/目录去寻找。

实例：

数据访问接口的位置已经在配置类指定，首先在com.imooc.springbootmultidb.mapper1创建 OrderDao ， *** 作的是数据源 1 中的 order 表。

实例：

然后在com.imooc.springbootmultidb.mapper2创建 ErpOrderDao ， *** 作的是数据源 2 中的 erporder 表。

实例：

这两个接口中使用的数据对象比较简单，代码如下：

实例：

分别针对 OrderDao 、 ErpOrderDao 编写对应的映射文件，然后按照配置类指定的位置，两个文件分别放到resources/mapper1和resources/mapper2目录下。

实例：

实例：

数据 *** 作接口与对应的映射文件均已编写完毕，现在可以通过测试类进行多数据源测试了，我们在测试类中同时向两个库插入记录。

实例：

运行测试方法后，两个数据库表中均新增数据成功，这样我们就成功的使用 Spring Boot 同时 *** 作了两个数据源。

采用多数据源之后，事务的实现方式也随之发生变化。当某个数据源 *** 作出现异常时，该数据源和其他数据源的事务都需要回滚。这种涉及多个数据源的事务，称为分布式事务，接来下我们就来具体实现一下。

在 pom.xml 引入 Atomikos 事务管理器相关的依赖项， Atomikos 是一个开源的事务管理器，支持分布式事务。

实例：

需要将默认的数据源更换为支持分布式事务的数据源， MySQL 对应的数据源为 MysqlXADataSource ， SQL Server 对应的数据源为 SQLServerXADataSource 。

实例：

继续修改 DataSourceConfig 类，在其中配置分布式事务管理器组件。当项目中使用事务时，会通过配置的分布式事务管理器管理分布式事务 *** 作。

实例：

在测试方法上添加@Transactional开启事务，然后在两个数据源 *** 作中间模拟抛出异常。

实例：

此时运行测试类，可以发现数据源 1 的事务已回滚，验证成功！

在开发 Spring Boot 项目时，如果默认配置满足不了我们的需求，可以通过手工配置组件实现我们需要的功能。这些组件可能是各个公司提供的，我们根据相应文档，为其配置各个属性即可。

MySQL的权限有哪些

一．权限表

mysql数据库中的3个权限表：user 、db、 host

权限表的存取过程是：

1)先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证；

2)通过权限验证，进行权限分配时，按照useràdbàtables_privàcolumns_priv的顺序进行分配。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

二．MySQL各种权限（共27个）

（以下 *** 作都是以root身份登陆进行grant授权，以p1@localhost身份登陆执行各种命令。）

1. usage

连接（登陆）权限，建立一个用户，就会自动授予其usage权限（默认授予）。

mysql>grant usage on *.* to ‘p1′@’localhost’ identified by ‘123′

该权限只能用于数据库登陆，不能执行任何 *** 作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。

2. select

必须有select的权限，才可以使用select table

mysql>grant select on pyt.* to ‘p1′@’localhost’

mysql>select * from shop

3. create

必须有create的权限，才可以使用create table

mysql>grant create on pyt.* to ‘p1′@’localhost’

4. create routine

必须具有create routine的权限，才可以使用{create |alter|drop} {procedure|function}

mysql>grant create routine on pyt.* to ‘p1′@’localhost’

当授予create routine时，自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：

mysql>show grants for ‘p1′@’localhost’

+—————————————————————————+

Grants for p1@localhost

+————————————————————————–+

| GRANT USAGE ON *.* TO ‘p1′@’localhost’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′ |

| GRANT SELECT, CREATE, CREATE ROUTINE ON `pyt`.* TO ‘p1′@’localhost’|

| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro_shop1` TO ‘p1′@’localhost’ |

+————————————————————————————-+

5. create temporary tables(注意这里是tables，不是table)

必须有create temporary tables的权限，才可以使用create temporary tables.

mysql>grant create temporary tables on pyt.* to ‘p1′@’localhost’

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql>create temporary table tt1(id int)

6. create view

必须有create view的权限，才可以使用create view

mysql>grant create view on pyt.* to ‘p1′@’localhost’

mysql>create view v_shop as select price from shop

7. create user

要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。

mysql>grant create user on *.* to ‘p1′@’localhost’

或：mysql>grant insert on *.* to p1@localhost

8. insert

必须有insert的权限，才可以使用insert into ….. values….

9. alter

必须有alter的权限，才可以使用alter table

alter table shop modify dealer char(15)

10. alter routine

必须具有alter routine的权限，才可以使用{alter |drop} {procedure|function}

mysql>grant alter routine on pyt.* to ‘p1′@’ localhost ‘

mysql>drop procedure pro_shop

Query OK, 0 rows affected (0.00 sec)

mysql>revoke alter routine on pyt.* from ‘p1′@’localhost’

[mysql@mydev ~]$ mysql -h localhost -u p1 -p pyt

mysql>drop procedure pro_shop

ERROR 1370 (42000): alter routine command denied to user ‘p1′@’localhost’ for routine ‘pyt.pro_shop’

11. update

必须有update的权限，才可以使用update table

mysql>update shop set price=3.5 where article=0001 and dealer=’A'

12. delete

必须有delete的权限，才可以使用delete from ….where….(删除表中的记录)

13. drop

必须有drop的权限，才可以使用drop database db_namedrop table tab_name

drop view vi_namedrop index in_name

14. show database

通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。

对于p1@localhost用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库：

mysql>show databases

+——————–+

| Database |

+——————–+

| information_schema|

| pyt |

| test |

+——————–+

15. show view

必须拥有show view权限，才能执行show create view。

mysql>grant show view on pyt.* to p1@localhost

mysql>show create view v_shop

16. index

必须拥有index权限，才能执行[create |drop] index

mysql>grant index on pyt.* to p1@localhost

mysql>create index ix_shop on shop(article)

mysql>drop index ix_shop on shop

17. excute

执行存在的Functions,Procedures

mysql>call pro_shop1(0001,@a)；

+———+

| article |

+———+

| 0001 |

| 0001 |

+———+

mysql>select @a

+——+

| @a |

+——+

| 2 |

+——+

18. lock tables

必须拥有lock tables权限，才可以使用lock tables

mysql>grant lock tables on pyt.* to p1@localhost

mysql>lock tables a1 read

mysql>unlock tables

19. references

有了REFERENCES权限，用户就可以将其它表的一个字段作为某一个表的外键约束。

20. reload

必须拥有reload权限，才可以执行flush [tables | logs | privileges]

mysql>grant reload on pyt.* to p1@localhost

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql>grant reload on *.* to ‘p1′@’localhost’

Query OK, 0 rows affected (0.00 sec)

mysql>flush tables

21. replication client

拥有此权限可以查询master server、slave server状态。

mysql>show master status

ERROR 1227 (42000): Access deniedyou need the SUPER,REPLICATION CLIENT privilege for this operation

mysql>grant Replication client on *.* to p1@localhost

或：mysql>grant super on *.* to p1@localhost

mysql>show master status

+——————+———-+————–+——————+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000006 | 2111 | | |

+——————+———-+————–+——————+

mysql>show slave status

22. replication slave

拥有此权限可以查看从服务器，从主服务器读取二进制日志。

mysql>show slave hosts

ERROR 1227 (42000): Access deniedyou need the REPLICATION SLAVE privilege for this operation

mysql>show binlog events

ERROR 1227 (42000): Access deniedyou need the REPLICATION SLAVE privilege for this operation

mysql>grant replication slave on *.* to p1@localhost

mysql>show slave hosts

Empty set (0.00 sec)

mysql>show binlog events

+—————+——-+—————-+———–+————-+————–+

| Log_name | Pos | Event_type | Server_id| End_log_pos|Info | +—————+——-+————–+———–+————-+—————+

| mysql-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 | |mysql-bin.000005|98|Query|1|197|use `mysql`create table a1(i int)engine=myisam|

……………………………………

23. Shutdown

关闭MySQL：

[mysql@mydev ~]$ mysqladmin shutdown

重新连接：

[mysql@mydev ~]$ mysql

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

[mysql@mydev ~]$ cd /u01/mysql/bin

[mysql@mydev bin]$ ./mysqld_safe &

[mysql@mydev bin]$ mysql

24. grant option

拥有grant option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）

mysql>grant Grant option on pyt.* to p1@localhost

mysql>grant select on pyt.* to p2@localhost

25. file

拥有file权限才可以执行 select ..into outfile和load data infile… *** 作，但是不要把file, process, super权限授予管理员以外的账号，这样存在严重的安全隐患。

mysql>grant file on *.* to p1@localhost

mysql>load data infile ‘/home/mysql/pet.txt’ into table pet

26. super

这个权限允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS。

mysql>grant super on *.* to p1@localhost

mysql>purge master logs before ‘mysql-bin.000006′

27. process

通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。

mysql>show processlist

+—-+——+———–+——+———+——+——-+——————+

| Id | User | Host | db | Command | Time | State | Info |

+—-+——+———–+——+———+——+——-+——————+

| 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |

+—-+——+———–+——+———+——+——-+——————+

另外，

管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟*.*

mysql>grant super on pyt.* to p1@localhost

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql>grant super on *.* to p1@localhost

Query OK, 0 rows affected (0.01 sec)

回答的有点多请耐心看完。

希望能帮助你还请及时采纳谢谢

1事务的原理

事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行。MySQL事务处理只支持InnoDB和BDB数据表类型。

1事务的ACID原则

** 1（Atomicity）原子性**： 事务是最小的执行单位，不允许分割。原子性确保动作要么全部完成，要么完全不起作用；

2（Consistency）一致性： 执行事务前后，数据保持一致；

3（Isolation）隔离性： 并发访问数据库时，一个事务不被其他事务所干扰。

4（Durability）持久性: 一个事务被提交之后。对数据库中数据的改变是持久的，即使数据库发生故障。

1缓冲池（Buffer Pool）

Buffer Pool中包含了磁盘中部分数据页的映射。当从数据库读取数据时，会先从Buffer Pool中读取数据，如果Buffer Pool中没有，则从磁盘读取后放入到Buffer Pool中。当向数据库写入数据时，会先写入到Buffer Pool中，Buffer Pool中更新的数据会定期刷新到磁盘中（此过程称为刷脏）。

2日志缓冲区（Log Buffer）

当在MySQL中对InnoDB表进行更改时，这些更改命令首先存储在InnoDB日志缓冲区（Log Buffer）的内存中，然后写入通常称为重做日志（redo logs）的InnoDB日志文件中。

3双写机制缓存（DoubleWrite Buffer）

Doublewrite Buffer是共享表空间的物理文件的 buffer,其大小是2MB.是一个一分为二的2MB空间。

刷脏 *** 作开始之时,先进行脏页**‘备份’** *** 作.将脏页数据写入 Doublewrite Buffer.

将Doublewrite Buffer(顺序IO)写入磁盘文件中(共享表空间) 进行刷脏 *** 作.

4回滚日志(Undo Log)

Undo Log记录的是逻辑日志.记录的是事务过程中每条数据的变化版本和情况.

在Innodb 磁盘架构中Undo Log 默认是共享表空间的物理文件的Buffer.

在事务异常中断,或者主动(Rollback)回滚的过程中 ,Innodb基于 Undo Log进行数据撤销回滚,保证数据回归至事务开始状态.

5重做日志(Redo Log)

Redo Log通常指的是物理日志，记录的是数据页的物理修改.并不记录行记录情况。（也就是只记录要做哪些修改，并不记录修改的完成情况） 当数据库宕机重启的时候，会将重做日志中的内容恢复到数据库中。

1原子性

Innodb事务的原子性保证,包含事务的提交机制和事务的回滚机制.在Innodb引擎中事务的回滚机制是依托 回滚日志(Undo Log) 进行回滚数据，保证数据回归至事务开始状态.

2那么不同的隔离级别，隔离性是如何实现的，为什么不同事物间能够互不干扰？ 答案是 锁 和 MVCC。

3持久性

基于事务的提交机制流程有可能出现三种场景.

1 数据刷脏正常.一切正常提交，Redo Log 循环记录.数据成功落盘.持久性得以保证

2数据刷脏的过程中出现的系统意外导致页断裂现象 (部分刷脏成功)，针对页断裂情况,采用Double write机制进行保证页断裂数据的恢复.

3数据未出现页断裂现象,也没有刷脏成功，MySQL通过Redo Log 进行数据的持久化即可

4一致性

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性

2事务的隔离级别

Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

脏读： 指一个事务读取了另外一个事务未提交的数据。

不可重复读： 在一个事务内读取表中的某一行数据，多次读取结果不同

虚读(幻读)： 是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

2基本语法

-- 使用set语句来改变自动提交模式

SET autocommit = 0 /*关闭*/

SET autocommit = 1 /*开启*/

-- 注意:

--- 1.MySQL中默认是自动提交

--- 2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点

START TRANSACTION

-- 提交一个事务给数据库

COMMIT

-- 将事务回滚,数据回到本次事务的初始状态

ROLLBACK

-- 还原MySQL数据库的自动提交

SET autocommit =1

-- 保存点

SAVEPOINT 保存点名称 -- 设置一个事务保存点

ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点

RELEASE SAVEPOINT 保存点名称 -- 删除保存点

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

/*

课堂测试题目

A在线买一款价格为500元商品,网上银行转账.

A的yhk余额为2000,然后给商家B支付500.

商家B一开始的yhk余额为10000

创建数据库shop和创建表account并插入2条数据

*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci

USE `shop`

CREATE TABLE `account` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(32) NOT NULL,

`cash` DECIMAL(9,2) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)

VALUES('A',2000.00),('B',10000.00)

-- 转账实现

SET autocommit = 0-- 关闭自动提交

START TRANSACTION -- 开始一个事务,标记事务的起始点

UPDATE account SET cash=cash-500 WHERE `name`='A'

UPDATE account SET cash=cash+500 WHERE `name`='B'

COMMIT-- 提交事务

# rollback

SET autocommit = 1-- 恢复自动提交

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

3事务实现方式-MVCC

1什么是MVCC

MVCC是mysql的的多版本并发控制即multi-Version Concurrency Controller，mysql的innodb引擎支持MVVC。MVCC是为了实现事务的隔离性，通过版本号，避免同一数据在不同事务间的竞争，你可以把它当成基于多版本号的一种乐观锁。当然，这种乐观锁只在事务级别为RR（可重复读）和RC（读提交）生效。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突，极大的增加了系统的并发性能。

2MVCC的实现机制

InnoDB在每行数据都增加两个隐藏字段，一个记录创建的版本号，一个记录删除的版本号。

在多版本并发控制中，为了保证数据 *** 作在多线程过程中，保证事务隔离的机制，降低锁竞争的压力，保证较高的并发量。在每开启一个事务时，会生成一个事务的版本号，被 *** 作的数据会生成一条新的数据行（临时），但是在提交前对其他事务是不可见的；对于数据的更新（包括增删改） *** 作成功，会将这个版本号更新到数据的行中；事务提交成功，新的版本号也就更新到了此数据行中。这样保证了每个事务 *** 作的数据，都是互不影响的，也不存在锁的问题。

3MVCC下的CRUD

SELECT：

当隔离级别是REPEATABLE READ时select *** 作，InnoDB每行数据来保证它符合两个条件：

** 1 事务的版本号 大于等于 创建行版本号**

　 ** 2 行数据的删除版本 未定义 或者大于 事务版本号**

　 【行创建版本号 事务版本号 行删除版本号】

　

INSERT：

InnoDB为这个新行 记录 当前的系统版本号。

DELETE：

InnoDB将当前的系统版本号 设置为 这一行的删除版本号。

UPDATE：

InnoDB会写一个这行数据的新拷贝，这个拷贝的版本为 当前的系统版本号。它同时也会将这个版本号 写到 旧行的删除版本里。

————————————————

版权声明：本文为CSDN博主「@Autowire」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/zs18753479279/article/details/113933252

---