Ensuring Correct Transactions in Service Methods with Multiple Data Sources in Spring Boot

When building enterprise applications, it's often necessary to connect to multiple databases. This can present some unique challenges, especially around transaction management. In this article, we’ll explore how to handle multiple data sources in Spring Boot, ensuring proper transaction management in service methods and avoiding an easily overlooked pitfall.

The Challenge

In a Spring Boot application, you might have two separate databases, each requiring its own transaction manager. By default, Spring uses the primary transaction manager for all @Transactional methods unless specified otherwise. This can lead to issues when service methods interacting with one database inadvertently initiate transactions with another database.

Scenario Example

Imagine you have two databases: one for live operational data and another for archived data. Each database has its own transaction manager:

• LiveDatabaseTransactionManager: Manages transactions for live operational data.

• ArchiveDatabaseTransactionManager: Manages transactions for archived data.

Configuring Multiple Data Sources

Here’s how you can configure multiple data sources and transaction managers in Spring Boot.

1. Define Data Source Properties

In your application.yml:

spring:
  datasource:
    livedb:
      url: jdbc:postgresql://localhost:5432/livedb
      username: livedb_user
      password: livedb_password
      hikari:
        maximum-pool-size: 10
    archivedb:
      url: jdbc:postgresql://localhost:5432/archivedb
      username: archivedb_user
      password: archivedb_password
      hikari:
        maximum-pool-size: 10
  jpa:
    properties:
      hibernate:
        show_sql: true
        format_sql: true

2. Configure Data Sources and Transaction Managers

Live Database Configuration:

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.live.repository",
    entityManagerFactoryRef = "liveEntityManagerFactory",
    transactionManagerRef = "liveTransactionManager"
)
public class LiveDataSourceConfiguration {

    @Bean
    @ConfigurationProperties("spring.datasource.livedb")
    public DataSourceProperties liveDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.livedb.hikari")
    public DataSource liveDataSource() {
        return liveDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean liveEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(liveDataSource())
                .packages("com.example.live.entity")
                .persistenceUnit("livedb")
                .build();
    }

    @Bean
    @Primary
    public PlatformTransactionManager liveTransactionManager(EntityManagerFactory liveEntityManagerFactory) {
        return new JpaTransactionManager(liveEntityManagerFactory);
    }
}

Archived Database Configuration:

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.archive.repository",
    entityManagerFactoryRef = "archiveEntityManagerFactory",
    transactionManagerRef = "archiveTransactionManager"
)
public class ArchiveDataSourceConfiguration {

    @Bean
    @ConfigurationProperties("spring.datasource.archivedb")
    public DataSourceProperties archiveDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.archivedb.hikari")
    public DataSource archiveDataSource() {
        return archiveDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean archiveEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(archiveDataSource())
                .packages("com.example.archive.entity")
                .persistenceUnit("archivedb")
                .build();
    }

    @Bean
    public PlatformTransactionManager archiveTransactionManager(EntityManagerFactory archiveEntityManagerFactory) {
        return new JpaTransactionManager(archiveEntityManagerFactory);
    }
}

The Service Method Examples

Let's assume you have the following service methods intended to read data from the archived database:

Without Explicit Transaction Manager

@Service
@RequiredArgsConstructor
public class DataService {

    private final ArchiveRepository archiveRepository;

    @Transactional
    public List<ArchivedData> getArchivedDataWithoutExplicitTransactionManager() {
        return archiveRepository.findAll();
    }
}

With Explicit Transaction Manager

@Service
@RequiredArgsConstructor
public class DataService {

    private final ArchiveRepository archiveRepository;

    @Transactional("archiveTransactionManager")
    public List<ArchivedData> getArchivedDataWithExplicitTransactionManager() {
        return archiveRepository.findAll();
    }
}

Explanation of the Scenario

Consider a situation where you need to query the archived database in a business flow. The getArchivedDataWithoutExplicitTransactionManager method has a @Transactional annotation without specifying a transaction manager. Since the primary transaction manager connects to the live database, this method will start a transaction against the live database. However, this is wasteful because you only need to run queries on the archived database, not on the live database. This is a mistake that can easily get overlooked.

When archiveRepository.findAll() is called in getArchivedDataWithoutExplicitTransactionManager, it will start another transaction with the archived database because the repository is configured to use archiveTransactionManager through the @EnableJpaRepositories annotation. This leads to the following issues:

1. Unnecessary Transaction: The primary transaction manager (liveTransactionManager) is used even though it is not needed.

2. Resource Wastage: Resources are utilized unnecessarily by the liveTransactionManager.

3. Database Inconsistency: The archiveRepository.findAll() method starts a new transaction and commits immediately, rather than committing as part of the original service method transaction. This can cause data inconsistency issues.

In contrast, the getArchivedDataWithExplicitTransactionManager method specifies the correct transaction manager (archiveTransactionManager) in the @Transactional annotation. This ensures that the entire transaction is managed by archiveTransactionManager, avoiding the issues mentioned above.

Conclusion

When working with multiple data sources in Spring Boot, be mindful of the transaction manager in use in your service methods to avoid transaction boundary issues and ensure data consistency to maintain the integrity of your application’s data.