由于使用了微服务,会有多个数据库的情况,有时业务需要,需要切换数据源,所以使用了Mybatis plus的@DS来切换多数据源
yml数据库配置如下:
spring:
datasource:
dynamic:
primary: master
datasource:
master:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://user
username: root
password: root
common:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://book
username: root
password: root
service如下,默认是master数据源
@Service
@Slf4j
public class MasterService {
@Autowired
UserService userService;
@Autowired
BookService bookService;
/**必须master库方法先执行,才能回滚,达到事务效果*/
@Transactional(rollbackFor = Exception.class)
public void upload(ReqDto reqDto){
userService.save(reqDto);
bookService.save(reqDto);
}
}
userService
@Service
@Log4j2
public class UserService extends ServiceImpl<UserMapper, User> {
@Resource
private UserMapper userMapper;
public void save(ReqDto reqDto) {
userMapper.save(reqDto);
}
}
bookService
@Service
@Log4j2
@DS("common")
public class BookService extends ServiceImpl<BookMapper, Book> {
@Resource
private BookMapper bookMapper;
public void save(ReqDto reqDto) {
bookMapper.save(reqDto);
}
}
但是神奇的事发生的,bookService的数据库应该是common,但是却是master的,也就是说@DS切换数据源没有起作用
于是开始排查
- 去除MasterService.upload上面的@Transactional,数据源切换正常,但是事务无效
- BookService的save上面加@Transactional,数据源没有切换
- BookService的save上面加@Transactional(propagation = Propagation.REQUIRES_NEW),数据源切换,且事务有效
原因:
- 开启事务的同时,会从数据库连接池获取数据库连接;
- 如果内层的service使用@DS切换数据源,只是又做了一层拦截,但是并没有改变整个事务的连接;
- 在这个事务内的所有数据库操作,都是在事务连接建立之后,所以会产生数据源没有切换的问题;
- 为了使@DS起作用,必须替换数据库连接,也就是改变事务的传播机智,产生新的事务,获取新的数据库连接;
- 所以bookService的save方法上除了加@Transactional外,还需要设置propagation = Propagation.REQUIRES_NEW
使得代码走以下逻辑:
private TransactionStatus handleExistingTransaction(
.....省略.....
if (definition.getPropagationBehavior() == TransactionDefinition.PROPAGATION_REQUIRES_NEW) {
if (debugEnabled) {
logger.debug("Suspending current transaction, creating new transaction with name [" +
definition.getName() + "]");
}
SuspendedResourcesHolder suspendedResources = suspend(transaction);
try {
return startTransaction(definition, transaction, debugEnabled, suspendedResources);
}
catch (RuntimeException | Error beginEx) {
resumeAfterBeginException(transaction, suspendedResources, beginEx);
throw beginEx;
}
}
.....省略.....)
在走startTransaction,再走doBegin,重新创建新事务,获取新的数据库连接,从而得到@DS的数据源
startTransaction(definition, transaction, debugEnabled, suspendedResources);
protected void doBegin(Object transaction, TransactionDefinition definition) {
DataSourceTransactionObject txObject = (DataSourceTransactionObject) transaction;
Connection con = null;
try {
if (!txObject.hasConnectionHolder() ||
txObject.getConnectionHolder().isSynchronizedWithTransaction()) {
Connection newCon = obtainDataSource().getConnection();//获取数据库连接
if (logger.isDebugEnabled()) {
logger.debug("Acquired Connection [" + newCon + "] for JDBC transaction");
}
txObject.setConnectionHolder(new ConnectionHolder(newCon), true);
}
txObject.getConnectionHolder().setSynchronizedWithTransaction(true);
con = txObject.getConnectionHolder().getConnection();
Integer previousIsolationLevel = DataSourceUtils.prepareConnectionForTransaction(con, definition);
txObject.setPreviousIsolationLevel(previousIsolationLevel);
txObject.setReadOnly(definition.isReadOnly());
// Switch to manual commit if necessary. This is very expensive in some JDBC drivers,
// so we don't want to do it unnecessarily (for example if we've explicitly
// configured the connection pool to set it already).
if (con.getAutoCommit()) {
txObject.setMustRestoreAutoCommit(true);
if (logger.isDebugEnabled()) {
logger.debug("Switching JDBC Connection [" + con + "] to manual commit");
}
con.setAutoCommit(false);
}
.....省略.....)
最终代码如下,只需要修改的是bookService
bookService
@Service
@Log4j2
@DS("common")
public class BookService extends ServiceImpl<BookMapper, Book> {
@Resource
private BookMapper bookMapper;
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void save(ReqDto reqDto) {
bookMapper.save(reqDto);
}
}
- @DS数据源切换生效
- @Transaction事务生效
需要注意:
master:userService
common:bookService
common数据库的操作,需要在master之后,这样当bookService.save失败,会使得userService回滚;
如果common的操作先,那当userService失败,无法使bookService回滚
会回滚
@Transactional(rollbackFor = Exception.class)
public void upload(ReqDto respDto){
userService.save(respDto);
bookService.save(respDto);
}
不会回滚
@Transactional(rollbackFor = Exception.class)
public void upload(ReqDto respDto){
bookService.save(respDto);
userService.save(respDto);
}