SpringBoot multi-data source transaction solution


A previous article provided an integration scheme for springboot multi-data source dynamic registration switching. In the subsequent use process, it was found that there were various bug s in transaction control, and it was decided to analyze and solve this problem.


The multi-data source switching process structure diagram is shown below, including several components

  • Customized data source configuration processing, dynamically registered to the system through the DruidDataSource object
  • Custom Data Source Identification Annotations and Aspects
  • Context thread variable holder when data source is switched
  • Customize AbstractRoutingDataSource to implement data source routing switching

problem analysis

After adding the @Transitional annotation to the Controller, the data source switching will be invalid, and only the main database will be operated. After querying the data, the solution is to set the Order of the aspect to -1 to make the execution order before the transaction control interception. Subsequent switching of other libraries or the main library operation is invalid, and the connection obtained is always the connection corresponding to the library after the first switch.

After analyzing the code, it is found that AbstractRoutingDataSource is only responsible for providing the level of getConnection, but the subsequent operation of connection cannot be tracked. The project framework mybatis and jdbcTemplate are mixed, and the subsequent operations at the spring level for transaction/data source/connection The logical level operations of the three are: In the same way, the jdbcTemplate code is relatively simple, so use this as an entry point for further analysis

Through breakpoint debugging, you will find that the execution of the sql statement will eventually fall to the execute method. The beginning of the method is to obtain the connection through DataSourceUtils.getConnection. This is where we need to track. Click to find the jump to the doGetConnection method, which is what we need The specific logic of the analysis

The ConnectionHolder obtained in the first line is the thread holding object corresponding to the current transaction, because we know that the essence of the transaction is that the sql inside the method corresponds to the same database connection. For different nested business methods, the only thing that is the same is The current thread ID is the same, so we can achieve transaction control by binding the connection to the thread

Click on the getResource method and find that the dataSource is used as a key to retrieve the corresponding contextHolder from a Map collection

We seem to have found something here. Before the instantiation of jdbcTemplatechu, the data source was directly assigned a custom DynamicDataSource, so every time we obtain the connection basis in things, the DynamicDataSource object is used as the key, so it will be the same every time! !

    public JdbcTemplate jdbcTemplate(){
        JdbcTemplate jdbcTemplate = null;
            jdbcTemplate = new JdbcTemplate(dynamicDataSource());
        }catch (Exception e){
        return jdbcTemplate;

Later, I found relevant information for mybatis. The default implementation of transaction control is SpringManagedTransaction. After viewing the source code, I found the familiar DataSourceUtils.getConnection, which proves that our analysis direction is correct.



The custom operation class inherits jdbcTemplate and rewrites getDataSource, and assigns the corresponding key of the DataSource we obtained to the data source object of the actual switching library.

public class DynamicJdbcTemplate extends JdbcTemplate {
    public DataSource getDataSource() {
        DynamicDataSource router =  (DynamicDataSource) super.getDataSource();
        DataSource acuallyDataSource = router.getAcuallyDataSource();
        return acuallyDataSource;

    public DynamicJdbcTemplate(DataSource dataSource) {
    public DataSource getAcuallyDataSource() {
        Object lookupKey = determineCurrentLookupKey();
        if (null == lookupKey) {
            return this;
        DataSource determineTargetDataSource = this.determineTargetDataSource();
        return determineTargetDataSource == null ? this : determineTargetDataSource;


Customize the transaction operation class, implement the Transaction interface, and replace the TransitionFactory. The implementation here is slightly different from the online solution. Three variables are defined online, datasource (dynamic data source object)/connection (main connection)/connections (slave library) connection), but the framework needs mybatis and jdbctemplate to be unified, mybatis is controlled from the connection level, jdbctemplate is controlled from the datasource level, so all use key-value pair storage

public class DynamicTransaction implements Transaction {
    private final DynamicDataSource dynamicDataSource;
    private ConcurrentHashMap<String, DataSource> dataSources;
    private ConcurrentHashMap<String, Connection> connections;
    private ConcurrentHashMap<String, Boolean> autoCommits;
    private ConcurrentHashMap<String, Boolean> isConnectionTransactionals;

    public DynamicTransaction(DataSource dataSource) {
        this.dynamicDataSource = (DynamicDataSource) dataSource;
        dataSources = new ConcurrentHashMap<>();
        connections = new ConcurrentHashMap<>();
        autoCommits = new ConcurrentHashMap<>();
        isConnectionTransactionals = new ConcurrentHashMap<>();

    public Connection getConnection() throws SQLException {
        String dataBaseID = DBContextHolder.getDataSource();
        if (!dataSources.containsKey(dataBaseID)) {
            DataSource dataSource = dynamicDataSource.getAcuallyDataSource();
            dataSources.put(dataBaseID, dataSource);
        if (!connections.containsKey(dataBaseID)) {
            Connection connection = DataSourceUtils.getConnection(dataSources.get(dataBaseID));
            connections.put(dataBaseID, connection);
        if (!autoCommits.containsKey(dataBaseID)) {
            boolean autoCommit = connections.get(dataBaseID).getAutoCommit();
            autoCommits.put(dataBaseID, autoCommit);
        if (!isConnectionTransactionals.containsKey(dataBaseID)) {
            boolean isConnectionTransactional = DataSourceUtils.isConnectionTransactional(connections.get(dataBaseID), dataSources.get(dataBaseID));
            isConnectionTransactionals.put(dataBaseID, isConnectionTransactional);
        return connections.get(dataBaseID);

    public void commit() throws SQLException {
        for (String dataBaseID : connections.keySet()) {
            Connection connection = connections.get(dataBaseID);
            boolean isConnectionTransactional = isConnectionTransactionals.get(dataBaseID);
            boolean autoCommit = autoCommits.get(dataBaseID);
            if (connection != null && !isConnectionTransactional && !autoCommit) {

    public void rollback() throws SQLException {
        for (String dataBaseID : connections.keySet()) {
            Connection connection = connections.get(dataBaseID);
            boolean isConnectionTransactional = isConnectionTransactionals.get(dataBaseID);
            boolean autoCommit = autoCommits.get(dataBaseID);
            if (connection != null && !isConnectionTransactional && !autoCommit) {

    public void close() {
        for (String dataBaseID : connections.keySet()) {
            Connection connection = connections.get(dataBaseID);
            DataSource dataSource = dataSources.get(dataBaseID);
            DataSourceUtils.releaseConnection(connection, dataSource);

    public Integer getTimeout() {
        return null;
public class DynamicTransactionFactory extends SpringManagedTransactionFactory {
    public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
        return new DynamicTransaction(dataSource);
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        final PackagesSqlSessionFactoryBean sessionFactory = new PackagesSqlSessionFactoryBean();
        sessionFactory.setTransactionFactory(new DynamicTransactionFactory());
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
        //Turn off camel case conversion to prevent underlined fields from being unmapped
        return sessionFactory.getObject();

transaction manager

The problem of dynamic library switching in transactions is solved, but only for the main library transaction. If the operation of the slave library also requires the characteristics of the transaction, how to operate it? Here, you need to manually register a transaction management for each data source when registering the data source. device

The main library is fixed, masterTransitionManage can be declared directly in the configuration Bean and set as default

    public DataSourceTransactionManager MasterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());

From the transaction manager of the library we can get the dataSource initialization object and then register the singleton object with the Spring container

 public static void registerSingletonBean(String beanName, Object singletonObject) {
        //Convert applicationContext to ConfigurableApplicationContext
        ConfigurableApplicationContext configurableApplicationContext = (ConfigurableApplicationContext) context;
        //Get BeanFactory
        DefaultListableBeanFactory defaultListableBeanFactory = (DefaultListableBeanFactory) configurableApplicationContext.getAutowireCapableBeanFactory();
        if(configurableApplicationContext.containsBean(beanName)) {
        //Dynamically register bean s.
        defaultListableBeanFactory.registerSingleton(beanName, singletonObject);

 SpringBootBeanUtil.registerSingletonBean(key + "TransactionManager", new DataSourceTransactionManager(druidDataSource));

When using it, you only need to specify the transitionFactory name for the @Transitional annotation.


It took three days to solve this problem, and I checked a lot of information and solutions, many of which are only reference or specific, so it is still necessary to grasp the core of the problem and track some source code. For example, this article needs a clear understanding Only by the relationship between Transition-Connection-LocalThread can find the direction of the investigation

In the follow-up, the global transaction that integrates the two-stage submission of XA based on JMS (atomikos) is implemented. Using DruidXADataSrouce, the interaction between the thread pools of druid and atomikos has leaked.

Tags: Mybatis Spring Boot JdbcTemplate

Posted by ravi_aptech on Sun, 01 May 2022 18:35:48 +0300