Flowable Table Locking issue

Hi Team
Getting below error.
org.flowable.engine.impl.persistence.entity.HistoricActivityInstanceEntityImpl.insertHistoricActivityInstance-Inline

org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: java.sql.SQLTransientConnectionException: (conn=1860579) 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: java.sql.SQLTransientConnectionException: (conn=1860579) Lock wait timeout exceeded; try restarting transaction

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
    at org.flowable.common.engine.impl.db.DbSqlSession.flushRegularInsert(DbSqlSession.java:509)
    at org.flowable.common.engine.impl.db.DbSqlSession.flushInsertEntities(DbSqlSession.java:493)
    at org.flowable.common.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:473)
    at org.flowable.common.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:358)
    at org.flowable.common.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:192)
    at org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:61)
    at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:81)
    at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:51)
    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)
    at org.flowable.engine.impl.RuntimeServiceImpl.startProcessInstance(RuntimeServiceImpl.java:732)
    at org.flowable.engine.impl.runtime.ProcessInstanceBuilderImpl.start(ProcessInstanceBuilderImpl.java:205)
    at com.techm.bm.om.orderdecompositionservice.services.WorkflowService.startProcess(WorkflowService.java:134)
    at com.techm.bm.om.orderdecompositionservice.consumer.ServiceOrderCreatedEventHandler.on(ServiceOrderCreatedEventHandler.java:86)
    at sun.reflect.GeneratedMethodAccessor830.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.axonframework.messaging.annotation.AnnotatedMessageHandlingMember.handle(AnnotatedMessageHandlingMember.java:132)
    at org.axonframework.eventhandling.AnnotationEventHandlerAdapter.handle(AnnotationEventHandlerAdapter.java:91)
    at org.axonframework.eventhandling.SimpleEventHandlerInvoker.handle(SimpleEventHandlerInvoker.java:108)
    at org.axonframework.eventhandling.MultiEventHandlerInvoker.handle(MultiEventHandlerInvoker.java:89)
    at org.axonframework.eventhandling.AbstractEventProcessor.lambda$null$1(AbstractEventProcessor.java:165)
    at org.axonframework.messaging.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:57)
    at org.axonframework.messaging.interceptors.CorrelationDataInterceptor.handle(CorrelationDataInterceptor.java:65)
    at org.axonframework.messaging.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:55)
    at org.axonframework.eventhandling.TrackingEventProcessor.lambda$new$1(TrackingEventProcessor.java:181)
    at org.axonframework.messaging.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:55)
    at org.axonframework.eventhandling.AbstractEventProcessor.lambda$processInUnitOfWork$2(AbstractEventProcessor.java:173)
    at org.axonframework.messaging.unitofwork.BatchingUnitOfWork.executeWithResult(BatchingUnitOfWork.java:86)
    at org.axonframework.eventhandling.AbstractEventProcessor.processInUnitOfWork(AbstractEventProcessor.java:159)
    at org.axonframework.eventhandling.TrackingEventProcessor.processBatch(TrackingEventProcessor.java:438)
    at org.axonframework.eventhandling.TrackingEventProcessor.processingLoop(TrackingEventProcessor.java:301)
    at org.axonframework.eventhandling.TrackingEventProcessor$TrackingSegmentWorker.run(TrackingEventProcessor.java:1161)
    at org.axonframework.eventhandling.TrackingEventProcessor$WorkerLauncher.run(TrackingEventProcessor.java:1276)
    at java.lang.Thread.run(Thread.java:748)

Caused by: java.sql.SQLTransientConnectionException: (conn=1860579) Lock wait timeout exceeded; try restarting transaction
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:273)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229)
at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
… 37 common frames omitted
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Lock wait timeout exceeded; try restarting transaction
at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220)
… 46 common frames omitted
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1674)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1536)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1499)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318)
… 47 common frames omitted

2021-07-19 10:23:11.769 ERROR 7 — [tion-process]-0] .b.o.o.c.ServiceOrderCreatedEventHandler :

Error updating database. Cause: java.sql.SQLTransientConnectionException: (conn=1860579) 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: java.sql.SQLTransientConnectionException: (conn=1860579) Lock wait timeout exceeded; try restarting transaction

org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: java.sql.SQLTransientConnectionException: (conn=1860579) 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: java.sql.SQLTransientConnectionException: (conn=1860579) Lock wait timeout exceeded; try restarting transaction

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
    at org.flowable.common.engine.impl.db.DbSqlSession.flushRegularInsert(DbSqlSession.java:509)
    at org.flowable.common.engine.impl.db.DbSqlSession.flushInsertEntities(DbSqlSession.java:493)
    at org.flowable.common.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:473)
    at org.flowable.common.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:358)
    at org.flowable.common.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:192)
    at org.flowable.common.engine.impl.interceptor.CommandContext.close(CommandContext.java:61)
    at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:81)
    at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:51)
    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)
    at org.flowable.engine.impl.RuntimeServiceImpl.startProcessInstance(RuntimeServiceImpl.java:732)
    at org.flowable.engine.impl.runtime.ProcessInstanceBuilderImpl.start(ProcessInstanceBuilderImpl.java:205)
    at com.techm.bm.om.orderdecompositionservice.services.WorkflowService.startProcess(WorkflowService.java:134)
    at com.techm.bm.om.orderdecompositionservice.consumer.ServiceOrderCreatedEventHandler.on(ServiceOrderCreatedEventHandler.java:86)
    at sun.reflect.GeneratedMethodAccessor830.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.axonframework.messaging.annotation.AnnotatedMessageHandlingMember.handle(AnnotatedMessageHandlingMember.java:132)
    at org.axonframework.eventhandling.AnnotationEventHandlerAdapter.handle(AnnotationEventHandlerAdapter.java:91)
    at org.axonframework.eventhandling.SimpleEventHandlerInvoker.handle(SimpleEventHandlerInvoker.java:108)
    at org.axonframework.eventhandling.MultiEventHandlerInvoker.handle(MultiEventHandlerInvoker.java:89)
    at org.axonframework.eventhandling.AbstractEventProcessor.lambda$null$1(AbstractEventProcessor.java:165)
    at org.axonframework.messaging.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:57)
    at org.axonframework.messaging.interceptors.CorrelationDataInterceptor.handle(CorrelationDataInterceptor.java:65)
    at org.axonframework.messaging.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:55)
    at org.axonframework.eventhandling.TrackingEventProcessor.lambda$new$1(TrackingEventProcessor.java:181)
    at org.axonframework.messaging.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:55)
    at org.axonframework.eventhandling.AbstractEventProcessor.lambda$processInUnitOfWork$2(AbstractEventProcessor.java:173)
    at org.axonframework.messaging.unitofwork.BatchingUnitOfWork.executeWithResult(BatchingUnitOfWork.java:86)
    at org.axonframework.eventhandling.AbstractEventProcessor.processInUnitOfWork(AbstractEventProcessor.java:159)
    at org.axonframework.eventhandling.TrackingEventProcessor.processBatch(TrackingEventProcessor.java:438)
    at org.axonframework.eventhandling.TrackingEventProcessor.processingLoop(TrackingEventProcessor.java:301)
    at org.axonframework.eventhandling.TrackingEventProcessor$TrackingSegmentWorker.run(TrackingEventProcessor.java:1161)
    at org.axonframework.eventhandling.TrackingEventProcessor$WorkerLauncher.run(TrackingEventProcessor.java:1276)
    at java.lang.Thread.run(Thread.java:748)

Caused by: java.sql.SQLTransientConnectionException: (conn=1860579) Lock wait timeout exceeded; try restarting transaction
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:273)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229)
at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
… 37 common frames omitted
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Lock wait timeout exceeded; try restarting transaction
at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220)
… 46 common frames omitted
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1674)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1536)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1499)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318)
… 47 common frames omitted

2021-07-19 10:23:12.315 WARN 7 — [tion-process]-0] o.a.e.TrackingEventProcessor : Error occurred. Starting retry mode.

javax.management.RuntimeErrorException:

Error updating database. Cause: java.sql.SQLTransientConnectionException: (conn=1860579) 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: java.sql.SQLTransientConnectionException: (conn=1860579) Lock wait timeout exceeded; try restarting transaction

    at com.techm.bm.om.orderdecompositionservice.consumer.ServiceOrderCreatedEventHandler.on(ServiceOrderCreatedEventHandler.java:90)

This typically means the database can’t handle the load it needs to handle: a lock was taken but the work wasn’t completed in the timeout period.

Can you share more about which database / it’s setup? And how much concurrency / type of load you have?

@joram thanks for reply. we are using MariaDb and currently having 10 concurrent user but facing this lock issue sometime.
please suggest someone to resolve this issue,

With 10 users, you should hardly be giving the database a sweat. How is your database setup (hardware-wise), did you configure your connection pool settings, any config that might help us?