Ensure database isolation level is correctly set

[joaquinfelici]

User Story (Required on creation)

As a user, I want an error to be thrown if the transaction isolation level configured for the database is not correct and to be able to skip this check to be able to set a custom level even if it's not optimal.

Functional Requirements (Required before implementation)

• Prevent the engine from starting if the transaction isolation level is different from the recommended one (READ_COMMITTED).
• Have a flag to allow for this check to be skipped, and log a warning when this flag is enabled and the level is non optimal.

Technical Requirements (Required before implementation)

Requirements

On engine initialisation, the transaction isolation level for a Connection will be checked, and we'll have the following scenarios:

• The isolation level is READ_COMMITTED: no action is done.
• The isolation level is different from READ_COMMITTED: the engine will be stopped with an error messages explaining the reason behind it and suggesting that, if the risk can be accepted, a flag (this is a new flag that is gonna be introduced) skipIsolationLevelCheck can be set to true in order to allow for the engine to start.
• The isolation level is different from READ_COMMITTED and flag skipIsolationLevelCheck is true: A warning will be logged mentioning that the isolation level is not optimal and the engine will start normally.

Limitations of Scope

Hints

• Also add documentation here and here.

Links

• https://jira.camunda.com/browse/SUPPORT-22215
• https://github.com/camunda/product-hub/issues/2380

Breakdown

Pull Requests

Beta Give feedback

1. feat(engine): check transaction isolation level on init #4526
   ci:all-as ci:all-db +2
   
2. docs(engine): add property isolation level property and update documentation camunda-docs-manual#1671
   +0
   

Dev2QA handover

• Does this ticket need a QA test and the testing goals are not clear from the description? Add a Dev2QA handover comment

[tasso94]

Manual testing

Tomcat

Datasource config in server.xml:

<Resource name="jdbc/ProcessEngine"
          auth="Container"
          type="javax.sql.DataSource" 
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          uniqueResourceName="process-engine"
          driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://localhost:5432/process-engine"
          defaultTransactionIsolation="SERIALIZABLE"
          username="camunda"
          password="camunda"
          maxActive="20"
          minIdle="5"
          maxIdle="20" />

Default

04-Sep-2024 06:48:05.817 SEVERE [main] org.apache.catalina.startup.Catalina.start The required Server component failed to start so Tomcat is unable to start.
  org.apache.catalina.LifecycleException: Failed to start component [StandardServer[8005]]
  	at org.apache.catalina.util.LifecycleBase.handleSubClassException(LifecycleBase.java:402)
  	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:179)
  	at org.apache.catalina.startup.Catalina.start(Catalina.java:757)
  	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
  	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
  	at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:345)
  	at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:473)
  Caused by: org.camunda.bpm.engine.ProcessEngineException: ENGINE-08043 Exception while performing 'deploy BPM platform' => 'Start process engine default': ENGINE-12019 The transaction isolation level set for the database is 'SERIALIZABLE' which differs from the recommended value. Please change the isolation level to 'READ_COMMITTED' or set property 'skipIsolationLevelCheck' to true. Please keep in mind that some levels are known to cause deadlocks and other unexpected behaviours.

`skipIsolationLevelCheck` set to `true`

04-Sep-2024 06:52:14.504 WARNING [main] org.camunda.commons.logging.BaseLogger.logWarn ENGINE-12020 The transaction isolation level set for the database is 'SERIALIZABLE' which differs from the recommended value and property skipIsolationLevelCheck is enabled. Please keep in mind that levels different from 'READ_COMMITTED' are known to cause deadlocks and other unexpected behaviours.

WildFly 33

Datasource config in standalone.xml:

<datasource jndi-name="java:jboss/datasources/ProcessEngine" pool-name="ProcessEngine" enabled="true" use-java-context="true" jta="true" use-ccm="true">
    <connection-url>jdbc:postgresql://localhost:5432/process-engine</connection-url>
    <driver>postgresql</driver>
    <transaction-isolation>TRANSACTION_READ_UNCOMMITTED</transaction-isolation>
    <security user-name="camunda" password="camunda"/>
</datasource>
<drivers>
    <driver name="postgresql" module="org.postgresql" />
</drivers>

Default

07:18:04,192 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 80) MSC000001: Failed to start service org.camunda.bpm.platform.process-engine.default: org.jboss.msc.service.StartException in service org.camunda.bpm.platform.process-engine.default: org.camunda.bpm.engine.ProcessEngineException: ENGINE-12019 The transaction isolation level set for the database is 'READ_UNCOMMITTED' which differs from the recommended value. Please change the isolation level to 'READ_COMMITTED' or set property 'skipIsolationLevelCheck' to true. Please keep in mind that some levels are known to cause deadlocks and other unexpected behaviours.
  at org.camunda.bpm.wildfly.camunda-wildfly-subsystem@7.22.0-SNAPSHOT//org.camunda.bpm.container.impl.jboss.service.MscManagedProcessEngineController$1.run(MscManagedProcessEngineController.java:105)
  at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
  at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
  at [email protected]//org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
  at [email protected]//org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1990)
  at [email protected]//org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1486)
  at [email protected]//org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1348)
  at java.base/java.lang.Thread.run(Thread.java:833)
  at [email protected]//org.jboss.threads.JBossThread.run(JBossThread.java:513)
Caused by: org.camunda.bpm.engine.ProcessEngineException: ENGINE-12019 The transaction isolation level set for the database is 'READ_UNCOMMITTED' which differs from the recommended value. Please change the isolation level to 'READ_COMMITTED' or set property 'skipIsolationLevelCheck' to true. Please keep in mind that some levels are known to cause deadlocks and other unexpected behaviours.
  at [email protected]//org.camunda.bpm.engine.impl.cfg.ConfigurationLogger.invalidTransactionIsolationLevel(ConfigurationLogger.java:142)
  at [email protected]//org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.checkTransactionIsolationLevel(ProcessEngineConfigurationImpl.java:1707)
  at [email protected]//org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.initDataSource(ProcessEngineConfigurationImpl.java:1692)
  at [email protected]//org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.init(ProcessEngineConfigurationImpl.java:1149)
  at [email protected]//org.camunda.bpm.engine.impl.cfg.AbstractTransactionProcessEngineConfiguration.init(AbstractTransactionProcessEngineConfiguration.java:54)
  at [email protected]//org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:1120)
  at org.camunda.bpm.wildfly.camunda-wildfly-subsystem@7.22.0-SNAPSHOT//org.camunda.bpm.container.impl.jboss.service.MscManagedProcessEngineController.startProcessEngine(MscManagedProcessEngineController.java:187)
  at org.camunda.bpm.wildfly.camunda-wildfly-subsystem@7.22.0-SNAPSHOT//org.camunda.bpm.container.impl.jboss.service.MscManagedProcessEngineController$2.run(MscManagedProcessEngineController.java:141)
  at org.camunda.bpm.wildfly.camunda-wildfly-subsystem@7.22.0-SNAPSHOT//org.camunda.bpm.container.impl.jboss.service.MscManagedProcessEngineController$2.run(MscManagedProcessEngineController.java:138)
  at org.camunda.bpm.wildfly.camunda-wildfly-subsystem@7.22.0-SNAPSHOT//org.camunda.bpm.container.impl.jboss.util.Tccl.runWithTccl(Tccl.java:54)
  at org.camunda.bpm.wildfly.camunda-wildfly-subsystem@7.22.0-SNAPSHOT//org.camunda.bpm.container.impl.jboss.util.Tccl.runUnderClassloader(Tccl.java:46)
  at org.camunda.bpm.wildfly.camunda-wildfly-subsystem@7.22.0-SNAPSHOT//org.camunda.bpm.container.impl.jboss.service.MscManagedProcessEngineController.startInternal(MscManagedProcessEngineController.java:138)
  at org.camunda.bpm.wildfly.camunda-wildfly-subsystem@7.22.0-SNAPSHOT//org.camunda.bpm.container.impl.jboss.service.MscManagedProcessEngineController$1.run(MscManagedProcessEngineController.java:98)
  ... 8 more

`skipIsolationLevelCheck` set to `true`

07:19:34,889 WARN  [org.camunda.bpm.engine.cfg] (ServerService Thread Pool -- 80) ENGINE-12020 The transaction isolation level set for the database is 'READ_UNCOMMITTED' which differs from the recommended value and property skipIsolationLevelCheck is enabled. Please keep in mind that levels different from 'READ_COMMITTED' are known to cause deadlocks and other unexpected behaviours.

Spring Boot

Datasource config in application.yml:

spring.datasource:
  username: camunda
  password: camunda
  url: jdbc:postgresql://localhost:5432/process-engine
  driver-class-name: org.postgresql.Driver
  hikari:
    transaction-isolation: "TRANSACTION_REPEATABLE_READ"

Default

Caused by: org.camunda.bpm.engine.ProcessEngineException: ENGINE-12019 The transaction isolation level set for the database is 'SERIALIZABLE' which differs from the recommended value. Please change the isolation level to 'READ_COMMITTED' or set property 'skipIsolationLevelCheck' to true. Please keep in mind that some levels are known to cause deadlocks and other unexpected behaviours.
  at org.camunda.bpm.engine.impl.cfg.ConfigurationLogger.invalidTransactionIsolationLevel(ConfigurationLogger.java:142) ~[camunda-engine-7.22.0-SNAPSHOT.jar:7.22.0-SNAPSHOT]
  at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.checkTransactionIsolationLevel(ProcessEngineConfigurationImpl.java:1707) ~[camunda-engine-7.22.0-SNAPSHOT.jar:7.22.0-SNAPSHOT]
  at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.initDataSource(ProcessEngineConfigurationImpl.java:1692) ~[camunda-engine-7.22.0-SNAPSHOT.jar:7.22.0-SNAPSHOT]
  at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.init(ProcessEngineConfigurationImpl.java:1149) ~[camunda-engine-7.22.0-SNAPSHOT.jar:7.22.0-SNAPSHOT]
  at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:1120) ~[camunda-engine-7.22.0-SNAPSHOT.jar:7.22.0-SNAPSHOT]
  at org.camunda.bpm.engine.spring.SpringTransactionsProcessEngineConfiguration.buildProcessEngine(SpringTransactionsProcessEngineConfiguration.java:65) ~[camunda-engine-spring-6-7.22.0-20240830.164344-61.jar:7.22.0-SNAPSHOT]
  at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:55) ~[camunda-engine-spring-6-7.22.0-20240830.164344-61.jar:7.22.0-SNAPSHOT]
  at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:34) ~[camunda-engine-spring-6-7.22.0-20240830.164344-61.jar:7.22.0-SNAPSHOT]
  at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:182) ~[spring-beans-6.1.10.jar:6.1.10]
  ... 31 common frames omitted

`camunda.bpm.generic-properties.properties.skip-isolation-level-check: true`

2024-09-04T07:28:13.791+02:00  WARN 27495 --- [           main] org.camunda.bpm.engine.cfg               : ENGINE-12020 The transaction isolation level set for the database is 'SERIALIZABLE' which differs from the recommended value and property skipIsolationLevelCheck is enabled. Please keep in mind that levels different from 'READ_COMMITTED' are known to cause deadlocks and other unexpected behaviours.

[tasso94]

Handover Dev 2 QA

I have already tested Spring Boot, WildFly, and Tomcat. Maybe we can focus for the QA test on more exotic setups like WebLogic, WebSphere?

[gbetances089]

So I have tried this on this setup:

• Websphere Liberty (Using this guide)
  Datasource config in server.xml:

  <dataSource
      id="CamundaBpmDataSource"
      jndiName="jdbc/ProcessEngine"
      type="javax.sql.ConnectionPoolDataSource"
      isolationLevel="TRANSACTION_READ_COMMITTED">
    <jdbcDriver
        javax.sql.DataSource="org.postgresql.jdbc3.Jdbc3ConnectionPool"
        libraryRef="Camunda" />

    <properties
        serverName ="localhost"
        portNumber = "5432"
        databaseName = "process-engine"
        user="camunda"
        password="camunda" />
  </dataSource>

• PostgresSQL 13

docker run -it --rm \
    --name=postgres13 \
    -p 5432:5432 \
    -e POSTGRES_DB=process-engine \
    -e POSTGRES_PASSWORD=camunda \
    -e POSTGRES_USER=camunda \
    postgres:13.2

And everything comes up fine (video) and on the logs I can't find the error code for the isolation db error/the engine comes up fine, is this expected? as we are looking for "READ_COMMITTED" + engine should stop working

[tasso94]

Hi @gbetances089,

what happens if you change isolationLevel="TRANSACTION_READ_COMMITTED" to isolationLevel="TRANSACTION_ SERIALIZABLE"?

Using the first one forces the database to use READ_COMMITTED, which is the expected transaction isolation level.

Best,
Tassilo

[gbetances089]

Now after updating the iso level, I got the error and engine stopped.

  <dataSource
      id="CamundaBpmDataSource"
      jndiName="jdbc/ProcessEngine"
      type="javax.sql.ConnectionPoolDataSource"
      isolationLevel="TRANSACTION_SERIALIZABLE">
    <jdbcDriver
        javax.sql.DataSource="org.postgresql.jdbc3.Jdbc3ConnectionPool"
        libraryRef="Camunda" />

    <properties
        serverName ="localhost"
        portNumber = "5432"
        databaseName = "process-engine"
        user="camunda"
        password="camunda" />
  </dataSource>

```xml [9/16/24, 10:10:48:159 CEST] 00000037 com.ibm.ejs.container.BusinessExceptionMappingStrategy E CNTR0020E: EJB threw an unexpected (non-declared) exception during invocation of method "start" on bean "BeanId(camunda-bpm-platform#camunda-ibm-websphere-service.jar#BpmPlatformBootstrap, null)". Exception data: org.camunda.bpm.engine.ProcessEngineException: ENGINE-08043 Exception while performing 'deploying Camunda Platform' => 'Start process engine default': ENGINE-12019 The transaction isolation level set for the database is 'SERIALIZABLE' which differs from the recommended value. Please change the isolation level to 'READ_COMMITTED' or set property 'skipIsolationLevelCheck' to true. Please keep in mind that some levels are known to cause deadlocks and other unexpected behaviours. at org.camunda.bpm.container.impl.ContainerIntegrationLogger.exceptionWhilePerformingOperationStep(ContainerIntegrationLogger.java:316) at org.camunda.bpm.container.impl.spi.DeploymentOperation.execute(DeploymentOperation.java:136) at org.camunda.bpm.container.impl.jmx.MBeanServiceContainer.executeDeploymentOperation(MBeanServiceContainer.java:160) at org.camunda.bpm.container.impl.spi.DeploymentOperation$DeploymentOperationBuilder.execute(DeploymentOperation.java:216) at org.camunda.bpm.container.impl.ejb.EjbBpmPlatformBootstrap.start(EjbBpmPlatformBootstrap.java:76) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at com.ibm.ejs.container.interceptors.InterceptorProxy.invokeInterceptor(InterceptorProxy.java:200) at com.ibm.ejs.container.interceptors.InvocationContextImpl.proceed(InvocationContextImpl.java:585) at com.ibm.ejs.container.interceptors.InvocationContextImpl.doLifeCycle(InvocationContextImpl.java:326) at com.ibm.ejs.container.SingletonBeanO.callTransactionalLifecycleInterceptors(SingletonBeanO.java:221) at com.ibm.ejs.container.SingletonBeanO.initialize(SingletonBeanO.java:307) at com.ibm.ejs.container.BeanOFactory.create(BeanOFactory.java:103) at com.ibm.ejs.container.EJSHome.createSingletonBeanO(EJSHome.java:3483) at com.ibm.ejs.csi.EJBApplicationMetaData.createStartupBeans(EJBApplicationMetaData.java:1001) at com.ibm.ejs.csi.EJBApplicationMetaData.startedModule(EJBApplicationMetaData.java:735) at com.ibm.ws.ejbcontainer.osgi.internal.EJBRuntimeImpl.start(EJBRuntimeImpl.java:1132) at com.ibm.ws.ejbcontainer.osgi.internal.EJBModuleRuntimeContainerImpl.startModule(EJBModuleRuntimeContainerImpl.java:164) at com.ibm.ws.app.manager.module.internal.ModuleHandlerBase.deployModule(ModuleHandlerBase.java:99) at com.ibm.ws.app.manager.module.internal.DeployedModuleInfoImpl.installModule(DeployedModuleInfoImpl.java:49) at com.ibm.ws.app.manager.module.internal.SimpleDeployedAppInfoBase.deployModules(SimpleDeployedAppInfoBase.java:597) at com.ibm.ws.app.manager.module.internal.SimpleDeployedAppInfoBase.installApp(SimpleDeployedAppInfoBase.java:511) at com.ibm.ws.app.manager.module.internal.DeployedAppInfoBase.deployApp(DeployedAppInfoBase.java:349) at com.ibm.ws.app.manager.ear.internal.EARApplicationHandlerImpl.install(EARApplicationHandlerImpl.java:77) at com.ibm.ws.app.manager.internal.statemachine.StartAction.execute(StartAction.java:182) at com.ibm.ws.app.manager.internal.statemachine.ApplicationStateMachineImpl.enterState(ApplicationStateMachineImpl.java:1367) at com.ibm.ws.app.manager.internal.statemachine.ApplicationStateMachineImpl.run(ApplicationStateMachineImpl.java:910) at com.ibm.ws.threading.internal.ExecutorServiceImpl$RunnableWrapper.run(ExecutorServiceImpl.java:245) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:840) Caused by: org.camunda.bpm.engine.ProcessEngineException: ENGINE-12019 The transaction isolation level set for the database is 'SERIALIZABLE' which differs from the recommended value. Please change the isolation level to 'READ_COMMITTED' or set property 'skipIsolationLevelCheck' to true. Please keep in mind that some levels are known to cause deadlocks and other unexpected behaviours. ```

After setting the new property in the bpm-platform.xml file:

[9/16/24, 12:24:48:255 CEST] 00000037 org.camunda.bpm.engine.cfg W ENGINE-12020 The transaction isolation level set for the database is 'SERIALIZABLE' which differs from the recommended value and property skipIsolationLevelCheck is enabled. Please keep in mind that levels different from 'READ_COMMITTED' are known to cause deadlocks and other unexpected behaviours.

Other logs that I found where:

[9/16/24, 12:24:48:114 CEST] 00000037 com.ibm.ws.jdbc.internal.JDBCDriverService W DSRA8020E: Warning: The property 'skipIsolationLevelCheck' does not exist on the DataSource class org.postgresql.ds.PGConnectionPoolDataSource.

[9/16/24, 12:25:02:343 CEST] 00000040 org.camunda.bpm.container E ENGINE-08044 Exception while performing 'Stopping process engines': Cannot find process application with name InvoiceProcessApplication: deployedProcessApplication is null org.camunda.bpm.engine.exception.NullValueException: Cannot find process application with name InvoiceProcessApplication: deployedProcessApplication is null at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

Testcase - https://camunda.testrail.com/index.php?/cases/view/510472&group_by=cases:section_id&group_order=asc&display_deleted_cases=0&group_id=858