spring boot - managing transactions & multiple datasources

Question

I tried to extend the Managing Transactions example in the spring boot guides to two datasources, but the @Transaction annotation seems to work for only one of the datasources.

In "Application.java" I added the beans for the two datasources and their JdbcTemplates. In "BookingService.java" I used the JdbcTemplate belonging to the second datasource.

Here is my "Application.java":

package hello;

import javax.sql.DataSource;

import org.junit.Assert;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

@SpringBootApplication
public class Application {

    private static final Logger log = LoggerFactory.getLogger(Application.class);

    @Bean
    BookingService bookingService() {
        return new BookingService();
    }

    @Primary
    @Bean(name="datasource1")
    @ConfigurationProperties(prefix="datasource1")
    DataSource datasource1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name="jdbcTemplate1")
    @Autowired
    JdbcTemplate jdbcTemplate1(@Qualifier ("datasource1") DataSource datasource) {
        return new JdbcTemplate(datasource);
    }

    @Bean(name="datasource2")
    @ConfigurationProperties(prefix="datasource2")
    DataSource datasource2() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name="jdbcTemplate2")
    @Autowired
    JdbcTemplate jdbcTemplate2(@Qualifier ("datasource2") DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        log.info("Creating tables");
        jdbcTemplate.execute("drop table BOOKINGS if exists");
        jdbcTemplate.execute("create table BOOKINGS("
                + "ID serial, FIRST_NAME varchar(5) NOT NULL)");
        return jdbcTemplate;
    }

    public static void main(String[] args) {
        ApplicationContext ctx = SpringApplication.run(Application.class, args);

        BookingService bookingService = ctx.getBean(BookingService.class);
        bookingService.book("Alice", "Bob", "Carol");
        Assert.assertEquals("First booking should work with no problem", 3,
                bookingService.findAllBookings().size());

        try {
            bookingService.book("Chris", "Samuel");
        }
        catch (RuntimeException e) {
            log.info("v--- The following exception is expect because 'Samuel' is too big for the DB ---v");
            log.error(e.getMessage());
        }

        for (String person : bookingService.findAllBookings()) {
            log.info("So far, " + person + " is booked.");
        }
        log.info("You shouldn't see Chris or Samuel. Samuel violated DB constraints, and Chris was rolled back in the same TX");
        Assert.assertEquals("'Samuel' should have triggered a rollback", 3,
                bookingService.findAllBookings().size());

        try {
            bookingService.book("Buddy", null);
        }
        catch (RuntimeException e) {
            log.info("v--- The following exception is expect because null is not valid for the DB ---v");
            log.error(e.getMessage());
        }

        for (String person : bookingService.findAllBookings()) {
            log.info("So far, " + person + " is booked.");
        }
        log.info("You shouldn't see Buddy or null. null violated DB constraints, and Buddy was rolled back in the same TX");
        Assert.assertEquals("'null' should have triggered a rollback", 3, bookingService
                .findAllBookings().size());
    }
}

And here is "BookingService.java":

package hello;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.annotation.Transactional;

public class BookingService {

    private final static Logger log = LoggerFactory.getLogger(BookingService.class);

    @Autowired
    @Qualifier("jdbcTemplate2")
    JdbcTemplate jdbcTemplate;

    @Transactional
    public void book(String... persons) {
        for (String person : persons) {
            log.info("Booking " + person + " in a seat...");
            jdbcTemplate.update("insert into BOOKINGS(FIRST_NAME) values (?)", person);
        }
    };

    public List<String> findAllBookings() {
        return jdbcTemplate.query("select FIRST_NAME from BOOKINGS", new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString("FIRST_NAME");
            }
        });
    }
}

These are the apllication properties in "application.yml":

datasource1:
    url:        "jdbc:h2:~/h2/ds1;DB_CLOSE_ON_EXIT=FALSE"
    username:   "sa"

datasource2:
    url:        "jdbc:h2:~/h2/ds2;DB_CLOSE_ON_EXIT=FALSE"
    username:   "sa"

The "pom.xml" here is the same as in Managing Transactions.

When the @Primary annotation is on the datasource2 bean, everything works as expected. When the @Primary annotation is on the datasource1 bean, the write in datasource2 is not transactional and one gets the following output:

...

2016-05-27 16:01:23.775  INFO 884 --- [           main] hello.Application                        : So far, Alice is booked.
2016-05-27 16:01:23.775  INFO 884 --- [           main] hello.Application                        : So far, Bob is booked.
2016-05-27 16:01:23.775  INFO 884 --- [           main] hello.Application                        : So far, Carol is booked.
2016-05-27 16:01:23.775  INFO 884 --- [           main] hello.Application                        : So far, Chris is booked.
2016-05-27 16:01:23.775  INFO 884 --- [           main] hello.Application                        : You shouldn't see Chris or Samuel. Samuel violated DB constraints, and Chris was rolled back in the same TX
Exception in thread "main" 2016-05-27 16:01:23.776  INFO 884 --- [       Thread-2] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@3901d134: startup date [Fri May 27 16:01:22 CEST 2016]; root of context hierarchy
java.lang.AssertionError: 'Samuel' should have triggered a rollback expected:<3> but was:<4>
    at org.junit.Assert.fail(Assert.java:88)
    at org.junit.Assert.failNotEquals(Assert.java:834)
    at org.junit.Assert.assertEquals(Assert.java:645)
    at hello.Application.main(Application.java:84)
2016-05-27 16:01:23.778  INFO 884 --- [       Thread-2] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown

So "Chris" wasn't rolled back.

I guess it has something to do with properly initializing both databases. Is this a bug, or am I missing something here?

Thanks!

Answer 1

I added two beans in "Application.java":

@Bean(name="tm1") 
@Autowired
DataSourceTransactionManager tm1(@Qualifier ("datasource1") DataSource datasource) {
    DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);
    return txm;
}

@Bean(name="tm2") 
@Autowired
DataSourceTransactionManager tm2(@Qualifier ("datasource2") DataSource datasource) {
    DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);
    return txm;
}

and changed the @Transactional in "BookingService.java" to:

@Transactional("tm2")

So now we have two resource-local transaction managers, one for each datasource, and it works as expected.

Many thanks to M.Deinum!