Camunda Clean Up Issue

Camunda 7 Topics Discussion & Questions
1

Hi,

Currently we are trying to leverage the camunda clean up since the historical records are taking up space in our Database.

Camunda Version: 7.10

Our configs:
Cleanup Window: 00:01 - 23:59
Cleanup Strategy: 23:59
Cleanup BatchSize: 100
CleanupDegreeOfParallelism: 1
isolation_level:read_commit

16021198119917001070460356045724
160211981199170010704603560457241512×1512 710 KB

We already tried increasing and decreasing the batch size and parallelism value but we keep on encountering ‘lock wait exceeded’ error.

We also tried increasing the inno_lock_wait_timeout value to 100 and 500.

Also tried increasing the lock-time-in-millis and wait-time-in-millis for the job-executor.

What could be possibly the reason why it fails?

Sample Error Snippet
"JobExecutor[org.camunda.bpm.engine.spring.components.jobexecutor.SpringJobExecutor]] ERROR org.camunda.bpm.engine.jobexecutor - ENGINE-14019 Exception during job acquisition ENGINE-03083 Exception while executing Batch Database Operations with message ’

Error flushing statements. Cause: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.JobEntity.updateEverLivingJob (batch index #1) failed. Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

Cause: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.JobEntity.updateEverLivingJob (batch index #1) failed. Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
'. Flush summary:
[
UPDATE EverLivingJobEntity[c591fa49-08a0-11eb-b3fa-023a0a2cac82]
]
org.camunda.bpm.engine.ProcessEngineException: ENGINE-03083 Exception while executing Batch Database Operations with message ’

Error flushing statements. Cause: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.JobEntity.updateEverLivingJob (batch index #1) failed. Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

Cause: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.JobEntity.updateEverLivingJob (batch index #1) failed. Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
'. Flush summary:
[
UPDATE EverLivingJobEntity[c591fa49-08a0-11eb-b3fa-023a0a2cac82]
]
at org.camunda.bpm.engine.impl.db.EnginePersistenceLogger.flushDbOperationsException(EnginePersistenceLogger.java:692)
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperations(DbEntityManager.java:361)
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:324)
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flush(DbEntityManager.java:296)
at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:207)
at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:136)
at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:115)
at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:45)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:43)
at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:69)
at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
at org.camunda.bpm.engine.impl.jobexecutor.SequentialJobAcquisitionRunnable.acquireJobs(SequentialJobAcquisitionRunnable.java:157)
at org.camunda.bpm.engine.impl.jobexecutor.SequentialJobAcquisitionRunnable.run(SequentialJobAcquisitionRunnable.java:75)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.apache.ibatis.exceptions.PersistenceException:

Error flushing statements. Cause: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.JobEntity.updateEverLivingJob (batch index #1) failed. Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

Cause: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.JobEntity.updateEverLivingJob (batch index #1) failed. Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:255)
at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.flushOperations(DbSqlSession.java:90)
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperations(DbEntityManager.java:356)
... 13 common frames omitted

Caused by: org.apache.ibatis.executor.BatchExecutorException: org.camunda.bpm.engine.impl.persistence.entity.JobEntity.updateEverLivingJob (batch index #1) failed. Cause: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:146)
at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:129)
at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:122)
at org.apache.ibatis.executor.CachingExecutor.flushStatements(CachingExecutor.java:114)
at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:253)
… 15 common frames omitted
Caused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
at jdk.internal.reflect.GeneratedConstructorAccessor187.newInstance(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at com.mysql.cj.util.Util.handleNewInstance(Util.java:192)
at com.mysql.cj.util.Util.getInstance(Util.java:167)
at com.mysql.cj.util.Util.getInstance(Util.java:174)
at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:853)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:435)
at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796)
at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
at jdk.internal.reflect.GeneratedMethodAccessor289.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:78)
at com.sun.proxy.$Proxy185.executeBatch(Unknown Source)
at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:122)
… 19 common frames omitted
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)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:832)
… 29 common frames omitted"

2

@hazel_cruzat try to set the below value.

Run this:

SET GLOBAL innodb_lock_wait_timeout = 5000;

And then this:

SET innodb_lock_wait_timeout = 5000;

Check InnoDB status for locks

SHOW ENGINE InnoDB STATUS;

Check MySQL open tables

SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions

SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;

Check lock dependency - what blocks what

SELECT * FROM `information_schema`.`innodb_locks`;

Some other thread is holding a record lock on some record for too long, and your thread is being timed out. After investigating the results above, you should be able to see what is locking what.

Also check the MySQL driver compatibility with the database version which is being used.

3

Upon checking the previous clean up call blocks the next one.

If thats the case it means the 1st clean up call takes time to complete before allowing to call another one.

Is there a workaround for this?