@Transactional导致无法动态数据源切换
公司目前数据源为主从模式:主库可读写,从库只负责读。使用spring-jdbc提供的AbstractRoutingDataSource结合ThreadLocal存储key,实现数据源动态切换。
最近项目加入数据源切换后,偶尔会报出read-only异常,百思不得其解......
<!--数据源--> <bean id="dsCrm" class="cn.mwee.framework.commons.utils.datasource.RoutingDataSource"> <property name="targetDataSources"> <map key-type="java.lang.String"> <entry key="master" value-ref="dsCrm_master"/> <entry key="slave1" value-ref="dsCrm_slave1"/> <entry key="slave2" value-ref="dsCrm_slave2"/> </map> </property> <!--默认走主库--> <property name="defaultTargetDataSource" ref="dsCrm_master"/> </bean>
RoutingDataSource类是对AbstractRoutingDataSource轻量封装实现determineCurrentLookupKey :
public class RoutingDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DBContext.getDBKey(); } }
对应的业务代码如下,数据源切换在其他项目使用正常,代码迁移过来之后偶发报出read-only异常,数据库处于只读模式。写方法需要事物默认走主库,在该方法前也没有数据源的切换。
@Transactional(rollbackFor = Exception.class) public DataResult settingMarketMsg(SettingMarketMsgRequest request) { ..... } @Slave public DataResult detailMarketMsg(DetailMarketMsgRequest request) { ...... }
因为aop切面只会切入打上@Slave注解的方法并切为从库,方法返回会清除key。所以臆想着肯定不会有问题?思考N久。。。
最后在aop的配置中看到破绽:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 @Component("dynamicDataSourceAspect") 2 public class DynamicDataSourceAspect { 3 public void setCrmDataSource(JoinPoint joinPoint) { 4 MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature(); 5 Method method = methodSignature.getMethod(); 6 // 默认 master 优先 7 DBContext.setDBKey(DbKeyConstant.DS_MASTER); 8 if (method.isAnnotationPresent(Slave.class)) { 9 DBContext.setDBKey(DbKeyConstant.DS_SLAVE_1); 10 } 11 logger.info("Revert DataSource : {} > {}", DBContext.getDBKey(), joinPoint.getSignature()); 12 } 13 public void clearCrmDataSource(JoinPoint joinPoint) { 14 logger.info("Clear DataSource : {} > {}", DBContext.getDBKey(), joinPoint.getSignature()); 15 DBContext.clearDBKey(); 16 } 17 }
aop的xml配置 :
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <aop:aspectj-autoproxy proxy-target-class="true"/> 2 <aop:config> 3 <aop:aspect id="dynamicDataSourceAspect" ref="dynamicDataSourceAspect" order="3"> 4 <aop:pointcut id="dsAspect" 5 expression="@annotation(cn.mwee.framework.commons.utils.datasource.Slave)"/> 6 <aop:before pointcut-ref="dsAspect" method="setCrmDataSource"/> 7 <aop:after-returning pointcut-ref="dsAspect" method="clearCrmDataSource"/> 8 </aop:aspect> 9 </aop:config>
问题出在after-returning 即 方法正常返回之后执行,一旦执行异常就不会执行。此时线程如果没有被回收将一直持有该key。那线程持有该key,怎么跟上述【read-only异常】联系起来呢?
先看事物管理器配置:
<!--事务管理器--> <bean id="crmTxManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dsCrm"/> </bean> <tx:annotation-driven transaction-manager="crmTxManager"/>
DataSourceTransactionManager的源码事物开始doBegin部分:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /** 2 * This implementation sets the isolation level but ignores the timeout. 3 */ 4 @Override 5 protected void doBegin(Object transaction, TransactionDefinition definition) { 6 DataSourceTransactionObject txObject = (DataSourceTransactionObject) transaction; 7 Connection con = null; 8 9 try { 10 11 // 第一次获取数据源,往后直接复用 12 if (txObject.getConnectionHolder() == null || 13 txObject.getConnectionHolder().isSynchronizedWithTransaction()) { 14 Connection newCon = this.dataSource.getConnection(); 15 if (logger.isDebugEnabled()) { 16 logger.debug("Acquired Connection [" + newCon + "] for JDBC transaction"); 17 } 18 txObject.setConnectionHolder(new ConnectionHolder(newCon), true); 19 } 20 21 txObject.getConnectionHolder().setSynchronizedWithTransaction(true); 22 con = txObject.getConnectionHolder().getConnection(); 23 24 Integer previousIsolationLevel = DataSourceUtils.prepareConnectionForTransaction(con, definition); 25 txObject.setPreviousIsolationLevel(previousIsolationLevel); 26 27 // Switch to manual commit if necessary. This is very expensive in some JDBC drivers, 28 // so we don't want to do it unnecessarily (for example if we've explicitly 29 // configured the connection pool to set it already). 30 if (con.getAutoCommit()) { 31 txObject.setMustRestoreAutoCommit(true); 32 if (logger.isDebugEnabled()) { 33 logger.debug("Switching JDBC Connection [" + con + "] to manual commit"); 34 } 35 con.setAutoCommit(false); 36 } 37 38 prepareTransactionalConnection(con, definition); 39 txObject.getConnectionHolder().setTransactionActive(true); 40 41 int timeout = determineTimeout(definition); 42 if (timeout != TransactionDefinition.TIMEOUT_DEFAULT) { 43 txObject.getConnectionHolder().setTimeoutInSeconds(timeout); 44 } 45 46 // Bind the connection holder to the thread. 47 if (txObject.isNewConnectionHolder()) { 48 TransactionSynchronizationManager.bindResource(getDataSource(), txObject.getConnectionHolder()); 49 } 50 } 51 52 catch (Throwable ex) { 53 if (txObject.isNewConnectionHolder()) { 54 DataSourceUtils.releaseConnection(con, this.dataSource); 55 txObject.setConnectionHolder(null, false); 56 } 57 throw new CannotCreateTransactionException("Could not open JDBC Connection for transaction", ex); 58 } 59 }
AbstractRoutingDataSource获取数据源源码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /** 2 * Retrieve the current target DataSource. Determines the 3 * {@link #determineCurrentLookupKey() current lookup key}, performs 4 * a lookup in the {@link #setTargetDataSources targetDataSources} map, 5 * falls back to the specified 6 * {@link #setDefaultTargetDataSource default target DataSource} if necessary. 7 * @see #determineCurrentLookupKey() 8 */ 9 protected DataSource determineTargetDataSource() { 10 // 根据线程绑定的key获取数据源, 如果不存在则获取默认数据源 11 Assert.notNull(this.resolvedDataSources, "DataSource router not initialized"); 12 Object lookupKey = determineCurrentLookupKey(); 13 DataSource dataSource = this.resolvedDataSources.get(lookupKey); 14 if (dataSource == null && (this.lenientFallback || lookupKey == null)) { 15 dataSource = this.resolvedDefaultDataSource; 16 } 17 if (dataSource == null) { 18 throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]"); 19 } 20 return dataSource; 21 }
这样可以解释偶发报出read-only异常了。项目使用tomcat,其中tomcat工作线程池是复用。当tomcat工作线程响应请求,访问带有@Slave方法,数据源切换至从库。由于某种原因异常导致未进入after-returning线程绑定的key未清除。此时访问写方法并tomcat使用同一个工作线程响应请求,通过AbstractRoutingDataSource将获得只读库的数据源,因而会产生报出read-only异常。
问题偶发原因在于必须满足两点:
1、只读请求异常未切数据源
2、复用相同tomcat工作线程池。
找到问题症结之后,自然容易解决:将after-returning 修改为 after(不管是否异常,都执行),每次必清空数据源即可。
<aop:config> <aop:aspect id="dynamicDataSourceAspect" ref="dynamicDataSourceAspect" order="3"> <aop:pointcut id="dsAspect" expression="@annotation(cn.mwee.framework.commons.utils.datasource.Slave)"/> <aop:before pointcut-ref="dsAspect" method="setCrmDataSource"/> <aop:after pointcut-ref="dsAspect" method="clearCrmDataSource"/> </aop:aspect> </aop:config>
参考链接 :