Strange db locks on mysql

[jit]

Hi, It will be a long post but I believe it will be helpfull for both product development and for the people who may encounter this problem in near future.

For months ago, I was having these deadlock problems and decided these deadlocks as optimistic locks and let flowable-engine to retry these transactions and things seem to work proper.

Deadlock found when trying to get lock; try restarting transaction

I am having the exceptions below during the execution of a process and the process terminates after these exceptions. I have changed ISOLATION LEVEL of mysql from REPEATABLE-READ to READ-COMMITTED but I am still having these deadlock exceptions. What else I can do to get rid of from these exceptions. You can check all exceptions from here. https://601062a6dfa9f.htmlsave.net/ | ### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found w…

But this time we are having “Lock wait timeout exceeded; try restarting transaction” exceptions and they are really an headache for us and we even started to decide changing our engine from flowable to any other activity bpmn forked engine.

Having these lock waits without any pattern is making things much harder. The only pattern I may say is; these locks generally appearing at HISTORY tables.

Since these History upserts are being done in a seperate execution, I was not expecting these lock exceptions to stop my runtime executions but they are really effecting my runtime executions. Btw I tried both sync-history and async-history features.

The things I tried,
Changing ISOLATION_LEVEL on mysql to READ-COMMITTED
Changing History_Level on flowable from AUDIT to ACTIVITY
Changing History execution mode on flowable from sync to async.
and none of them worked.

We also have an Oracle DB but we are not facing with these kind of issues on that DB.

I am also sharing some SQLs regarding to these locks and the first one is really interesting because we got this exception when we tried to start a new process instance. Is there a table level lock on flowable queries or something like for update statements? We are using version 6.5.0

2021-Nov-08 01:09:55 AM ERROR [ScheduledListener2] (CommandContext.java:115) - Error while closing command context
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may exist in org/flowable/db/mapping/entity/HistoricActivityInstance.xml
### The error may involve org.flowable.engine.impl.persistence.entity.HistoricActivityInstanceEntityImpl.insertHistoricActivityInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_HI_ACTINST (         ID_,         REV_,         PROC_DEF_ID_,         PROC_INST_ID_,         EXECUTION_ID_,         ACT_ID_,         TASK_ID_,         CALL_PROC_INST_ID_,         ACT_NAME_,         ACT_TYPE_,         ASSIGNEE_,         START_TIME_,         END_TIME_,         DURATION_,         DELETE_REASON_,         TENANT_ID_       ) values (         ?,         1,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?       )
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.1.jar:3.5.1]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushRegularInsert(DbSqlSession.java:442) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushInsertEntities(DbSqlSession.java:426) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:406) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:291) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:191) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:61) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:81) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.spring.SpringTransactionInterceptor.lambda$execute$0(SpringTransactionInterceptor.java:56) ~[flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) [spring-tx-5.1.7.RELEASE.jar:5.1.7.RELEASE]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:56) [flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) [flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) [flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51) [flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at **org.flowable.engine.impl.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:119) [flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]**
	at com.mycompany.executorservice.service.ProcessStarterService.startProcess(ProcessStarterService.java:102) [classes/:0.0.1-SNAPSHOT]
	at com.mycompany.executorservice.WorkerWatchDog.startProcess(WorkerWatchDog.java:743) [classes/:0.0.1-SNAPSHOT]
	at com.mycompany.executorservice.WorkerWatchDog.startAwaitingProcesses(WorkerWatchDog.java:676) [classes/:0.0.1-SNAPSHOT]
	at com.mycompany.executorservice.WorkerWatchDog.handleWorkerDedicatedScheduledProcess(WorkerWatchDog.java:508) [classes/:0.0.1-SNAPSHOT]
	at com.mycompany.executorservice.WorkerWatchDog.checkIfThereIsAwaitingProcess(WorkerWatchDog.java:381) [classes/:0.0.1-SNAPSHOT]
	at sun.reflect.GeneratedMethodAccessor1784.invoke(Unknown Source) ~[?:?]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_221]
	at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_221]
	at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84) [spring-context-5.1.7.RELEASE.jar:5.1.7.RELEASE]
	at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) [spring-context-5.1.7.RELEASE.jar:5.1.7.RELEASE]
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:1.8.0_221]
	at java.util.concurrent.FutureTask.runAndReset(Unknown Source) [?:1.8.0_221]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source) [?:1.8.0_221]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) [?:1.8.0_221]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_221]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_221]
	at java.lang.Thread.run(Unknown Source) [?:1.8.0_221]
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:372) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-3.2.0.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:?]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.1.jar:3.5.1]
	... 32 more

2021-Nov-08 01:06:32 AM ERROR [flowable-async-job-executor-thread-14] (CommandContext.java:115) - Error while closing command context
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may exist in org/flowable/db/mapping/entity/HistoricProcessInstance.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: delete from ACT_HI_PROCINST      WHERE  END_TIME_ <= ?                                     and END_TIME_ is not NULL
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:212) ~[mybatis-3.5.1.jar:3.5.1]
	at org.flowable.common.engine.impl.db.BulkDeleteOperation.execute(BulkDeleteOperation.java:34) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushBulkDeletes(DbSqlSession.java:553) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:529) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:293) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:191) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:61) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:81) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.spring.SpringTransactionInterceptor.lambda$execute$0(SpringTransactionInterceptor.java:56) ~[flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.1.7.RELEASE.jar:5.1.7.RELEASE]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:56) ~[flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.HistoricProcessInstanceQueryImpl.deleteWithRelatedData(HistoricProcessInstanceQueryImpl.java:949) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.jobexecutor.BpmnHistoryCleanupJobHandler.execute(BpmnHistoryCleanupJobHandler.java:35) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.DefaultJobManager.executeJobHandler(DefaultJobManager.java:405) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.DefaultJobManager.executeTimerJob(DefaultJobManager.java:370) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.DefaultJobManager.execute(DefaultJobManager.java:230) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.cmd.ExecuteAsyncJobCmd.execute(ExecuteAsyncJobCmd.java:79) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable$2.execute(ExecuteAsyncRunnable.java:131) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable$2.execute(ExecuteAsyncRunnable.java:128) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.CommandInvoker$1.run(CommandInvoker.java:51) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperation(CommandInvoker.java:93) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperations(CommandInvoker.java:72) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:56) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.BpmnOverrideContextInterceptor.execute(BpmnOverrideContextInterceptor.java:25) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:53) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:72) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.spring.SpringTransactionInterceptor.lambda$execute$0(SpringTransactionInterceptor.java:56) ~[flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) [spring-tx-5.1.7.RELEASE.jar:5.1.7.RELEASE]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:56) [flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) [flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) [flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51) [flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.executeJob(ExecuteAsyncRunnable.java:128) [flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:116) [flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_221]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_221]
	at java.lang.Thread.run(Unknown Source) [?:1.8.0_221]
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	
	... 39 more

2021-Nov-05 01:06:53 AM ERROR [flowable-async-job-executor-thread-22] (MyCompanyExecutionExceptionHandler.java:62) - Job 65610ad7-3dbb-11ec-ac03-005056907c90 failed|
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may exist in org/flowable/db/mapping/entity/HistoricActivityInstance.xml
### The error may involve org.flowable.engine.impl.persistence.entity.HistoricActivityInstanceEntityImpl.bulkDeleteHistoricActivityInstancesForNonExistingProcessInstances-Inline
### The error occurred while setting parameters
### SQL: delete   ACTINST   from ACT_HI_ACTINST ACTINST where ACTINST.PROC_INST_ID_ is not null and ACTINST.PROC_INST_ID_ != '' and       NOT EXISTS (select PROCINST.ID_ from ACT_HI_PROCINST PROCINST where ACTINST.PROC_INST_ID_ = PROCINST.ID_)
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:212) ~[mybatis-3.5.1.jar:3.5.1]
	at org.flowable.common.engine.impl.db.BulkDeleteOperation.execute(BulkDeleteOperation.java:34) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushBulkDeletes(DbSqlSession.java:553) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:529) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:293) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:191) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:61) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:81) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.spring.SpringTransactionInterceptor.lambda$execute$0(SpringTransactionInterceptor.java:56) ~[flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.1.7.RELEASE.jar:5.1.7.RELEASE]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:56) ~[flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.HistoricProcessInstanceQueryImpl.deleteWithRelatedData(HistoricProcessInstanceQueryImpl.java:950) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.jobexecutor.BpmnHistoryCleanupJobHandler.execute(BpmnHistoryCleanupJobHandler.java:35) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.DefaultJobManager.executeJobHandler(DefaultJobManager.java:405) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.DefaultJobManager.executeTimerJob(DefaultJobManager.java:370) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.DefaultJobManager.execute(DefaultJobManager.java:230) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.cmd.ExecuteAsyncJobCmd.execute(ExecuteAsyncJobCmd.java:79) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable$2.execute(ExecuteAsyncRunnable.java:131) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable$2.execute(ExecuteAsyncRunnable.java:128) ~[flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.CommandInvoker$1.run(CommandInvoker.java:51) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperation(CommandInvoker.java:93) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperations(CommandInvoker.java:72) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:56) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.engine.impl.interceptor.BpmnOverrideContextInterceptor.execute(BpmnOverrideContextInterceptor.java:25) ~[flowable-engine-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:53) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:72) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.spring.SpringTransactionInterceptor.lambda$execute$0(SpringTransactionInterceptor.java:56) ~[flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.1.7.RELEASE.jar:5.1.7.RELEASE]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:56) ~[flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.executeJob(ExecuteAsyncRunnable.java:128) [flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:116) [flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_252]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_252]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_252]
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-3.3.1.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-3.3.1.jar:?]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.1.jar:3.5.1]
	... 39 more

2021-Nov-05 01:08:02 AM ERROR [flowable-async-job-executor-thread-25] (CommandContext.java:115) - Error while closing command context
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may exist in org/flowable/db/mapping/entity/HistoricActivityInstance.xml
### The error may involve org.flowable.engine.impl.persistence.entity.HistoricActivityInstanceEntityImpl.insertHistoricActivityInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_HI_ACTINST (         ID_,         REV_,         PROC_DEF_ID_,         PROC_INST_ID_,         EXECUTION_ID_,         ACT_ID_,         TASK_ID_,         CALL_PROC_INST_ID_,         ACT_NAME_,         ACT_TYPE_,         ASSIGNEE_,         START_TIME_,         END_TIME_,         DURATION_,         DELETE_REASON_,         TENANT_ID_       ) values (         ?,         1,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?       )
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.1.jar:3.5.1]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushRegularInsert(DbSqlSession.java:442) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushInsertEntities(DbSqlSession.java:426) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:406) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:291) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:191) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:61) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:81) ~[flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.spring.SpringTransactionInterceptor.lambda$execute$0(SpringTransactionInterceptor.java:56) ~[flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) [spring-tx-5.1.7.RELEASE.jar:5.1.7.RELEASE]
	at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:56) [flowable-spring-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) [flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56) [flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51) [flowable-engine-common-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.executeJob(ExecuteAsyncRunnable.java:128) [flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at org.flowable.job.service.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:116) [flowable-job-service-6.5.0-SNAPSHOT.jar:6.5.0-SNAPSHOT]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_252]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_252]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_252]
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.21.jar:8.0.21]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-3.3.1.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-3.3.1.jar:?]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.1.jar:3.5.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.1.jar:3.5.1]
	... 19 more

[Hargundas11]

kill the process which are in deadlock.

[Hargundas11]

SELECT *
FROM sys.dm_exec_sessions;
SELECT *
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

[joram]

@jit

We also have an Oracle DB but we are not facing with these kind of issues on that DB.

That’s something we’ve seen in our benchmarks: when using Oracle (or postgres), things work better than when using MySQL. Typically it’s a matter of not provisioning enough (resource-wise), but even then there are differences in reality.

Flowable does not do any table locks, but MySQL seems to be using table locks itself under heavy load.

What kind of hardware are you running on? What kind of volume (instances / hour)?