前言:
在最近的实际工作当中,遇到一个业务场景,需要保证新老数据库的同步,在动态切换数据库当中,遇到了@Transactional和@DS的冲突问题,这里我写了一份个人的总结,从业务还原、原因剖析和如何解决等等一步步阐明。
目录
(二)@DSTransactional注解代替@Transactional
(五)、临时添加一个temp数据库,进行插入操作,并抛出异常
(七)、使用@DSTransactional注解,在slave和temp之间抛出异常
(八)、使用@DSTransactional注解,在最后抛出异常
一、场景模拟
1、先导入pom.xml依赖
-
<dependencies>
-
<dependency>
-
<groupId>org.springframework.boot
</groupId>
-
<artifactId>spring-boot-starter-jdbc
</artifactId>
-
</dependency>
-
<dependency>
-
<groupId>org.springframework.boot
</groupId>
-
<artifactId>spring-boot-starter-web
</artifactId>
-
</dependency>
-
-
<dependency>
-
<groupId>org.springframework.boot
</groupId>
-
<artifactId>spring-boot-starter-test
</artifactId>
-
<scope>test
</scope>
-
</dependency>
-
-
<!--数据源切换依赖-->
-
<dependency>
-
<groupId>com.baomidou
</groupId>
-
<artifactId>dynamic-datasource-spring-boot-starter
</artifactId>
-
<version>3.5.2
</version>
-
</dependency>
-
-
<!--MySQL驱动依赖-->
-
<dependency>
-
<groupId>mysql
</groupId>
-
<artifactId>mysql-connector-java
</artifactId>
-
<scope>runtime
</scope>
-
</dependency>
-
-
<!--Mybatis依赖-->
-
<dependency>
-
<groupId>org.mybatis.spring.boot
</groupId>
-
<artifactId>mybatis-spring-boot-starter
</artifactId>
-
<version>2.2.2
</version>
-
</dependency>
-
</dependencies>
2、yml文件配置了3个数据源,主数据源是master,从数据源是slave,后续临时加了个数据源temp,为了用于事务的测试,数据库均为MySQL。
server: port: 8080 spring: datasource: dynamic: primary: master datasource: master: username: root password: root url: jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC driver-class-name: com.mysql.cj.jdbc.Driver slave: username: root password: root url: jdbc:mysql://localhost:3306/slave?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC driver-class-name: com.mysql.cj.jdbc.Driver temp: username: root password: root url: jdbc:mysql://localhost:3306/temp?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC driver-class-name: com.mysql.cj.jdbc.Driver mybatis: mapper-locations: classpath:mapping/*.xml
3、分别编写主数据源和从数据源的Mapper层接口
-
@Mapper
-
@DS("master")
-
public
interface
MasterMapper {
-
int
insertUser
(User user);
-
}
-
@Mapper
-
@DS("slave")
-
public
interface
SlaveMapper {
-
int
insertRole
(Role role);
-
}
4、分别编写对应的XML文件
-
<?xml version="1.0" encoding="UTF-8" ?>
-
<!DOCTYPE mapper
-
PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN"
-
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
-
<mapper namespace="com.example.demo.mapper.MasterMapper">
-
<insert id="insertUser" parameterType="com.example.demo.bean.User">
-
INSERT INTO user (username, password)
-
VALUES(#{username}, #{password})
-
</insert>
-
</mapper>
-
<?xml version="1.0" encoding="UTF-8" ?>
-
<!DOCTYPE mapper
-
PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN"
-
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
-
<mapper namespace="com.example.demo.mapper.SlaveMapper">
-
<insert id="insertRole" parameterType="com.example.demo.bean.Role">
-
INSERT INTO role (role)
-
VALUES(#{role})
-
</insert>
-
</mapper>
5、编写Service方法
-
@Service
-
public
class
UserService {
-
-
@Resource
-
private MasterMapper masterMapper;
-
-
@Resource
-
private SlaveMapper slaveMapper;
-
-
@Transactional
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
masterMapper.insertUser(user);
-
Role
role
=
new
Role();
-
role.setRole(
"管理员");
-
slaveMapper.insertRole(role);
-
}
-
}
6、测试运行,报错如下
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: Table 'master.role' doesn't exist
### The error may exist in file [D:\JavaProjects\Java\demo\target\classes\mapping\SlaveMapper.xml]
### The error may involve com.example.demo.mapper.SlaveMapper.insertRole-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO role (role) VALUES(?)
### Cause: java.sql.SQLSyntaxErrorException: Table 'master.role' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'master.role' doesn't exist
从报错的表面上来看,是因为在主数据库当中不存在role这个表,可是我们已经切换了数据源呀,可为什么还是报错呢,具体的详细原因,我们往下细说。
二、原因分析
@Transactional开启事务的时候,会先从数据库连接池获取是数据库的连接(基于Spring的AOP切面),我们UserService方法上面没有打上@DS注解,所以Spring默认采用的是主数据源,而且在这之后,这个事务会通过ThreadLocal跟当前线程绑定并也报错了connection连接,通俗的来讲,在进入UserService方法的时候,当前事务以及绑定了数据源Master,在运行到SlaveMapper接口时,因为当前事务的connection连接已经存在,所以拿到的数据源还是默认的Master,于是想找到Slave当中的role表,当然是不可能的,所以只能报错了。
三、解决方案
(一)更改事务的传播机制
其实我们只要更改一下事务的传播机制,将它设置为:Propagation.REQUIRES_NEW即可,意思就是将原有的Spring事务挂起,并创建一个新的事务并分配的一个新的connection,两者不影响,具体操作如下:
1、修改原有的UserService代码
不用通过@DS指定数据源,因为默认是Master;将slave业务操作分离出来,封装到一个Service服务类当中,再通过@Resource注解注入进来,最后还是指定一下回滚策略,遇到异常就回滚。
-
@Service
-
public
class
UserService {
-
-
@Resource
-
private MasterMapper masterMapper;
-
-
@Resource
-
private SlaveService slaveService;
-
-
@Transactional(rollbackFor = Exception.class)
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
masterMapper.insertUser(user);
-
slaveService.slave();
-
}
-
-
}
2、 编写SlaveService服务代码
必须通过@DS指定一下数据源为slave,在slave方法上面重新修改一下事务的传播机制即可
-
@Service
-
@DS("slave")
-
public
class
SlaveService {
-
@Resource
-
private SlaveMapper slaveMapper;
-
-
@Transactional(propagation = Propagation.REQUIRES_NEW,rollbackFor = Exception.class)
-
public
void
slave
(){
-
Role
role
=
new
Role();
-
role.setRole(
"管理员");
-
slaveMapper.insertRole(role);
-
}
-
}
3、其他的保持不变,最后我们再测试一下,看一下输出结果,成功了!
(二)@DSTransactional注解代替@Transactional
我们可以使用@DSTransactional注解代替@Transactional即可,其他什么都不用动,也是最简单的方法。
1、导入pom.xml依赖
-
<dependency>
-
<groupId>com.baomidou
</groupId>
-
<artifactId>dynamic-datasource-spring-boot-starter
</artifactId>
-
<version>3.5.0
</version>
-
</dependency>
2、修改UserService代码
-
@Service
-
public
class
UserService {
-
-
@Resource
-
private MasterMapper masterMapper;
-
-
@Resource
-
private SlaveMapper slaveMapper;
-
-
@DSTransactional
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
masterMapper.insertUser(user);
-
Role
role
=
new
Role();
-
role.setRole(
"管理员");
-
slaveMapper.insertRole(role);
-
}
-
}
3、运行测试,查看输出结果,成功!
四、事务回滚机制
(一)、在Master和Slave事务执行前抛出异常
UserService类:
-
@Transactional(rollbackFor = Exception.class)
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
int
a
=
1/
0;
-
masterMapper.insertUser(user);
-
slaveService.slave();
-
}
结果:数据保持一致
(二)、当master事务和slave事务中间抛出异常
UserService类:
-
@Transactional(rollbackFor = Exception.class)
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
masterMapper.insertUser(user);
-
int
a
=
1/
0;
-
slaveService.slave();
-
}
结果:回滚master事务,slave事务无影响
(三)、在slave方法中抛出异常
SlaveService类:
-
@Transactional(propagation = Propagation.REQUIRES_NEW,rollbackFor = Exception.class)
-
public
void
slave
(){
-
Role
role
=
new
Role();
-
role.setRole(
"管理员");
-
slaveMapper.insertRole(role);
-
int
a
=
1/
0;
-
}
结果:master和slave事务都会进行回滚
(四)、在master和slave事务之后
UserService类:
-
@Transactional(rollbackFor = Exception.class)
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
masterMapper.insertUser(user);
-
slaveService.slave();
-
int
a
=
1/
0;
-
}
结果:master事务回滚,slave已经提交事务,入库
(五)、临时添加一个temp数据库,进行插入操作,并抛出异常
UserService类:
-
@Transactional(rollbackFor = Exception.class)
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
masterMapper.insertUser(user);
-
slaveService.slave();
-
tempService.temp();
-
}
TempService类:
-
@Transactional(propagation = Propagation.REQUIRES_NEW,rollbackFor = Exception.class)
-
public
void
temp
(){
-
Car
car
=
new
Car();
-
car.setCar(
"AE86");
-
tempMapper.insertCar(car);
-
int
a
=
1/
0;
-
}
结果:master回滚,slave事务提交,temp回滚
(六)、嵌套
UserService类:
-
@Transactional(rollbackFor = Exception.class)
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
masterMapper.insertUser(user);
-
slaveService.slave();
-
}
SlaveService类:
-
@Transactional(propagation = Propagation.REQUIRES_NEW,rollbackFor = Exception.class)
-
public
void
slave
(){
-
Role
role
=
new
Role();
-
role.setRole(
"管理员");
-
slaveMapper.insertRole(role);
-
tempService.temp();
-
}
TempService类:
-
@Transactional(propagation = Propagation.REQUIRES_NEW,rollbackFor = Exception.class)
-
public
void
temp
(){
-
Car
car
=
new
Car();
-
car.setCar(
"AE86");
-
tempMapper.insertCar(car);
-
int
a
=
1/
0;
-
}
结果:master回滚,slave回滚,temp回滚
(七)、使用@DSTransactional注解,在slave和temp之间抛出异常
UserService类
-
@DSTransactional
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
masterMapper.insertUser(user);
-
Role
role
=
new
Role();
-
role.setRole(
"管理员");
-
slaveMapper.insertRole(role);
-
int a=
1/
0;
-
Car
car
=
new
Car();
-
car.setCar(
"AE86");
-
tempMapper.insertCar(car);
-
}
结果:master回滚,slave回滚、temp回滚
(八)、使用@DSTransactional注解,在最后抛出异常
-
@DSTransactional
-
public
void
Add
(){
-
User
user
=
new
User();
-
user.setUsername(
"张三");
-
user.setPassword(
"123456");
-
masterMapper.insertUser(user);
-
Role
role
=
new
Role();
-
role.setRole(
"管理员");
-
slaveMapper.insertRole(role);
-
Car
car
=
new
Car();
-
car.setCar(
"AE86");
-
tempMapper.insertCar(car);
-
int a=
1/
0;
-
}
结果:同上
五、gitee源码地址
https://gitee.com/huang-tuantuan/transactional
六、总结
以上就是我目前对于这种问题的分析与解决方案,参考了网上各种各样的解决方案,写了一份总结文档。
版权声明:本文为CSDN博主「黄团团」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接
通过此篇文章了解到,普通的数据源切换中,事务失效的原因;
多数据源中,可在事务中开启新事物,但是由于事务的传播性,导致外部事物无法影响内部事务,但是内部事务会直接影响外部事务;
可以依然怒多数据源的事务来解决类似问题,既: @DSTransactional,这样就达成了既定的目标,事务一致性;与单数据源事务,基本没有区别;