Database Deadlock Error When Deleting Deployment

Camunda 7 Topics Discussion & Questions
1

We are trying to delete old Deployments, via the REST API, in a script. When running this script, numerous Deployment delete operations fail. However, I can successfully delete deployments reliably by using the Cockpit and deleting one deployment at a time.

We are running Camunda 7.5, Apache Tomcat, stand-alone. We are using an SQL database running on Amazon RDS.

The script we are running gets a list of Deployments that are older than a specified data, using this API endpoint:

GET /engine-rest/deployment?before=<date>

Then it uses this API endpoint to delete the Deployment:

DELETE /engine-rest/deployment/:deploymentId?cascade=true

On over half of the attempted delete operations we receive the following error:

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: delete     from         ACT_GE_BYTEARRAY      WHERE ID_ in (     select         JOB_EXCEPTION_STACK_ID_     from         ACT_HI_JOB_LOG     WHERE JOB_EXCEPTION_STACK_ID_ is not null                                                      and PROCESS_INSTANCE_ID_ = ?   )
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:154)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:165)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeDelete(DbSqlSession.java:187)
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.deleteBulk(DbSqlSession.java:201)
	at org.camunda.bpm.engine.impl.db.AbstractPersistenceSession.executeDbOperation(AbstractPersistenceSession.java:48)
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:294)
	... 44 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at sun.reflect.GeneratedConstructorAccessor152.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1065)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
	... 49 more

We also see instances of this error:

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.VariableInstanceEntity.deleteByteArrayNoRevisionCheck-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_GE_BYTEARRAY where ID_ = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction'. Flush summary: 
 [
  INSERT UserOperationLogEntryEventEntity[f3a07a9d-ad9d-11e6-8a0c-0242ac120003]
...

Do you have any idea what might be the cause of this error? We really need to be able to delete old deployments in a bulk manner, such as via a script.

2

Hi @Stephen_Bucholtz,

I think that tose are not deadlocks, but the OptimisticLockingException’s which are reported as deadlocks in MySQL for some reason. You can read a bit more about it here https://docs.camunda.org/manual/7.5/user-guide/process-engine/transactions-in-processes/#optimistic-locking. Which basically means that multiple delete operations try to cascade on same data set. Which is not a bad thing and only means that you have to retry operation.

Does that help you?
Askar.

3

Thank you. That helps me to understand what is going on. I guess I have to handle this in my script by retrying failed operations, which obviously adds a bit more complexity.

4

Just in case anyone is interested, I discovered the root cause of the locking exceptions.

My javascript that I wrote to delete deployments was using Promises such that a list of deployments was being deleted in parallel. As pointed out by Askar, many of these requests failed due to the Optimistic Locking mechanism. I changed my script so that each deployment in the list is deleted sequentially, each one waiting for the previous deletion to complete. With this change I have completely eliminated all errors.

3 Likes
5