@Transaction not working on multi datasource Spring Boot + MyBatis application

Question

I'm trying to configure Spring Boot + MyBatis application which should work with several datasources. I tried to do it similar to sample here.

Querying and updating data is working, but when I wrote the unit test, I found that @Transactional is not working. Transactions must work between all databases. It means that, if one method with @Transactional make updates on both databases then everything should rollback in case of exception.

It is a sample application for testing purposes and co-workers. After successful configuring the new applications will be configured and developed in similar manner.

Maven:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.aze.mybatis</groupId>
    <artifactId>sample-one</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.6.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4.0</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.5.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Below are the configuration classes:

package com.aze.mybatis.sampleone;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
public class Application {

    public static void main(String[] args) throws Exception {
        SpringApplication.run(Application.class, args);
    }

}

Config to database BSCS (Oracle)

package com.aze.mybatis.sampleone.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.aze.mybatis.sampleone.dao", annotationClass = BscsDataSource.class, sqlSessionFactoryRef = BscsDatabaseConfig.SQL_SESSION_FACTORY_NAME)
@EnableTransactionManagement
public class BscsDatabaseConfig {

    static final String SQL_SESSION_FACTORY_NAME = "sessionFactoryBscs";
    private static final String TX_MANAGER = "txManagerBscs";

    @Bean(name = "dataSourceBscs")
    @ConfigurationProperties(prefix = "bscs.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = TX_MANAGER)
    public PlatformTransactionManager txManagerBscs() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean(name = BscsDatabaseConfig.SQL_SESSION_FACTORY_NAME)
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        return sqlSessionFactoryBean.getObject();
    }

}

Config to database ONSUBS (Oracle)

package com.aze.mybatis.sampleone.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.aze.mybatis.sampleone.dao", annotationClass = OnsubsDataSource.class, sqlSessionFactoryRef = OnsubsDatabaseConfig.SQL_SESSION_FACTORY_NAME)
@EnableTransactionManagement
public class OnsubsDatabaseConfig {

    static final String SQL_SESSION_FACTORY_NAME = "sessionFactoryOnsubs";
    private static final String TX_MANAGER = "txManagerOnsubs";

    @Bean(name = "dataSourceOnsubs")
    @Primary
    @ConfigurationProperties(prefix = "onsubs.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = TX_MANAGER)
    @Primary
    public PlatformTransactionManager txManagerOnsubs() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean(name = OnsubsDatabaseConfig.SQL_SESSION_FACTORY_NAME)
    @Primary
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        return sqlSessionFactoryBean.getObject();
    }

}

Annotation for BSCS:

package com.aze.mybatis.sampleone.config;

public @interface BscsDataSource {
}

and ONSUBS:

package com.aze.mybatis.sampleone.config;

public @interface OnsubsDataSource {
}

Mapper interface that should work with ONSUBS:

package com.aze.mybatis.sampleone.dao;

import com.aze.mybatis.sampleone.config.OnsubsDataSource;
import com.aze.mybatis.sampleone.domain.Payment;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
@OnsubsDataSource
public interface PaymentDao {

    Payment getPaymentById(@Param("paymentId") Integer paymentId);

}

and BSCS:

package com.aze.mybatis.sampleone.dao;

import com.aze.mybatis.sampleone.config.BscsDataSource;
import com.aze.mybatis.sampleone.domain.PostpaidBalance;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
@BscsDataSource
public interface PostpaidCustomerDao {

    PostpaidBalance getPostpaidBalance(@Param("customerId") Integer customerId);

    // BigDecimal amount may be used as second parameter, but I want to show, how to work with two parameters where second is object
    void updateDepositAmount(@Param("customerId") Integer customerId, @Param("balance") PostpaidBalance postpaidBalance);

    void updateAzFdlLastModUser(@Param("customerId") Integer customerId, @Param("username") String username);

}

Below is a code with @Transactional

package com.aze.mybatis.sampleone.service;

import com.aze.mybatis.sampleone.dao.PaymentDao;
import com.aze.mybatis.sampleone.dao.PostpaidCustomerDao;
import com.aze.mybatis.sampleone.domain.Payment;
import com.aze.mybatis.sampleone.domain.PostpaidBalance;
import com.aze.mybatis.sampleone.exception.DataNotFoundException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;

@Service
public class PaymentServiceImpl implements PaymentService {

    private static final String MIN_DEPOSIT_AMOUNT = "150";

    @Autowired
    private PaymentDao paymentDao;

    @Autowired
    private PostpaidCustomerDao postpaidCustomerDao;

    @Override
    public PostpaidBalance getPostpaidBalance(Integer customerId) {
        PostpaidBalance balance = postpaidCustomerDao.getPostpaidBalance(customerId);

        if (balance == null) {
            throw new DataNotFoundException(String.format("Can't find any balance information for customer with customer_id = %d", customerId));
        }

        return balance;
    }

    // Note. By default rolling back on RuntimeException and Error but not on checked exceptions
    // If you want to rollback on check exception too then add "rollbackFor = Exception.class"
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void updateDepositAmount(Integer customerId, PostpaidBalance postpaidBalance, String username) {
        postpaidCustomerDao.updateDepositAmount(customerId, postpaidBalance);

        // In case of @Transactional annotation, you can use method from the same class if it doesn't change data on database
        PostpaidBalance balance = getPostpaidBalance(customerId);

        // This logic is for showing that how the @Transactional annotation works.
        // Because of the exception, the previous transaction will rollback
        if (balance.getDeposit().compareTo(new BigDecimal(MIN_DEPOSIT_AMOUNT)) == -1) {
            throw new IllegalArgumentException("The customer can not have deposit less than " + MIN_DEPOSIT_AMOUNT);
        }

        // In case of @Transactional annotation, you must not (!!!) use method from the same (!) class if it changes data on database
        // That is why, postpaidCustomerDao.updateAzFdlLastModUser() used here instead of this.updateAzFdlLastModUser()
        postpaidCustomerDao.updateAzFdlLastModUser(customerId, username);

        // If there is no exception, the transaction will commit
    }
}

Below is the unit test code:

package com.aze.mybatis.sampleone.service;

import com.aze.mybatis.sampleone.Application;
import com.aze.mybatis.sampleone.config.BscsDatabaseConfig;
import com.aze.mybatis.sampleone.config.OnsubsDatabaseConfig;
import com.aze.mybatis.sampleone.domain.PostpaidBalance;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestPropertySource;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.math.BigDecimal;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {Application.class, OnsubsDatabaseConfig.class, BscsDatabaseConfig.class})
@TestPropertySource(locations= "classpath:application.properties")
public class PaymentServiceImplTest extends Assert {


    // My goal is not to write a full and right unit tests, but just show you examples of working with MyBatis

    @Autowired
    private PaymentService paymentService;

    @Before
    public void setUp() throws Exception {
        assert paymentService != null;
    }

    @Test
    public void updateDepositAmount() throws Exception {
        final int customerId = 4301887; // not recommended way. Just for sample
        final String username = "ITCSC";
        boolean exceptionRaised = false;

        PostpaidBalance balance = paymentService.getPostpaidBalance(customerId);
        assertTrue("Find customer with deposit = 0", balance.getDeposit().compareTo(BigDecimal.ZERO) == 0);
        balance.setDeposit(BigDecimal.TEN);

        try {
            paymentService.updateDepositAmount(customerId, balance, username);
        } catch (Exception e) {
            exceptionRaised = true;
        }

        assertTrue(exceptionRaised);
        balance = paymentService.getPostpaidBalance(customerId);
        // We check that transaction was rollback and amount was not changed
        assertTrue(balance.getDeposit().compareTo(BigDecimal.ZERO) == 0);

        final BigDecimal minDepositAmount = new BigDecimal("150");
        balance.setDeposit(minDepositAmount);
        paymentService.updateDepositAmount(customerId, balance, username);

        balance = paymentService.getPostpaidBalance(customerId);
        assertTrue(balance.getDeposit().compareTo(minDepositAmount) != -1);
    }
}

Unit test fails on assertTrue(balance.getDeposit().compareTo(BigDecimal.ZERO) == 0);. The I check the database and see that first update postpaidCustomerDao.updateDepositAmount(customerId, postpaidBalance); was not rollback despite the @Transactional annotation.

Please help to solve problem.

Answer 1

If you have multiple TransactionManagers, you'll need to reference the one you want to use for @Transactional using Bean names or Qualifiers.

In your Java Config:

@Bean("myTM")
public PlatformTransactionManager transactionManager() {
    return new DataSourceTransactionManager(myDatasource());
}

In Services:

@Transactional("myTM")
public void insertWithException(JdbcTemplate jdbcTemplate) {
}

I debug'd my app to see how Spring chooses the TransactionManager. This is done in org.springframework.transaction.interceptor.TransactionAspectSupport#determineTransactionManager

/**
 * Determine the specific transaction manager to use for the given transaction.
 */
protected PlatformTransactionManager determineTransactionManager(TransactionAttribute txAttr) {
    // Do not attempt to lookup tx manager if no tx attributes are set
    if (txAttr == null || this.beanFactory == null) {
        return getTransactionManager();
    }
    String qualifier = txAttr.getQualifier();
    if (StringUtils.hasText(qualifier)) {
        return determineQualifiedTransactionManager(qualifier);
    }
    else if (StringUtils.hasText(this.transactionManagerBeanName)) {
        return determineQualifiedTransactionManager(this.transactionManagerBeanName);
    }
    else {
        PlatformTransactionManager defaultTransactionManager = getTransactionManager();
        if (defaultTransactionManager == null) {
            defaultTransactionManager = this.transactionManagerCache.get(DEFAULT_TRANSACTION_MANAGER_KEY);
            if (defaultTransactionManager == null) {
                defaultTransactionManager = this.beanFactory.getBean(PlatformTransactionManager.class);
                this.transactionManagerCache.putIfAbsent(
                        DEFAULT_TRANSACTION_MANAGER_KEY, defaultTransactionManager);
            }
        }
        return defaultTransactionManager;
    }
}

So it's just getting the default primary PlatformTransactionManager bean.