目录
一、前言
随着业务量增大,数据库性能终究会遇到瓶颈,因此需要将部分业务进行分库处理,同时部分查询需要切换到性能更好的数据库,如阿里云的数仓,因此便会遇到多数据源场景,当然此时不同数据源之间的事务一致性就格外重要,本文就记录下SpringBoot+Mybatis实现多数据源事务一致的小案例
二、多数据源事务
这里准备记录两种实现方式,以便日后查阅
方案一
主要思路就是不同的数据源管理指定路径下的mapper文件,同时使用不同的事务管理类来进行事务区分,依赖spring的默认事务传播行为实现,即spring首先检查当前线程上下文是否存在一个活跃事务,如果存在则加入,反之则创建。
1、创建数据表
分别在不同的数据库中建表
create table user_test_0
(
id serial not null
constraint user_0_pk
primary key,
name varchar,
tenant_id varchar
);
2、数据源配置
first.datasource.url=jdbc:postgresql://
first.datasource.username=
first.datasource.password=
second.datasource.url=jdbc:postgresql://
second.datasource.username=
second.datasource.password=
3、配置映射
@Configuration
@ConfigurationProperties(prefix = FirstDataSourceProperties.PREFIX)
public class FirstDataSourceProperties extends BaseDataSourceProperties {
public static final String PREFIX = "first.datasource";
}
@Configuration
@ConfigurationProperties(prefix = SecondDataSourceProperties.PREFIX)
public class SecondDataSourceProperties extends BaseDataSourceProperties {
public static final String PREFIX = "second.datasource";
}
接下来针对不同的数据源需要创建不同的数据源(DataSource)、事务管理类(DataSourceTransactionManager)、Mybatis会话相关类(SqlSessionFactory、SqlSessionTemplate)
@Configuration
@MapperScan(basePackages = "com.database.subtable.dao", sqlSessionTemplateRef = "firstSessionTemplate")
public class FirstConfiguration extends BaseDataSourceConfiguration {
private static final Logger logger = LoggerFactory.getLogger(FirstConfiguration.class);
@Autowired
FirstDataSourceProperties properties;
@Bean
public ThreadPoolTaskExecutor threadPoolTaskExecutor() {
logger.info("initialize threadPoolTaskExecutor");
ThreadPoolTaskExecutor threadPoolTaskExecutor = new ThreadPoolTaskExecutor();
threadPoolTaskExecutor.setCorePoolSize(5);
threadPoolTaskExecutor.setMaxPoolSize(10);
threadPoolTaskExecutor.setWaitForTasksToCompleteOnShutdown(true);
return threadPoolTaskExecutor;
}
@Bean
public WallConfig getWallConfig() {
WallConfig wallConfig = new WallConfig();
wallConfig.setStrictSyntaxCheck(false);
wallConfig.setDir("META-INF/druid/wall/postgres");
wallConfig.init();
return wallConfig;
}
@Bean
public WallFilter getWallFilter() {
WallFilter wallFilter = new WallFilter();
wallFilter.setDbType(properties.getDbType());
wallFilter.setConfig(getWallConfig());
return wallFilter;
}
@Bean(name = "firstDataSource")
public DataSource getDataSource() throws SQLException {
DataSource dataSource = buildDataSource(properties);
logger.info("firstDataSource init");
return dataSource;
}
@Bean(name = "firstDataSourceTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("firstDataSource") DataSource dataSource) throws SQLException {
return new DataSourceTransactionManager(dataSource);
}
@Bean("firstSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("firstDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:com/database/subtable/dao/*.xml"));
// sessionFactory.setTypeHandlersPackage("");
return sessionFactory.getObject();
}
@Bean(name = "firstSessionTemplate")
public SqlSessionTemplate sessionTemplate(
@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
@Configuration
@MapperScan(basePackages = "com.database.subtable.mybatis.second.mapper", sqlSessionTemplateRef = "secondSessionTemplate")
public class SecondConfiguration extends BaseDataSourceConfiguration {
private static final Logger logger = LoggerFactory.getLogger(SecondConfiguration.class);
@Autowired
SecondDataSourceProperties properties;
@Bean(name = "secondDataSource")
@Primary
public DataSource getDataSource() throws SQLException {
DataSource dataSource = buildDataSource(properties);
logger.info("secondDataSource init,url");
return dataSource;
}
@Bean(name = "secondDataSourceTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("secondDataSource") DataSource dataSource) throws SQLException {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "secondSqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:com/database/subtable/mybatis/second/mapper/*.xml"));
// sessionFactory.setPlugins(new Interceptor[]{new ShardTableInterceptor()});
return sessionFactory.getObject();
}
@Bean(name = "secondSessionTemplate")
public SqlSessionTemplate sessionTemplate(
@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
4、使用@Transactional注解
接下来在需要使用事务的类或方法标注@Transactional注解即可,不同的数据源记得分开
@Service
public class MultiDataSourcesImpl implements MultiDataSources {
@Resource UserMapper firstUserMapper;
@Resource SecondUserMapper secondUserMapper;
@Autowired private MultiDataSources multiDataSources;
@Override
@Transactional(transactionManager = "firstDataSourceTransactionManager")
public void test() {
User user = new User("张三", "腾讯");
firstUserMapper.insertUser(user);
multiDataSources.insertSecond();
}
@Override
@Transactional(transactionManager = "secondDataSourceTransactionManager")
public void insertSecond() {
User user = new User("李四", "百度");
secondUserMapper.insertUser(user);
int i = 1/0;
}
}
新建个测试类便会发现当 insertSecond() 方法抛出异常时,两个数据库会同时回滚,有兴趣可从 spring 的事务拦截器开始慢慢打断点 (TransactionInterceptor)
方案二
由于Mybatis获取connection是交由spring管理器到DataSource中获取连接,而spring中有个具有路由功能的DataSource,它可以通过查找键调用不同的数据源,即 AbstractRoutingDataSource。因此主要思路就是通过重写该类的 determineCurrentLookupKey 方法,利用 ThreadLocal 存储当前线程的数据源类型,根据业务动态返回数据源名称。
1、首先配置多数据源
首先就是把多数据源配置加载到进来,然后交给spring管理
@Configuration
@MapperScan("com.database.subtable")
public class MybatisConfiguration extends BaseDataSourceConfiguration {
public static final String FIRST = "first";
public static final String SECOND = "second";
private static final Logger logger = LoggerFactory.getLogger(MybatisConfiguration.class);
@Autowired private FirstDataSourceProperties firstDataSourceProperties;
@Autowired(required = false) private SecondDataSourceProperties secondDataSourceProperties;
@Bean
public ThreadPoolTaskExecutor threadPoolTaskExecutor() {
logger.info("initialize threadPoolTaskExecutor");
ThreadPoolTaskExecutor threadPoolTaskExecutor = new ThreadPoolTaskExecutor();
threadPoolTaskExecutor.setCorePoolSize(5);
threadPoolTaskExecutor.setMaxPoolSize(10);
threadPoolTaskExecutor.setWaitForTasksToCompleteOnShutdown(true);
return threadPoolTaskExecutor;
}
@Bean
public WallConfig getWallConfig() {
WallConfig wallConfig = new WallConfig();
wallConfig.setStrictSyntaxCheck(false);
wallConfig.setDir("META-INF/druid/wall/postgres");
wallConfig.init();
return wallConfig;
}
@Bean
public WallFilter getWallFilter() {
WallFilter wallFilter = new WallFilter();
wallFilter.setDbType(firstDataSourceProperties.getDbType());
wallFilter.setConfig(getWallConfig());
return wallFilter;
}
@Primary
@Bean(name = "firstDataSource")
public DataSource firstDataSource() throws SQLException {
DataSource dataSource = buildDataSource(firstDataSourceProperties);
logger.info("firstDataSource init");
return dataSource;
}
/**
* 条件注解生效时才注入当前数据源
* @return
* @throws SQLException
*/
@ConditionalOnBean(SecondDataSourceProperties.class)
@Bean(name = "secondDataSource")
public DataSource secondDataSource() throws SQLException {
DataSource dataSource = buildDataSource(secondDataSourceProperties);
logger.info("secondDataSource init,url");
return dataSource;
}
@Bean("dynamicDataSource")
public DataSource dynamicDataSource() throws SQLException{
DynamicDataSource dynamicDataSource = new DynamicDataSource();
Map<Object, Object> dataSourceMap = new HashMap<>(2);
dataSourceMap.put(FIRST, firstDataSource());
if (secondDataSourceProperties != null) {
dataSourceMap.put(SECOND, secondDataSource());
} else {
logger.info("this env don't support second datasource.");
}
// 将 first 数据源作为默认指定的数据源
dynamicDataSource.setDefaultDataSource(firstDataSource());
// 将 first 和 second 数据源作为指定的数据源
dynamicDataSource.setDataSources(dataSourceMap);
return dynamicDataSource;
}
@Bean(name = "jdbcTransactionManager")
public DataSourceTransactionManager jdbcTransactionManager() throws SQLException {
return new DataSourceTransactionManager(dynamicDataSource());
}
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dynamicDataSource());
sessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:com/database/subtable/*/*.xml"));
// sessionFactory.setPlugins(new Interceptor[]{new ShardTableInterceptor()});
return sessionFactory.getObject();
}
}
备用数据源可以设置开关,并不是每个环境都需要
@Configuration
@ConfigurationProperties(prefix = SecondDataSourceProperties.PREFIX)
@ConditionalOnProperty(prefix = SecondDataSourceProperties.PREFIX, name = "enabled", havingValue = "true") // 动态数据源时使用
public class SecondDataSourceProperties extends BaseDataSourceProperties {
public static final String PREFIX = "second.datasource";
}
2、重写 determineCurrentLookupKey 方法
其核心就是在初始化数据源时设置多个数据源到map集合中,然后根据key可以获取不同的数据源:
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSource.class);
@Override
protected DataSource determineTargetDataSource() {
return super.determineTargetDataSource();
}
@Override
protected Object determineCurrentLookupKey() {
String dataSourceKey = DynamicDataSourceContextHolder.getDataSourceKey();
if (StringUtils.equals(dataSourceKey, MybatisConfiguration.SECOND)) {
logger.info("get data from second");
}
return dataSourceKey;
}
public void setDefaultDataSource(Object defaultDataSource) {
super.setDefaultTargetDataSource(defaultDataSource);
}
public void setDataSources(Map<Object, Object> dataSources) {
super.setTargetDataSources(dataSources);
DynamicDataSourceContextHolder.addDataSourceKeys(dataSources.keySet());
}
}
3、保存当前线程的数据源类型
public class DynamicDataSourceContextHolder {
private static final ThreadLocal<String> contextHolder = ThreadLocal.withInitial(() -> MybatisConfiguration.FIRST);
public static List<Object> dataSourceKeys = new ArrayList<>();
public static void setDataSourceKey(String key) {
contextHolder.set(key);
}
public static String getDataSourceKey() {
return contextHolder.get();
}
public static void clearDataSourceKey() {
contextHolder.remove();
}
public static boolean containDataSourceKey(String key) {
return dataSourceKeys.contains(key);
}
public static boolean addDataSourceKeys(Collection<? extends Object> keys) {
return dataSourceKeys.addAll(keys);
}
}
4、转换数据源
都配置完之后,然后在业务中可以通过 注解+切面 动态设置数据源
public class DynamicDataSourceAspect {
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);
/**
* 切换数据源
* @param point
* @param dataSource
*/
@Before("@annotation(dataSource))")
public void switchDataSource(JoinPoint point, DataSource dataSource) {
try {
if (!DynamicDataSourceContextHolder.containDataSourceKey(dataSource.value())) {
logger.warn("DataSource [{}] doesn't exist, so auto use default DataSource", dataSource.value());
return;
}
DynamicDataSourceContextHolder.setDataSourceKey(dataSource.value());
logger.info("Switch DataSource to [{}] in Method [{}]", DynamicDataSourceContextHolder.getDataSourceKey(),
point.getSignature());
} catch (Exception e) {
logger.warn("switchDataSource error,e :", e);
}
}
/**
* 重置数据源
* @param point
* @param dataSource
*/
@After("@annotation(dataSource))")
public void restoreDataSource(JoinPoint point, DataSource dataSource) {
DynamicDataSourceContextHolder.clearDataSourceKey();
logger.info("Restore DataSource to [{}] in Method [{}]", DynamicDataSourceContextHolder.getDataSourceKey(),
point.getSignature());
}
}
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Transactional(propagation = Propagation.REQUIRES_NEW)
public @interface DataSource {
/**
* 数据源key值
* @return
*/
String value();
}
这种情况下事务可能不太行.......