Transaction issues within a Spring Boot application with two datasources

Camunda 7 Topics Discussion & Questions
1

Hi there,

We had some issues using Camunda within Spring Boot based microservice, and we could solve the issues. We thought we document our findings here on the Camunda forum.

We are using Camunda in a Microservice environment, where some Microservices are processapplications based on Spring Boot. We are using Camunda 7.12, Spring Boot 2.2.6 and camunda-bpm-spring-boot-starter 3.4.2.
Some of the microservices have two datasources configured one is the Camunda database and the other one is the database of the microservice.

Because of the two datasources, we ran into issues with database transactions. The problem was during the deployment, Camunda holds an exclusive lock on the table ACT_GE_PROPERTY. This lock was not released within the range of 6 to 9 minutes. As database we are using Oracle.

We checked the datasource configuration and changed it as follows as described here

@Configuration
public class DataSourceConfiguration {

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "datasource.microservice")
    public DataSourceProperties microserviceDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "datasource.microservice.configuration")
    public DataSource microserviceDataSource() {
        return microserviceDataSourceProperties()
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    @Primary
    public PlatformTransactionManager transactionManager() {
        return new JpaTransactionManager();
    }

    @Bean
    @ConfigurationProperties(prefix = "datasource.camunda")
    public DataSourceProperties camundaDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "camundaBpmDataSource")
    @ConfigurationProperties(prefix = "datasource.camunda.configuration")
    public DataSource camundaDataSource() {
        return camundaDataSourceProperties()
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    public PlatformTransactionManager camundaTransactionManager(
         @Qualifier("camundaBpmDataSource") DataSource dataSource
    ) {
        return new DataSourceTransactionManager(dataSource);
    }
}

and the corresponding configuration file:

datasource:
  microservice:
    driverClassName: oracle.jdbc.OracleDriver
    platform: oracle
    url: jdbc:oracle:thin:@{server}
    username: username
    password: ******
    configuration:
      autoCommit: false
  camunda:
    driverClassName: oracle.jdbc.OracleDriver
    platform: oracle
    url: jdbc:oracle:thin:@{server}
    username: username
    password: ******
    configuration:
      autoCommit: false

This configuration disables the auto-commit mode, which leaded to an another problem, no data were saved on the Camunda database.
As a site node we had to disable the auto-commit mode, because we got this error message:

Caused by: java.sql.SQLException: Could not commit with auto-commit set on
	at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:2171)
	at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:2207)
	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:366)
	at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java)
	at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:81)

We analyzed the Camunda source code and we figured out that Camunda is using the primary transactionManager for commands like starting a process, deployment etc. So we changed the processengine configuration so that the engine uses the camundaTransactionManager configured in DataSourceConfiguration class.
We changed the configuration using the SpringBootProcessEnginePlugin class as follows:

 @Bean
SpringBootProcessEnginePlugin transactionManagerProcessEnginePlugin(
   @Qualifier("camundaTransactionManager") PlatformTransactionManager camundaTransactionManager) 
{
   return new SpringBootProcessEnginePlugin() {
       @Override
       public void preInit(SpringProcessEngineConfiguration processEngineConfiguration) {
                processEngineConfiguration.setTransactionManager(camundaTransactionManager);
       }
   };
}

With this changes the deployment works perfectly and the data are saved in the database.

Are there any concerns/thoughts about these modifications?

2

@Amin, You’re correct. When multiple data sources are used, it’s required to specify the which transaction manager the process engine should use.

3

Thank you very much @aravindhrs for the feedback.

4