Spring - How to configure @Transactional properly with two datasources?

Question

I have two spring Datasource configurations, which looks like this:

@EnableJpaRepositories(basePackages = {"XXXXXX",
                                       "XXXXXX"}, entityManagerFactoryRef = "postgreSqlEM", transactionManagerRef = "postgreSqlTM")
@EnableTransactionManagement
@Configuration
public class PostgreSqlDataSourceConfig {

    @Primary
    @Bean
    @ConfigurationProperties("spring.postgresql.hikari")
    public HikariConfig postgreSqlHikariConfig() {
        return new HikariConfig();
    }

    @Primary
    @Bean
    public DataSource postgreSqlDataSource() {
        return new HikariDataSource(postgreSqlHikariConfig());
    }

    @Primary
    @Bean("postgreSqlEM")
    public LocalContainerEntityManagerFactoryBean postgreSqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(postgreSqlDataSource()).packages("XXXXXX",
                                                                   "XXXXXX",
                                                                   "XXXXXX")

                      .build();
    }

    @Primary
    @Bean("postgreSqlTM")
    public JpaTransactionManager postgreSqlTransactionManager(@Qualifier("postgreSqlEM") EntityManagerFactory emf) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
    }

    @Primary
    @Bean
    public EntityManager getEntityManager(@Qualifier("postgreSqlEM") EntityManagerFactory emf) {
        return emf.createEntityManager();
    }
}

This is the @Primary one, so all @Transactional without value configuration should point to it, as well as all EntityManager injections.

Spring boot starts properly, but all @Transactional annotations are ignored.

I am getting errors like this:

javax.persistence.TransactionRequiredException: javax.persistence.Query.executeUpdate requires active transaction

How do I configure it properly so @Transactional works?

I am on spring boot 2.1.5.RELEASE.

Answer 1

First, configure one TransactionManager per datasource. Two datasources means you will have two TransactionManagers.

Here is an example:

    @Bean(name = "blamTransactionManager")
    @Primary
    public PlatformTransactionManager blamTransactionManager(
        @Qualifier("blamDataSourcePool") final DataSource blamDataSource)
    {
        return new DataSourceTransactionManager(
            blamDataSource);
    }

Second, configure a ChainedTransactionManager using the two transaction managers created above.

Here is an example:

    @Bean(name = "chainedTransactionManager")
    public ChainedTransactionManager transactionManager(
        @Qualifier("blamTransactionManager") final PlatformTransactionManager blamTransactionManager,
        @Qualifier("kapowTransactionManager") final PlatformTransactionManager kapowTransactionManager)
    {
        return new ChainedTransactionManager(
            blamTransactionManager,
            kapowTransactionManager);
    }

Finally, reference the chainedTransactionManager in your Transactional annotation.

For example:

    @Transactional("chainedTransactionManager")

Answer 2

I've done something similar putting the entities which work with one transaction into a separated package. Here is how I configured a single datasource (I've four different datasources).

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    entityManagerFactoryRef = "firstEntityManagerFactory",
    basePackages = "com.example.repository.first"
)
public class FirstDbConfig {

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

    @Bean(name = "firstEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean
    entityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("firstDataSource") DataSource dataSource
    ) {
        return builder
                .dataSource(dataSource)
                .packages("com.example.domain.first")
                .persistenceUnit("first")
                .build();
    }

    @Bean(name = "firstTransactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("firstEntityManagerFactory") EntityManagerFactory
                    entityManagerFactory
    ) {
        return new JpaTransactionManager(entityManagerFactory);
    }

}