[FIXED] Spring boot "no transaction is in progress" with 2 datasources

Issue

I have two databases and i'm trying to save some records to both of them inside a service method.

This gives me the error: org.springframework.dao.InvalidDataAccessApiUsageException: no transaction is in progress; nested exception is javax.persistence.TransactionRequiredException: no transaction is in progress.

Here is entities:

@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "TABLE_NAME")
public class SomeEntity {
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_generator")
  @SequenceGenerator(name = "seq_generator", sequenceName = "SEQ_ID", allocationSize = 1)
  @Id
  @Column(name = "ID", nullable = false)
  private Long id;

  @Column(name = "SOME_STR", nullable = false)
  private String someStr;

  @Column(name = "SOME_INT", nullable = false)
  private Integer someInt;

  public SomeEntity(String someStr, Integer someInt) {
    this.someStr = someStr;
    this.someInt = someInt;
  }
}


@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "TABLE_NAME")
public class SomeEntityHist {
  @Id
  @Column(name = "ID", nullable = false)
  private Long id;

  @Column(name = "SOME_STR", nullable = false)
  private String someStr;

  @Column(name = "SOME_INT", nullable = false)
  private Integer someInt;
}

And here is one of the config files for multiple db connection:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "realEntityManager",
        basePackages = {"com.some.project.files.repository.real"}
)
@RequiredArgsConstructor
@Log4j2
@AutoConfigureOrder(1)
public class RealDatasourceConfig {

    private final Environment env;

    @Primary
    @Bean
    public DataSource realDataSource() throws SQLException {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDriverClassName(Objects.requireNonNullElse(env.getProperty("real.driver-class-name"), "oracle.jdbc.OracleDriver"));
        hikariDataSource.setJdbcUrl(env.getProperty("real.db-url"));
        hikariDataSource.setUsername(env.getProperty("real.username"));
        hikariDataSource.setPassword(env.getProperty("real.password"));
        hikariDataSource.setMinimumIdle(Integer.parseInt(Objects.requireNonNullElse(env.getProperty("real.minPoolSize"), "1")));
        hikariDataSource.setMaximumPoolSize(Integer.parseInt(Objects.requireNonNullElse(env.getProperty("real.maxPoolSize"), "10")));
        Properties props = new Properties();
        props.setProperty("maxStatements", env.getProperty("real.maxStatements", "300"));
        hikariDataSource.setDataSourceProperties(props);
        hikariDataSource.setPoolName(env.getProperty("real.pool-name"));
        hikariDataSource.setConnectionTestQuery(env.getProperty("real.connection-test-query"));
        return hikariDataSource;
    }

    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean realEntityManager(EntityManagerFactoryBuilder builder) throws SQLException {
        return builder
                .dataSource(realDataSource())
                .packages("com.some.project.files.entity.real")
                .persistenceUnit("real")
                .build();
    }

    @Primary
    @Bean(name = "transactionManager")
    public JpaTransactionManager realTransactionManager(EntityManagerFactory realEntityManager) {
        return new JpaTransactionManager(realEntityManager);
    }
}

And here is the other one:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "histEntityManager",
        basePackages = {"com.some.project.files.repository.hist"}
)
@RequiredArgsConstructor
@Log4j2
@AutoConfigureOrder(3)
public class HistDatasourceConfig {

    private final Environment env;


    @Bean
    public DataSource histDataSource() throws SQLException {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDriverClassName(Objects.requireNonNullElse(env.getProperty("hist.driver-class-name"), "oracle.jdbc.OracleDriver"));
        hikariDataSource.setJdbcUrl(env.getProperty("hist.jdbc-url"));
        hikariDataSource.setUsername(env.getProperty("hist.username"));
        hikariDataSource.setPassword(env.getProperty("hist.password"));
        hikariDataSource.setMinimumIdle(Integer.parseInt(Objects.requireNonNullElse(env.getProperty("hist.minPoolSize"), "1")));
        hikariDataSource.setMaximumPoolSize(Integer.parseInt(Objects.requireNonNullElse(env.getProperty("hist.maxPoolSize"), "10")));
        Properties props = new Properties();
        props.setProperty("maxStatements", env.getProperty("hist.maxStatements", "300"));
        hikariDataSource.setDataSourceProperties(props);
        hikariDataSource.setPoolName(env.getProperty("hist.pool-name"));
        hikariDataSource.setConnectionTestQuery(env.getProperty("hist.connection-test-query"));
        return hikariDataSource;
    }

    @Bean("histEntityManager")
    public LocalContainerEntityManagerFactoryBean histEntityManager(EntityManagerFactoryBuilder builder) throws SQLException {
        return builder
                .dataSource(histDataSource())
                .packages("com.some.project.files.entity.hist")
                .persistenceUnit("hist")
                .build();
    }

    @Bean
    public JpaTransactionManager histTransactionManager(EntityManagerFactory histEntityManager) {
        return new JpaTransactionManager(histEntityManager);
    }
}

The problem is about the hist entity. If i save just the other one it saves.

But if i try to save the hist entity like this:

@Override
@Transactional
public void someMethod() {
  SomeEntity entity = new SomeEntity("abc", 123);
  SomeRepository.save(entity);

  SomeEntityHist entityHist = new SomeEntityHist(1L, "abc", 123);
  SomeRepositoryHist.save(entityHist);
}

it saves the first one but it doesn't save the hist and when i look at the logs it just calls a select query and not insert.

And if i try to save the hist entity with saveAndFlush method it gives the error.

What is the reason what can i do about it. Is it about config files?

Solution

Both answers posted by @Airy and @GJohannes is pointing out the parts i was missing but there is also one thing i needed to add: @Qualifier("histEntityManager")

Here is the final config file that works for me:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "histEntityManager",
        transactionManagerRef = "histTransactionManager",
        basePackages = {"com.some.project.files.repository.hist"}
)
@RequiredArgsConstructor
@Log4j2
@AutoConfigureOrder(3)
public class HistDatasourceConfig {

    private final Environment env;


    @Bean
    public DataSource histDataSource() throws SQLException {
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDriverClassName(Objects.requireNonNullElse(env.getProperty("hist.driver-class-name"), "oracle.jdbc.OracleDriver"));
        hikariDataSource.setJdbcUrl(env.getProperty("hist.jdbc-url"));
        hikariDataSource.setUsername(env.getProperty("hist.username"));
        hikariDataSource.setPassword(env.getProperty("hist.password"));
        hikariDataSource.setMinimumIdle(Integer.parseInt(Objects.requireNonNullElse(env.getProperty("hist.minPoolSize"), "1")));
        hikariDataSource.setMaximumPoolSize(Integer.parseInt(Objects.requireNonNullElse(env.getProperty("hist.maxPoolSize"), "10")));
        Properties props = new Properties();
        props.setProperty("maxStatements", env.getProperty("hist.maxStatements", "300"));
        hikariDataSource.setDataSourceProperties(props);
        hikariDataSource.setPoolName(env.getProperty("hist.pool-name"));
        hikariDataSource.setConnectionTestQuery(env.getProperty("hist.connection-test-query"));
        return hikariDataSource;
    }

    @Bean("histEntityManager")
    public LocalContainerEntityManagerFactoryBean histEntityManager(EntityManagerFactoryBuilder builder) throws SQLException {
        return builder
                .dataSource(histDataSource())
                .packages("com.some.project.files.entity.hist")
                .persistenceUnit("hist")
                .build();
    }

    @Bean
    public JpaTransactionManager histTransactionManager(@Qualifier("histEntityManager") EntityManagerFactory histEntityManager) {
        return new JpaTransactionManager(histEntityManager);
    }
}

And ofcourse i'm adding @Transactional(transactionManager = "histTransactionManager")

Answered By - InspectorGadget
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)