Nesting transactions inside engine = problems

Flowable Engine
1

Hello,
I’m using Flowable 6.6.0 and during high load test I discovered that Flowable creating nested transaction which produces locks on connection pool. Imagine situation when 1 connection left in the pool and during procesing Flowable gets this 1 connection and tries get another one inside (nesting) so there is a logical lock, we have to wait for timeout (maxWaitForConnection) on connection pool. How to avoid that, if this is expected behaviour(normal situation) or maybe there is algorytm which can help us set right pool size like = executors_number * 2 + 4

StackTraces below

FIRST)
08:46:07,743 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:139)
08:46:07,743 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:61)
08:46:07,743 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.apache.ibatis.session.defaults.DefaultSqlSession.getConnection(DefaultSqlSession.java:297)
08:46:07,743 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.db.DbSqlSessionFactory.openSession(DbSqlSessionFactory.java:96)
08:46:07,743 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContext.getSession(CommandContext.java:265)
08:46:07,743 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.<init>(StandaloneMybatisTransactionContext.java:48)
08:46:07,743 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContextFactory.openTransactionContext(StandaloneMybatisTransactionContextFactory.java:26)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:47)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:83)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.fireTransactionEvent(StandaloneMybatisTransactionContext.java:99)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.commit(StandaloneMybatisTransactionContext.java:73)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.TransactionCommandContextCloseListener.afterSessionsFlush(TransactionCommandContextCloseListener.java:36)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContext.executeCloseListenersAfterSessionFlushed(CommandContext.java:177)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:78)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:92)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
08:46:07,744 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
08:4607,745 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51)
08:46:07,745 ERROR [stderr] (flowable-bpmn-acquire-timer-jobs) 	at deployment.server-bpm-product.ear//org.flowable.job.service.impl.asyncexecutor.AcquireTimerJobsRunnable.run(AcquireTimerJobsRunnable.java:66)


SECOND)

08:47:21,000 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:139)
08:47:21,000 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:61)
08:47:21,000 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.apache.ibatis.session.defaults.DefaultSqlSession.getConnection(DefaultSqlSession.java:297)
08:47:21,000 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.db.DbSqlSessionFactory.openSession(DbSqlSessionFactory.java:96)
08:47:21,000 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContext.getSession(CommandContext.java:265)
08:47:21,000 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.<init>(StandaloneMybatisTransactionContext.java:48)
08:47:21,000 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContextFactory.openTransactionContext(StandaloneMybatisTransactionContextFactory.java:26)
08:47:21,000 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:47)
08:47:21,000 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:83)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.fireTransactionEvent(StandaloneMybatisTransactionContext.java:99)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.commit(StandaloneMybatisTransactionContext.java:73)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.TransactionCommandContextCloseListener.afterSessionsFlush(TransactionCommandContextCloseListener.java:36)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContext.executeCloseListenersAfterSessionFlushed(CommandContext.java:177)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:78)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:92)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.executeJob(ExecuteAsyncRunnable.java:127)
08:47:21,001 ERROR [stderr] (flowable-async-job-executor-thread-40) at deployment.server-bpm-product.ear//org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:115)
2

Next example

   at org.flowable.common.engine.impl.db.DbSqlSessionFactory.openSession(DbSqlSessionFactory.java:96)
    at org.flowable.common.engine.impl.interceptor.CommandContext.getSession(CommandContext.java:265)
    at org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.<init>(StandaloneMybatisTransactionContext.java:48)
    at org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContextFactory.openTransactionContext(StandaloneMybatisTransactionContextFactory.java:26)
    at org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:47)
    at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:83)
    at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
    at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
    at org.flowable.job.service.impl.asyncexecutor.DefaultAsyncJobExecutor.unacquireJobAfterRejection(DefaultAsyncJobExecutor.java:100)
    at org.flowable.job.service.impl.asyncexecutor.DefaultAsyncJobExecutor.executeAsyncJob(DefaultAsyncJobExecutor.java:84)
    at org.flowable.job.service.impl.asyncexecutor.AbstractAsyncExecutor.executeAsyncJob(AbstractAsyncExecutor.java:84)
    at org.flowable.job.service.impl.asyncexecutor.JobAddedTransactionListener.execute(JobAddedTransactionListener.java:47)
    at org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.executeTransactionListeners(StandaloneMybatisTransactionContext.java:114)
    at org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext$1.execute(StandaloneMybatisTransactionContext.java:102)
    at org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext$1.execute(StandaloneMybatisTransactionContext.java:99)
    at org.flowable.engine.impl.interceptor.CommandInvoker$1.run(CommandInvoker.java:59)
    at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperation(CommandInvoker.java:107)
    at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperations(CommandInvoker.java:81)
    at org.flowable.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:64)
    at org.flowable.engine.impl.interceptor.BpmnOverrideContextInterceptor.execute(BpmnOverrideContextInterceptor.java:26)
    at org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:53)
    at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:83)
    at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
    at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
    at org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.fireTransactionEvent(StandaloneMybatisTransactionContext.java:99)
    at org.flowable.common.engine.impl.cfg.standalone.StandaloneMybatisTransactionContext.commit(StandaloneMybatisTransactionContext.java:73)
    at org.flowable.common.engine.impl.interceptor.TransactionCommandContextCloseListener.afterSessionsFlush(TransactionCommandContextCloseListener.java:36)
    at org.flowable.common.engine.impl.interceptor.CommandContext.executeCloseListenersAfterSessionFlushed(CommandContext.java:177)
    at org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:78)
    at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:92)
    at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
    at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
    at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51)
3

@joram I saw your comment in JobAddedTransactionListener.execute but problem still exists,
To summarize you should call listeners after releasing main connection