Connection due to idle-in-transaction timeout

Flowable Engine
1

We are getting iddle timout error when using the flowable engine.
After we restart the database and the rest-api, everything works normally for a while but soon after, the error start happening again.
Our workload is very small. We are in testing phase still.

We have a small RDS pg database.

2023-03-20 18:04:46.030 ERROR 1 --- [       task-178] ltAsyncRunnableExecutionExceptionHandler : Job 82635756-c746-11ed-9fda-12478b2be6df failed
org.springframework.transaction.TransactionSystemException: JDBC rollback failed; nested exception is org.postgresql.util.PSQLException: FATAL: terminating connection due to idle-in-transaction timeout
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.translateException(DataSourceTransactionManager.java:435) ~[spring-jdbc-5.3.14.jar:5.3.14]
	at org.springframework.jdbc.support.JdbcTransactionManager.translateException(JdbcTransactionManager.java:188) ~[spring-jdbc-5.3.14.jar:5.3.14]
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:351) ~[spring-jdbc-5.3.14.jar:5.3.14]
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:835) ~[spring-tx-5.3.14.jar:5.3.14]
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:809) ~[spring-tx-5.3.14.jar:5.3.14]
	at org.springframework.transaction.support.TransactionTemplate.rollbackOnException(TransactionTemplate.java:168) ~[spring-tx-5.3.14.jar:5.3.14]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:115)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:144) ~[spring-tx-5.3.14.jar:5.3.14]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:57) ~[flowable-spring-common-6.7.2.jar:6.7.2]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.executeJob(ExecuteAsyncRunnable.java:127) ~[flowable-job-service-6.7.2.jar:6.7.2]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:115) ~[flowable-job-service-6.7.2.jar:6.7.2]
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) ~[na:na]
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ~[na:na]
	at java.base/java.lang.Thread.run(Unknown Source) ~[na:na]
Caused by: org.postgresql.util.PSQLException: FATAL: terminating connection due to idle-in-transaction timeout
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql-42.5.1.jar:42.5.1]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql-42.5.1.jar:42.5.1]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql-42.5.1.jar:42.5.1]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:316) ~[postgresql-42.5.1.jar:42.5.1]
	at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:879) ~[postgresql-42.5.1.jar:42.5.1]
	at org.postgresql.jdbc.PgConnection.rollback(PgConnection.java:922) ~[postgresql-42.5.1.jar:42.5.1]
	at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:396) ~[HikariCP-3.4.5.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java) ~[HikariCP-3.4.5.jar:na]
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doRollback(DataSourceTransactionManager.java:348) ~[spring-jdbc-5.3.14.jar:5.3.14]
	... 13 common frames omitted
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)

After a while using the application, it seems that the engine can’t connect to the database anymore.

2023-03-20 18:04:46.032 ERROR 1 --- [       task-175] o.f.c.e.impl.interceptor.CommandContext  : Error while closing command context
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: HikariDataSource HikariDataSource (flowable-rest) has been closed.
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51)
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:309) ~[spring-jdbc-5.3.14.jar:5.3.14]
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400) ~[spring-tx-5.3.14.jar:5.3.14]
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.handleExistingTransaction(AbstractPlatformTransactionManager.java:434) ~[spring-tx-5.3.14.jar:5.3.14]
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:352) ~[spring-tx-5.3.14.jar:5.3.14]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:137) ~[spring-tx-5.3.14.jar:5.3.14]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:57) ~[flowable-spring-common-6.7.2.jar:6.7.2]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.job.service.impl.asyncexecutor.DefaultAsyncRunnableExecutionExceptionHandler$1.execute(DefaultAsyncRunnableExecutionExceptionHandler.java:60) ~[flowable-job-service-6.7.2.jar:6.7.2]
	at org.flowable.job.service.impl.asyncexecutor.DefaultAsyncRunnableExecutionExceptionHandler$1.execute(DefaultAsyncRunnableExecutionExceptionHandler.java:37) ~[flowable-job-service-6.7.2.jar:6.7.2]
	at org.flowable.engine.impl.interceptor.CommandInvoker$1.run(CommandInvoker.java:67) ~[flowable-engine-6.7.2.jar:6.7.2]
	at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperation(CommandInvoker.java:140) ~[flowable-engine-6.7.2.jar:6.7.2]
	at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperations(CommandInvoker.java:114) ~[flowable-engine-6.7.2.jar:6.7.2]
	at org.flowable.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:72) ~[flowable-engine-6.7.2.jar:6.7.2]
	at org.flowable.engine.impl.interceptor.BpmnOverrideContextInterceptor.execute(BpmnOverrideContextInterceptor.java:26) ~[flowable-engine-6.7.2.jar:6.7.2]
	at org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:53) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:105) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.common.spring.SpringTransactionInterceptor.lambda$execute$0(SpringTransactionInterceptor.java:57) ~[flowable-spring-common-6.7.2.jar:6.7.2]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.3.14.jar:5.3.14]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:57) ~[flowable-spring-common-6.7.2.jar:6.7.2]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.job.service.impl.asyncexecutor.DefaultAsyncRunnableExecutionExceptionHandler.handleException(DefaultAsyncRunnableExecutionExceptionHandler.java:37)
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51) ~[flowable-engine-common-6.7.2.jar:6.7.2]
	at org.flowable.job.service.impl.asyncexecutor.DefaultAsyncRunnableExecutionExceptionHandler.handleException(DefaultAsyncRunnableExecutionExceptionHandler.java:37) ~[flowable-job-service-6.7.2.jar:6.7.2]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.handleFailedJob(ExecuteAsyncRunnable.java:211) ~[flowable-job-service-6.7.2.jar:6.7.2]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.executeJob(ExecuteAsyncRunnable.java:146) ~[flowable-job-service-6.7.2.jar:6.7.2]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:115) ~[flowable-job-service-6.7.2.jar:6.7.2]
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) ~[na:na]
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ~[na:na]
	at java.base/java.lang.Thread.run(Unknown Source) ~[na:na]
Caused by: java.sql.SQLException: HikariDataSource HikariDataSource (flowable-rest) has been closed.
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:96) ~[HikariCP-3.4.5.jar:na]
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:265) ~[spring-jdbc-5.3.14.jar:5.3.14]
	... 29 common frames omitted
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.handleFailedJob(ExecuteAsyncRunnable.java:211)
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.executeJob(ExecuteAsyncRunnable.java:146)
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:115)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: HikariDataSource HikariDataSource (flowable-rest) has been closed.
Exception in thread task-184 org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: HikariDataSource HikariDataSource (flowable-rest) has been closed.
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:309)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:137)
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:57)
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.executeJob(ExecuteAsyncRunnable.java:146)

image
image1203×630 47.8 KB

(and the error list goes on…)

2

Hey @gcamaralooplex

What kind of jobs are you running?

it’s possible that the idle-in-transaction timeout is set too low on your PostgreSQL database. You can check the database settings to see if this is the case and adjust the timeout if necessary

3

I’ve run into this issue also.
The culprit usually is a call-out to some external system.
For example an API call to a service, sending email but the mail server is not reachable, etc.
It could also be that you have some service that is taking time to complete.

The way Flowable works, is that it creates a database transaction at the start of execution, it keeps the transaction open while it executes any number of steps in your flow until it hits an async or a user task or an end event. Then it commits the transaction. So while Flowable is not actively using the database, the connection will is idle in transaction while all the other processing is happening on the Flowable.side.