Hi all,
I have PSQL errors due to “terminating connection due to idle-in-transaction timeout”
occur while running a Camunda process.
Analyzing the errors stack it would appear that the error is thrown when I set (in my source code: at com.MyAdapter.execute(MyAdapter.java:162)) a variable camunda:
execution.setVariable(ProcessConstants.CUR_PAGE_VAR_NAME, new Integer(curPage));
Below is the complete error stack dumped by Camunda process:
### Error querying database. Cause: org.postgresql.util.PSQLException: FATAL: terminating connection due to idle-in-transaction timeout
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/HistoricVariableInstance.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select * from ACT_HI_VARINST where ID_ = ?
### Cause: org.postgresql.util.PSQLException: FATAL: terminating connection due to idle-in-transaction timeout
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.postgresql.util.PSQLException: FATAL: terminating connection due to idle-in-transaction timeout
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/HistoricVariableInstance.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select * from ACT_HI_VARINST where ID_ = ?
### Cause: org.postgresql.util.PSQLException: FATAL: terminating connection due to idle-in-transaction timeout
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectById(DbSqlSession.java:108)
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectById(DbEntityManager.java:202)
at org.camunda.bpm.engine.impl.history.handler.DbHistoryEventHandler.insertHistoricVariableUpdateEntity(DbHistoryEventHandler.java:110)
at org.camunda.bpm.engine.impl.history.handler.DbHistoryEventHandler.handleEvent(DbHistoryEventHandler.java:40)
at org.camunda.bpm.engine.impl.history.event.HistoryEventProcessor.processHistoryEvents(HistoryEventProcessor.java:74)
at org.camunda.bpm.engine.impl.persistence.entity.VariableInstanceHistoryListener.onUpdate(VariableInstanceHistoryListener.java:60)
at org.camunda.bpm.engine.impl.persistence.entity.VariableInstanceHistoryListener.onUpdate(VariableInstanceHistoryListener.java:29)
at org.camunda.bpm.engine.impl.core.variable.scope.AbstractVariableScope.invokeVariableLifecycleListenersUpdate(AbstractVariableScope.java:365)
at org.camunda.bpm.engine.impl.core.variable.scope.AbstractVariableScope.invokeVariableLifecycleListenersUpdate(AbstractVariableScope.java:359)
at org.camunda.bpm.engine.impl.core.variable.scope.AbstractVariableScope.setVariableLocal(AbstractVariableScope.java:316)
at org.camunda.bpm.engine.impl.core.variable.scope.AbstractVariableScope.setVariable(AbstractVariableScope.java:295)
at org.camunda.bpm.engine.impl.core.variable.scope.AbstractVariableScope.setVariable(AbstractVariableScope.java:303)
at org.camunda.bpm.engine.impl.core.variable.scope.AbstractVariableScope.setVariable(AbstractVariableScope.java:289)
at com.MyAdapter.execute(MyAdapter.java:162)
at org.camunda.bpm.engine.impl.bpmn.delegate.JavaDelegateInvocation.invoke(JavaDelegateInvocation.java:36)
at org.camunda.bpm.engine.impl.delegate.DelegateInvocation.proceed(DelegateInvocation.java:54)
at org.camunda.bpm.engine.impl.delegate.DefaultDelegateInterceptor.handleInvocationInContext(DefaultDelegateInterceptor.java:87)
at org.camunda.bpm.engine.impl.delegate.DefaultDelegateInterceptor.handleInvocation(DefaultDelegateInterceptor.java:59)
at org.camunda.bpm.engine.impl.bpmn.behavior.ServiceTaskDelegateExpressionActivityBehavior$3.call(ServiceTaskDelegateExpressionActivityBehavior.java:112)
at org.camunda.bpm.engine.impl.bpmn.behavior.ServiceTaskDelegateExpressionActivityBehavior$3.call(ServiceTaskDelegateExpressionActivityBehavior.java:98)
at org.camunda.bpm.engine.impl.bpmn.behavior.AbstractBpmnActivityBehavior.executeWithErrorPropagation(AbstractBpmnActivityBehavior.java:108)
at org.camunda.bpm.engine.impl.bpmn.behavior.ServiceTaskDelegateExpressionActivityBehavior.performExecution(ServiceTaskDelegateExpressionActivityBehavior.java:123)
at org.camunda.bpm.engine.impl.bpmn.behavior.TaskActivityBehavior.execute(TaskActivityBehavior.java:65)
at org.camunda.bpm.engine.impl.pvm.runtime.operation.PvmAtomicOperationActivityExecute$2.callback(PvmAtomicOperationActivityExecute.java:57)
at org.camunda.bpm.engine.impl.pvm.runtime.operation.PvmAtomicOperationActivityExecute$2.callback(PvmAtomicOperationActivityExecute.java:46)
at org.camunda.bpm.engine.impl.pvm.runtime.PvmExecutionImpl.continueIfExecutionDoesNotAffectNextOperation(PvmExecutionImpl.java:1958)
at org.camunda.bpm.engine.impl.pvm.runtime.operation.PvmAtomicOperationActivityExecute.execute(PvmAtomicOperationActivityExecute.java:38)
at org.camunda.bpm.engine.impl.pvm.runtime.operation.PvmAtomicOperationActivityExecute.execute(PvmAtomicOperationActivityExecute.java:27)
at org.camunda.bpm.engine.impl.interceptor.AtomicOperationInvocation.execute(AtomicOperationInvocation.java:91)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.invokeNext(CommandInvocationContext.java:125)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performNext(CommandInvocationContext.java:104)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performOperation(CommandInvocationContext.java:79)
at org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.performOperation(ExecutionEntity.java:618)
at org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.performOperation(ExecutionEntity.java:594)
at org.camunda.bpm.engine.impl.pvm.runtime.PvmExecutionImpl$5.callback(PvmExecutionImpl.java:1897)
at org.camunda.bpm.engine.impl.pvm.runtime.PvmExecutionImpl$5.callback(PvmExecutionImpl.java:1894)
at org.camunda.bpm.engine.impl.pvm.runtime.PvmExecutionImpl.continueExecutionIfNotCanceled(PvmExecutionImpl.java:1964)
at org.camunda.bpm.engine.impl.pvm.runtime.PvmExecutionImpl.dispatchDelayedEventsAndPerformOperation(PvmExecutionImpl.java:1913)
at org.camunda.bpm.engine.impl.pvm.runtime.PvmExecutionImpl.dispatchDelayedEventsAndPerformOperation(PvmExecutionImpl.java:1894)
at org.camunda.bpm.engine.impl.pvm.runtime.operation.PvmAtomicOperationTransitionNotifyListenerStart.eventNotificationsCompleted(PvmAtomicOperationTransitionNotifyListenerStart.java:57)
at org.camunda.bpm.engine.impl.pvm.runtime.operation.PvmAtomicOperationTransitionNotifyListenerStart.eventNotificationsCompleted(PvmAtomicOperationTransitionNotifyListenerStart.java:27)
at org.camunda.bpm.engine.impl.core.operation.AbstractEventAtomicOperation.execute(AbstractEventAtomicOperation.java:65)
at org.camunda.bpm.engine.impl.interceptor.AtomicOperationInvocation.execute(AtomicOperationInvocation.java:91)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.invokeNext(CommandInvocationContext.java:125)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performNext(CommandInvocationContext.java:104)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performOperation(CommandInvocationContext.java:79)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performOperation(CommandInvocationContext.java:69)
at org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.performOperationSync(ExecutionEntity.java:629)
at org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.performOperationSync(ExecutionEntity.java:604)
at org.camunda.bpm.engine.impl.core.operation.AbstractEventAtomicOperation.execute(AbstractEventAtomicOperation.java:58)
at org.camunda.bpm.engine.impl.interceptor.AtomicOperationInvocation.execute(AtomicOperationInvocation.java:91)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.invokeNext(CommandInvocationContext.java:125)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performNext(CommandInvocationContext.java:104)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performOperation(CommandInvocationContext.java:79)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performOperation(CommandInvocationContext.java:69)
at org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.performOperationSync(ExecutionEntity.java:629)
at org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.performOperationSync(ExecutionEntity.java:604)
at org.camunda.bpm.engine.impl.core.operation.AbstractEventAtomicOperation.execute(AbstractEventAtomicOperation.java:58)
at org.camunda.bpm.engine.impl.interceptor.AtomicOperationInvocation.execute(AtomicOperationInvocation.java:91)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.invokeNext(CommandInvocationContext.java:125)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performNext(CommandInvocationContext.java:104)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performOperation(CommandInvocationContext.java:79)
at org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.performOperation(ExecutionEntity.java:618)
at org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.performOperation(ExecutionEntity.java:594)
at org.camunda.bpm.engine.impl.pvm.runtime.operation.PvmAtomicOperationTransitionCreateScope.scopeCreated(PvmAtomicOperationTransitionCreateScope.java:34)
at org.camunda.bpm.engine.impl.pvm.runtime.operation.PvmAtomicOperationCreateScope.execute(PvmAtomicOperationCreateScope.java:50)
at org.camunda.bpm.engine.impl.pvm.runtime.operation.PvmAtomicOperationCreateScope.execute(PvmAtomicOperationCreateScope.java:24)
at org.camunda.bpm.engine.impl.interceptor.AtomicOperationInvocation.execute(AtomicOperationInvocation.java:91)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.invokeNext(CommandInvocationContext.java:125)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performNext(CommandInvocationContext.java:112)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext$1.call(CommandInvocationContext.java:95)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext$1.call(CommandInvocationContext.java:93)
at org.camunda.bpm.engine.impl.context.ProcessApplicationClassloaderInterceptor.call(ProcessApplicationClassloaderInterceptor.java:44)
at org.camunda.bpm.application.AbstractProcessApplication.execute(AbstractProcessApplication.java:114)
at org.camunda.bpm.application.AbstractProcessApplication.execute(AbstractProcessApplication.java:125)
at org.camunda.bpm.engine.impl.context.Context.executeWithinProcessApplication(Context.java:192)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performNext(CommandInvocationContext.java:93)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performOperation(CommandInvocationContext.java:79)
at org.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.performOperation(CommandInvocationContext.java:69)
at org.camunda.bpm.engine.impl.jobexecutor.AsyncContinuationJobHandler.execute(AsyncContinuationJobHandler.java:76)
at org.camunda.bpm.engine.impl.jobexecutor.AsyncContinuationJobHandler.execute(AsyncContinuationJobHandler.java:36)
at org.camunda.bpm.engine.impl.persistence.entity.JobEntity.execute(JobEntity.java:129)
at org.camunda.bpm.engine.impl.cmd.ExecuteJobsCmd.execute(ExecuteJobsCmd.java:99)
at org.camunda.bpm.engine.impl.cmd.ExecuteJobsCmd.execute(ExecuteJobsCmd.java:36)
at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:104)
at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:66)
at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobHelper.executeJob(ExecuteJobHelper.java:36)
at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobHelper.executeJob(ExecuteJobHelper.java:29)
at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.executeJob(ExecuteJobsRunnable.java:88)
at org.camunda.bpm.engine.impl.jobexecutor.ExecuteJobsRunnable.run(ExecuteJobsRunnable.java:57)
at org.springframework.cloud.sleuth.instrument.async.TraceRunnable.run(TraceRunnable.java:65)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
In my Camunda project with SpringBoot framework I’m using Camunda 7.8.0, OpenJDK 1.8, SpringBoot 2.1.5.RELEASE and Camunda is configured with PostgreSQL 11.0 as DBMS and with Hikari as DbConnection pool.
Note: PostgreSQL is only used for Camunda (internal ACID) transactions; the application is stateless and does not use the database for application scope.
Here is my Spring Boot/Camunda/Datasource/Hibernate/Hikari configuration:
Camunda Job Excecution Settings
# = CAMUNDA Job Execution settings
# defualt =3
camunda.bpm.job-execution.core-pool-size=200
# defualt =3
camunda.bpm.job-execution.max-jobs-per-acquisition=200
# default =10
camunda.bpm.job-execution.max-pool-size=600
# defualt =3
camunda.bpm.job-execution.queue-capacity=200
JobExectuor Custom Properties (not read automatically by SpringBoot Config Engine)
# ===============================
# = CAMUNDA CUSTOM CONFIGURATION
# ===============================
# Camunda JobExectuor->MaxWait
camunda.bpm.job-execution.wait-time-in-millis=1000
# JobExectuor->WaitTimeInMillis
camunda.bpm.job-execution.max-wait=1000
Datasource Configuration
# ===============================
# = DATASOURCE
# ===============================
## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
#parameters that identifies the database and its user
spring.datasource.url=jdbc:postgresql://${DB_HOST}:${DB_PORT}/wfm_db
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}
spring.datasource.platform=postgresql
Hibernata Configuration
# ===============================
# = HIBERNATE
# ===============================
# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
# Disabling contextual LOB creation as createClob() method threw error
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false
#spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate5.SpringSessionContext
Hikari Configuration
# ===============================
# = HIKARI (Database Connection Pooling)
# ===============================
logging.level.com.zaxxer.hikari.HikariConfig=${HIKARI_LOGLEVEL}
logging.level.org.hibernate.SQL=${HIKARI_LOGLEVEL}
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=${HIKARI_LOGLEVEL}
spring.datasource.hikari.minimumIdle=3
spring.datasource.hikari.maximumPoolSize=8
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.poolName=SpringBootJPAHikariCP
spring.datasource.hikari.maxLifetime=1800000
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.jdbc-url=jdbc:postgresql://${DB_HOST}:${DB_PORT}/wfm_db
spring.datasource.hikari.username=${DB_USER}
spring.datasource.hikari.password=${DB_PASSWORD}
Any solution to fix the problem?
Thanks in advance,
Giuseppe