Deadlock While completing Task

Devakumar_Jayaraman · May 27, 2016, 10:27am

Hi,

I am facing deadlock when completing tasks of different process instances simultaneously. Is there any special configuration required for deletion of records??

27-05-16 17:46:07.391 [executorService-5] ERROR org.camunda.bpm.engine.context - ENGINE-16004 Exception while closing command context: ENGINE-03004 Exception while executing Database Operation ‘DELETE ExecutionEntity[c87d9cbd-23ef-11e6-b304-0050569b4c18]’ with message ’

Error updating database. Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

The error may involve org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.deleteExecution-Inline

The error occurred while setting parameters

SQL: delete from ACT_RU_EXECUTION where ID_ = ? and REV_ = ?

Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

'. Flush summary:
[
INSERT ExecutionEntity[d175c174-23ef-11e6-b304-0050569b4c18]
INSERT ByteArrayEntity[d0c2ec2e-23ef-11e6-b304-0050569b4c18]
INSERT ByteArrayEntity[d0c2ec30-23ef-11e6-b304-0050569b4c18]
INSERT ByteArrayEntity[d175c172-23ef-11e6-b304-0050569b4c18]
INSERT ByteArrayEntity[d175c173-23ef-11e6-b304-0050569b4c18]
INSERT VariableInstanceEntity[d0c2ec2f-23ef-11e6-b304-0050569b4c18]
INSERT VariableInstanceEntity[d0c2ec31-23ef-11e6-b304-0050569b4c18]
INSERT VariableInstanceEntity[d175c176-23ef-11e6-b304-0050569b4c18]
DELETE VariableInstanceEntity[c87d9cbf-23ef-11e6-b304-0050569b4c18]
DELETE VariableInstanceEntity[c87d9cc0-23ef-11e6-b304-0050569b4c18]
DELETE_BULK deleteByteArrayNoRevisionCheck d0c2ec30-23ef-11e6-b304-0050569b4c18
DELETE_BULK deleteByteArrayNoRevisionCheck d175c172-23ef-11e6-b304-0050569b4c18
UPDATE ExecutionEntity[c87d9cb5-23ef-11e6-b304-0050569b4c18]
DELETE ExecutionEntity[c87d9cbd-23ef-11e6-b304-0050569b4c18]
]
org.camunda.bpm.engine.ProcessEngineException: ENGINE-03004 Exception while executing Database Operation ‘DELETE ExecutionEntity[c87d9cbd-23ef-11e6-b304-0050569b4c18]’ with message ’

Error updating database. Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

The error may involve org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.deleteExecution-Inline

The error occurred while setting parameters

SQL: delete from ACT_RU_EXECUTION where ID_ = ? and REV_ = ?

Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

'. Flush summary:
[
INSERT ExecutionEntity[d175c174-23ef-11e6-b304-0050569b4c18]
INSERT ByteArrayEntity[d0c2ec2e-23ef-11e6-b304-0050569b4c18]
INSERT ByteArrayEntity[d0c2ec30-23ef-11e6-b304-0050569b4c18]
INSERT ByteArrayEntity[d175c172-23ef-11e6-b304-0050569b4c18]
INSERT ByteArrayEntity[d175c173-23ef-11e6-b304-0050569b4c18]
INSERT VariableInstanceEntity[d0c2ec2f-23ef-11e6-b304-0050569b4c18]
INSERT VariableInstanceEntity[d0c2ec31-23ef-11e6-b304-0050569b4c18]
INSERT VariableInstanceEntity[d175c176-23ef-11e6-b304-0050569b4c18]
DELETE VariableInstanceEntity[c87d9cbf-23ef-11e6-b304-0050569b4c18]
DELETE VariableInstanceEntity[c87d9cc0-23ef-11e6-b304-0050569b4c18]
DELETE_BULK deleteByteArrayNoRevisionCheck d0c2ec30-23ef-11e6-b304-0050569b4c18
DELETE_BULK deleteByteArrayNoRevisionCheck d175c172-23ef-11e6-b304-0050569b4c18
UPDATE ExecutionEntity[c87d9cb5-23ef-11e6-b304-0050569b4c18]
DELETE ExecutionEntity[c87d9cbd-23ef-11e6-b304-0050569b4c18]
]
at org.camunda.bpm.engine.impl.db.EnginePersistenceLogger.flushDbOperationException(EnginePersistenceLogger.java:113) ~[camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:296) ~[camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flush(DbEntityManager.java:282) ~[camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:315) ~[camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:243) ~[camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:104) [camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42) [camunda-engine-spring-7.4.0.jar:7.4.0]
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40) [camunda-engine-spring-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) [camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.RuntimeServiceImpl.signal(RuntimeServiceImpl.java:266) [camunda-engine-7.4.0.jar:7.4.0]
at com.phoenix.workflow.service.impl.ApmProcessServiceImpl.completeTaskSync(ApmProcessServiceImpl.java:96) [classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_51]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_51]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_51]
at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_51]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at com.sun.proxy.$Proxy71.completeTaskSync(Unknown Source) [na:na]
at com.phoenix.workflow.service.impl.ApmProcessServiceImpl.completeTask(ApmProcessServiceImpl.java:83) [classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_51]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_51]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_51]
at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_51]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at com.sun.proxy.$Proxy71.completeTask(Unknown Source) [na:na]
at com.phoenix.workflow.service.impl.ApmIntegrationServiceImpl.processAdapterResponse(ApmIntegrationServiceImpl.java:116) [classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_51]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_51]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_51]
at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_51]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [spring-tx-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) [spring-aop-4.2.3.RELEASE.jar:4.2.3.RELEASE]
at com.sun.proxy.$Proxy72.processAdapterResponse(Unknown Source) [na:na]
at com.phoenix.workflow.integration.AdapterMessageConsumer$2.call(AdapterMessageConsumer.java:107) [classes/:na]
at com.phoenix.workflow.integration.AdapterMessageConsumer$2.call(AdapterMessageConsumer.java:1) [classes/:na]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_51]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_51]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_51]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_51]
Caused by: org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

The error may involve org.camunda.bpm.engine.impl.persistence.entity.ExecutionEntity.deleteExecution-Inline

The error occurred while setting parameters

SQL: delete from ACT_RU_EXECUTION where ID_ = ? and REV_ = ?

Cause: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:154) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:165) ~[mybatis-3.2.8.jar:3.2.8]
at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeDelete(DbSqlSession.java:187) ~[camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.deleteEntity(DbSqlSession.java:172) ~[camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.db.AbstractPersistenceSession.executeDbOperation(AbstractPersistenceSession.java:45) ~[camunda-engine-7.4.0.jar:7.4.0]
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperationManager(DbEntityManager.java:293) ~[camunda-engine-7.4.0.jar:7.4.0]
... 57 common frames omitted

Caused by: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374) ~[ojdbc6-6.0.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:911) ~[c3p0-0.9.0.4.jar:0.9.0.4]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71) ~[mybatis-3.2.8.jar:3.2.8]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152) ~[mybatis-3.2.8.jar:3.2.8]
... 62 common frames omitted

thorben · May 27, 2016, 11:17am

Are you on Camunda 7.4.0?
If that is the case, you are likely having this problem: https://app.camunda.com/jira/browse/CAM-5440
Add the index mentioned in the ticket to resolve it.

Cheers,
Thorben

hawky4s · May 27, 2016, 11:28am

Yes, he is on 7.4.0: [quote=“Devakumar_Jayaraman, post:1, topic:693”]
at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.executeDelete(DbSqlSession.java:187) ~[camunda-engine-7.4.0.jar:7.4.0]
[/quote]
The correct file for the index.

Cheers,
Christian

Devakumar_Jayaraman · May 30, 2016, 10:58am

Hi Thorben,

Thank you so much for your support.

After adding the Index, the deadlock issue is gone now. Please help to provide more information about this index. Because i am not seeing any entry in that table. Still how this index is fixing the issue??

thorben · May 30, 2016, 11:22am

Hi @Devakumar_Jayaraman,

The table ACT_EXT_TASK has a foreign key reference to ACT_RU_EXECUTION. Now, when an execution is deleted, the database must ensure that the foreing key constraint is not violated, i.e. it must check if there are corresponding entries in ACT_EXT_TASK. Without the index, Oracle locks the entire ACT_EXT_TASK table in this case, regardless whether it contains data or not. This becomes a problem when multiple ACT_RU_EXECUTION entries are deleted in parallel.

Cheers,
Thorben

Devakumar_Jayaraman · May 30, 2016, 3:11pm

Thanks thorben… got it…