Spring Boot Batch with two Datasources causes javax.persistence.TransactionRequiredException: Executing an update/delete query

Question

I have a spring boot batch project with two datasources, one for the batch tables, one for the business tables. It works fine reading from the business database.

My configuration looks like

@Configuration
@EnableBatchProcessing
public class DataSourceConfiguration {

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

    @Bean
    @ConfigurationProperties(prefix = "spring.sybasedatasource")
    public DataSource sybaseDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    BatchConfigurer batchConfigurer(DataSource dataSource) {
        return new DefaultBatchConfigurer(dataSource);
    }

    @Bean
    TaskConfigurer taskConfigurer(DataSource dataSource) {
        return new DefaultTaskConfigurer(dataSource);
    }
}

But when I execute an update

public interface DocumentRepository extends JpaRepository<DocumentDescriptor, Integer> {

    @Modifying
    @Query(value = "update document set process_status = :status where process_status = 10001 and document_id = :documentId")
    int updateDocumentStatus(Integer status, Integer documentId);
}

I get a Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query; nested exception is javax.persistence.TransactionRequiredException: Executing an update/delete query

I already looked at some stackoverflow questions and added @Transactional to the repository method. Additionally I created dedicated TransactionManager

@Bean(name="tm1")
public PlatformTransactionManager tm1(@Qualifier ("dataSource") DataSource datasource) {
    return new DataSourceTransactionManager(datasource);
}

@Bean(name="tm2")
public PlatformTransactionManager tm2(@Qualifier ("sybaseDataSource") DataSource datasource) 
{
    return new DataSourceTransactionManager(datasource);
}

and used @Transaction("tm2") but the exception is still the same.

Any ideas what to do to get a proper transaction?

Answer 1

I solved it with

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = 
"...",
    entityManagerFactoryRef = "sybaseEntityManagerFactory",
    transactionManagerRef = "sybaseTransactionManager"
)
public class DataSourceConfiguration {

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

@Bean
@ConfigurationProperties(prefix = "spring.sybasedatasource")
public DataSource sybaseDataSource() {
    return DataSourceBuilder.create().build();
}

@Bean
public LocalContainerEntityManagerFactoryBean sybaseEntityManagerFactory() {
    return new EntityManagerFactoryBuilder(new HibernateJpaVendorAdapter(), Collections.emptyMap(), null)
            .dataSource(sybaseDataSource())
            .packages("...")
            .persistenceUnit(DataSourceConfiguration.class.getSimpleName())
            .build();
}

@Bean
@Primary
public DataSourceTransactionManager postgresTransactionManager(@Qualifier("dataSource") DataSource datasource) {
    return new DataSourceTransactionManager(datasource);
}

@Bean
public JpaTransactionManager sybaseTransactionManager(EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
}
}

and @Transactional("sybaseTransactionManager")