Lock Wait timeout Exception with MYSQL and MariaDB

[sushantmahajan]

Hi,

I am facing the below issue  quite a lot specifically on MYSQL and MariaDB. After such an error is seen, the jobs remain stuck and do not process any further.

### Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve org.activiti.engine.impl.persistence.entity.HistoricActivityInstanceEntity.insertHistoricActivityInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_HI_ACTINST (         ID_,         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_,         TENANT_ID_       ) values (         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?       )
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve org.activiti.engine.impl.persistence.entity.HistoricActivityInstanceEntity.insertHistoricActivityInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_HI_ACTINST (         ID_,         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_,         TENANT_ID_       ) values (         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?       )
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137)
   at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:755)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:587)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:211)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:137)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:66)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:47)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:45)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
   at org.activiti.engine.impl.jobexecutor.ExecuteJobsRunnable.run(ExecuteJobsRunnable.java:48)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
   at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
   at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:149)
   at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
   at org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:264)
   at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:288)
   at org.mariadb.jdbc.MySQLPreparedStatement.execute(MySQLPreparedStatement.java:141)
   at com.zaxxer.hikari.proxy.PreparedStatementProxy.execute(PreparedStatementProxy.java:44)
   at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.execute(PreparedStatementJavassistProxy.java)
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
   at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
   at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)
   at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
   … 16 more
Caused by: org.mariadb.jdbc.internal.common.QueryException: Lock wait timeout exceeded; try restarting transaction
   at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:942)
   at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:991)
   at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:281)
   … 25 more
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[vasile_dirla]

Hi,
could you provide some more details?
Activiti version, database version, and other configurations.

Would be great if you could provide a jUnit which shows this, because will be much easier to reproduce and fix the issue.
Thanks.

[sushantmahajan]

Hi Vasile,
Below is the information you are seeking-

Activiti version-5.16.1
Database-MYSQL 5.1.73
Core pool Size-5
Max pool size -5
History  level-Audit(Default)

The issue is randomly seen in the application logs. There is no specific case to reproduce the issue.

[jbarrez]

a lock timeout on an insert is very odd …. can you try using a connection pool (c3p0, bonecp, etc) instead of the default Mybatis connection pooling?