整合背景
公司用的是springboot项目,使用的是druid数据源。
原生spring的事务是不支持多数据源场景的,而后台大部分业务逻辑都会同时操作多个数据源,因此我们在业务逻辑里都是通过手动的方式控制事务——比如insert插入数据抛出异常了,那么在catch中再delete。
这种做法虽然满足了“事务回滚”需求,但是需要编写额外的代码,业务侵入性太强;同时,粗心时难免会漏写,导致实际业务没有回滚。
所以,我们需要一个能支持多数据源场景下的事务回滚框架,做到类似spring事务那样对业务无侵入。
原代码和配置
我们先看看,目前项目中数据源是如何配置的。
咱们以项目中的falconportal和fstack数据源为例,其他数据源整合方式与之类似。
1.properties配置
falconportal数据源,敏感信息用“XXX”代替了
spring.datasource.falconportal.url=jdbc:mysql://XXX:3306/falcon_portal?characterEncoding=utf-8&autoReconnect=true&serverTimezone=Asia/Shanghai
spring.datasource.falconportal.username=root
spring.datasource.falconportal.password=XQmelAwOSqMJdWJaHs5JFslhSNcUp7HCifVkLDoRg29OAIY/7X/vwlh/Y1Tlv15lnWk2m9FtmKKhqdhgFcZ5Ng==
spring.datasource.falconportal.connectionProperties=config.decrypt=true;config.decrypt.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAKG/z0UXRf0qEvtpfMcRCjMXHMDoxrV/Vjfn98YRHf25lo18cOWGSwGqkRxbWoVymW1iNHrt9q2gScoAFfT5WBECAwEAAQ==
spring.datasource.falconportal.driverClassName=com.mysql.jdbc.Driver
spring.datasource.falconportal.maxActive=40
spring.datasource.falconportal.maxWait=20000
spring.datasource.falconportal.minIdle=5
spring.datasource.falconportal.initialSize=5
spring.datasource.falconportal.validationQuery=select 1
spring.datasource.falconportal.validationQueryTimeout=30000
spring.datasource.falconportal.testOnBorrow=true
spring.datasource.falconportal.testOnReturn=false
spring.datasource.falconportal.testWhileIdle=false
spring.datasource.falconportal.timeBetweenEvictionRunsMillis=60000
spring.datasource.falconportal.minEvictableIdleTimeMillis=300000
spring.datasource.falconportal.filters=stat,wall,slf4j,config
fstack数据源
spring.datasource.fstack.url=jdbc:mysql://XXX:3306/fsb?&verifyServerCertificate=false&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.fstack.username=fsb
#spring.datasource.fstack.password=8gr8feb4Jq
spring.datasource.fstack.password=XVpnDFssoFJmqIQA+PTal+uUTrPUGIMnyywXMOWZdKwMTWsYxXR8G8PuV+nyGWK/GCdSYw7tuukhPIkGbGbPhg==
spring.datasource.fstack.connectionProperties=config.decrypt=true;config.decrypt.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAK/71anplNUpR+HdO+qkIAAZx6QHuOhon3MKxMABvkHqAz4Zr+9CaW2izvjzi1ccd2xHMivN29JA2FOfJ4MRSB0CAwEAAQ==
spring.datasource.fstack.driverClassName=com.mysql.jdbc.Driver
spring.datasource.fstack.maxActive=20
spring.datasource.fstack.maxWait=20000
spring.datasource.fstack.minIdle=5
spring.datasource.fstack.initialSize=5
spring.datasource.fstack.validationQuery=select 1
spring.datasource.fstack.validationQueryTimeout=30000
spring.datasource.fstack.testOnBorrow=true
spring.datasource.fstack.testOnReturn=false
spring.datasource.fstack.testWhileIdle=false
spring.datasource.fstack.timeBetweenEvictionRunsMillis=60000
spring.datasource.fstack.minEvictableIdleTimeMillis=300000
spring.datasource.fstack.filters=stat,wall,slf4j,config
2.代码配置
falconportal数据源
@Configuration
@Slf4j
@MapperScan(basePackages = "com.sf.fsbmapper", sqlSessionFactoryRef = "fsbSqlSessionFactory")
public class FstackDataSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.fstack")
public DruidDataSource fsbDatasource() {
DruidDataSource dataSource = DruidDataSourceBuilder.create().build() ;
return dataSource ;
}
@Bean(name = "fsbSqlSessionFactory")
public SqlSessionFactory fsbSqlSessionFactory()
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(fsbDatasource());
log.info("fsb xadatasouce init....................................................................");
return sessionFactory.getObject();
}
}
fstack数据源
@Configuration
@Slf4j
@MapperScan(basePackages = "com.sf.falconPortalMapper", sqlSessionFactoryRef = "falconPortalSqlSessionFactory")
public class FalconPortalSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.falconportal")
public DruidDataSource falconportalDatasource() {
DruidDataSource dataSource = DruidDataSourceBuilder.create().build() ;
return dataSource ;
}
@Bean(name = "falconPortalSqlSessionFactory")
public SqlSessionFactory falconPortalSqlSessionFactory()
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(falconportalDatasource());
log.info("falconportal datasouce init....................................................................");
return sessionFactory.getObject();
}
}
其他数据源的配置方式,与上面无异。
优化方案
由于spring不支持多数据源下的事务,所以咱们考虑使用atomikos框架——atomikos框架的出现,就是为了解决上述问题存在的。
因为之前咱们用的是durid,所以现在咱们也用atomikos中的DruidXADataSource类。
1.引入依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>
2.properties配置
与之前一致
3.代码配置
考虑到多个数据源的配置属性名都是一致的,所以使用BaseProperties类封装相同的属性,然后让每个子类继承它。
BaseProperties
@Data
public class BaseProperties {
private String url;
private String username;
private String password;
private String connectionProperties;
private String driverClassName;
private int maxActive;
private int maxWait;
private int minIdle;
private int initialSize;
private String validationQuery;
private int validationQueryTimeout;
private Boolean testOnBorrow;
private Boolean testOnReturn;
private Boolean testWhileIdle;
private int timeBetweenEvictionRunsMillis;
private int minEvictableIdleTimeMillis;
private String filters;
}
falconpoart数据源
@Component
@ConfigurationProperties(prefix = "spring.datasource.falconportal")
@Data
public class FalconPortalProperties extends BaseProperties{
}
@Slf4j
@Configuration
@MapperScan(basePackages = "com.sf.falconPortalMapper", sqlSessionFactoryRef = "falconPortalSqlSessionFactory")
public class FalconPortalDataSourceConfig {
@Autowired
private FalconPortalProperties falconPortalProperties;
@Bean(name = "falconportalDatasource")
public DataSource falconportalDatasource() {
DruidXADataSource druidXADataSource = new DruidXADataSource();
druidXADataSource.setUrl(falconPortalProperties.getUrl());
druidXADataSource.setPassword(falconPortalProperties.getPassword());
druidXADataSource.setUsername(falconPortalProperties.getUsername());
druidXADataSource.setConnectionProperties(falconPortalProperties.getConnectionProperties());
druidXADataSource.setDriverClassName(falconPortalProperties.getDriverClassName());
druidXADataSource.setMaxActive(falconPortalProperties.getMaxActive());
druidXADataSource.setMaxWait(falconPortalProperties.getMaxWait());
druidXADataSource.setMinIdle(falconPortalProperties.getMinIdle());
druidXADataSource.setInitialSize(falconPortalProperties.getInitialSize());
druidXADataSource.setValidationQuery(falconPortalProperties.getValidationQuery());
druidXADataSource.setValidationQueryTimeout(falconPortalProperties.getValidationQueryTimeout());
druidXADataSource.setTestOnBorrow(falconPortalProperties.getTestOnBorrow());
druidXADataSource.setTestOnReturn(falconPortalProperties.getTestOnReturn());
druidXADataSource.setTestWhileIdle(falconPortalProperties.getTestWhileIdle());
druidXADataSource.setTimeBetweenEvictionRunsMillis(falconPortalProperties.getTimeBetweenEvictionRunsMillis());
druidXADataSource.setMinEvictableIdleTimeMillis(falconPortalProperties.getMinEvictableIdleTimeMillis());
druidXADataSource.setFilters(falconPortalProperties.getFilters);
AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
xaDataSource.setXaDataSource(druidXADataSource);
xaDataSource.setMaxPoolSize(falconPortalProperties.getMaxActive());
xaDataSource.setMinPoolSize(falconPortalProperties.getMinIdle());
//设置连接的最大存活时间
xaDataSource.setMaxLifeTime(falconPortalProperties.getMaxWait());
xaDataSource.setUniqueResourceName("falconportalDatasource");
return xaDataSource;
}
@Bean(name = "falconPortalSqlSessionFactory")
public SqlSessionFactory falconPortalSqlSessionFactory()
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(falconportalDatasource());
log.info("falconportal datasouce init....................................................................");
return sessionFactory.getObject();
}
}
fstack数据源
@Component
@ConfigurationProperties(prefix = "spring.datasource.fstack")
@Data
public class FstackProperties extends BaseProperties{
}
@Configuration
@Slf4j
@MapperScan(basePackages = "com.sf.fsbmapper", sqlSessionFactoryRef = "fsbSqlSessionFactory")
public class FstackDataSourceConfig {
@Autowired
private FstackDataProperties fstackDataProperties;
@Bean(name = "fsbDatasource")
public DataSource fsbDatasource() throws SQLException {
MysqlXADataSource mysqlXADataSource = new MysqlXADataSource();
mysqlXADataSource.setUrl(fstackDataProperties.getUrl());
mysqlXADataSource.setPinGlobalTxToPhysicalConnection(true);
mysqlXADataSource.setUser(fstackDataProperties.getUsername());
PasswordDecryptUtil config = new PasswordDecryptUtil();
mysqlXADataSource.setPassword(config.decryptPassword(fstackDataProperties.getConnectionProperties(), fstackDataProperties.getPassword()));
AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
xaDataSource.setXaDataSource(mysqlXADataSource);
xaDataSource.setMaxPoolSize(fstackDataProperties.getMaxActive());
xaDataSource.setMinPoolSize(fstackDataProperties.getMinIdle());
xaDataSource.setMaxLifetime(fstackDataProperties.getMaxWait());
xaDataSource.setTestQuery(fstackDataProperties.getValidationQuery());
xaDataSource.setUniqueResourceName("fsbDatasource");
return xaDataSource;
}
@Bean(name = "fsbSqlSessionFactory")
public SqlSessionFactory fsbSqlSessionFactory()
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(fsbDatasource());
log.info("fsb xadatasouce init....................................................................");
return sessionFactory.getObject();
}
}
4.开启事务
通过上面几个步骤,就实现了atomikos多数据源事务的配置,现在我们可以直接在方法上加@Transactional注解了。
这样就实现了多数据源的数据一致性。
问题和解决
1.连接耗尽
通过上面的改造之后,虽然我们实现了事务控制,但出现了另外一个问题——连接耗尽。
具体问题和解决方案,请参我另一篇博文
在解决了该问题,过了一段时间后,某次开发环境日志里又出现了同样异常,而且是每隔一分钟打印一次。
经排查,发现fstack数据库地址连不上了,而项目里有定时任务每1分钟去读取数据库的数据。
于是推断是该任务运行导致的。为了验证猜想,先注释掉任务,再观察,发现还是会有这个问题。
这就奇怪了,难道是还有我们不知道的定时任务在运行?于是干脆将开启定时任务功能的注解注释掉,再观察,居然依然有。
于是得出结论,atomikos内部有个定时检测机制,会检测连不上的数据库,日志里打印的信息就是内部定时任务触发的。