Camunda CockroachDB RETRY_SERIALIZABLE exception

hi,

I am using CockroachDB with Camunda framework, so all the DB transactions are managed by the Camunda engine. I keep getting the following exception:

org.springframework.orm.jpa.JpaSystemException: Unable to commit against JDBC Connection; nested exception is org.hibernate.TransactionException: Unable to commit against JDBC Connection
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:331) ~[spring-orm-5.3.13.jar!/:5.3.13]
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233) ~[spring-orm-5.3.13.jar!/:5.3.13]
        at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:566) ~[spring-orm-5.3.13.jar!/:5.3.13]
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743) ~[spring-tx-5.3.13.jar!/:5.3.13]
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711) ~[spring-tx-5.3.13.jar!/:5.3.13]
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:152) ~[spring-tx-5.3.13.jar!/:5.3.13]
        at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:70) ~[camunda-engine-spring-7.16.1-ee.jar!/:7.16.1-ee]
        at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70) ~[camunda-engine-7.16.1-ee.jar!/:7.16.1-ee]
        at org.camunda.bpm.engine.impl.interceptor.CommandCounterInterceptor.execute(CommandCounterInterceptor.java:35) ~[camunda-engine-7.16.1-ee.jar!/:7.16.1-ee]
        at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33) ~[camunda-engine-7.16.1-ee.jar!/:7.16.1-ee]
        at org.camunda.bpm.engine.impl.interceptor.CrdbTransactionRetryInterceptor.execute(CrdbTransactionRetryInterceptor.java:46) ~[camunda-engine-7.16.1-ee.jar!/:7.16.1-ee]
        at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobHelper.executeJob(ExecuteJobHelper.java:57) ~[camunda-engine-7.16.1-ee.jar!/:7.16.1-ee]
        at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.executeJob(ExecuteJobsRunnable.java:110) ~[camunda-engine-7.16.1-ee.jar!/:7.16.1-ee]
        at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.run(ExecuteJobsRunnable.java:71) ~[camunda-engine-7.16.1-ee.jar!/:7.16.1-ee]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
        at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]
Caused by: org.hibernate.TransactionException: Unable to commit against JDBC Connection
        at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:92) ~[hibernate-core-5.4.32.Final.jar!/:5.4.32.Final]
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:282) ~[hibernate-core-5.4.32.Final.jar!/:5.4.32.Final]
        at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101) ~[hibernate-core-5.4.32.Final.jar!/:5.4.32.Final]
        at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:562) ~[spring-orm-5.3.13.jar!/:5.3.13]
        ... 14 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE - failed preemptive refresh): "sql txn" meta={id=9e5e35c2 key=/Table/86/1/"e272b892-b4d7-11ec-9086-7ebd38bb667b"/0 pri=0.08698208 epo=0 ts=1649159989.730620747,2 min=1649159983.781154722,0 seq=564} lock=true stat=PENDING rts=1649159983.781154722,0 wto=false max=1649159984.281154722,0
  Hint: See: https://www.cockroachlabs.com/docs/v20.2/transaction-retry-error-reference.html#retry_serializable
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.18.jar!/:42.2.18]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) ~[postgresql-42.2.18.jar!/:42.2.18]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) ~[postgresql-42.2.18.jar!/:42.2.18]
        at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:857) ~[postgresql-42.2.18.jar!/:42.2.18]
        at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:879) ~[postgresql-42.2.18.jar!/:42.2.18]
        at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:387) ~[HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar!/:na]
        at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:86)

These are some of the configurations that I have tried:

maximum-pool-size: 10
minimum-idle: 2
auto-commit: false
connection_timeout: 50000

@Bean
public ProcessEngine processEngine(ProcessEngineFactoryBean factoryBean) {
     return setProcessEngineConfigurations(factoryBean.getProcessEngineConfiguration());
}
private ProcessEngine setProcessEngineConfigurations(ProcessEngineConfigurationImpl config) {
   config.setDatabaseSchemaUpdate("true");
   config.setHistory("audit");
   config.setCommandRetries(5);
   return config.buildProcessEngine();
}

In the camunda process engine I set the number of retries to 5:

config.setDatabaseSchemaUpdate(“true”);
config.setHistory(“audit”);
config.setCommandRetries(5);

The explanation is taken from the article:

commandRetries - Integer Only used with CockroachDB. Specifies how many times a Command will be retried before a CrdbTransactionRetryException is reported to the caller

I also checked how concurrent access on different transactions may be causing conflicts: article.

Is there any other process engine setting that will prevent the RETRY_SERIALIZABLE exception when concurrent transactions are executed?

Thanks,
Malin

Did you ever got resolution to this issue?