deadlocked while deleting records from ACT_RU_EXECUTION with multiple activiti engines running

[jdp]

Hi,

I am getting below mentioned error while my application concurrently executing the TaskServiceImpl.complete command. my application is multithreaded and with multiple activiti engines(3) as well. Can any one help me out here, how can I get rid of this error.


org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.activiti.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: Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the 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.delete(DefaultSqlSession.java:161)
   at org.activiti.engine.impl.db.DbSqlSession$CheckedDeleteOperation.execute(DbSqlSession.java:271)
   at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:646)
   at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:504)
   at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:175)
   at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:122)
   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:131)
   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.TaskServiceImpl.complete(TaskServiceImpl.java:170)
   ———
   at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
   at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:701)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
   at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:634)
   
       ————-
   at org.hornetq.jms.client.JMSMessageListenerWrapper.onMessage(JMSMessageListenerWrapper.java:91)
   at org.hornetq.core.client.impl.ClientConsumerImpl.callOnMessage(ClientConsumerImpl.java:983)
   at org.hornetq.core.client.impl.ClientConsumerImpl.access$400(ClientConsumerImpl.java:48)
   at org.hornetq.core.client.impl.ClientConsumerImpl$Runner.run(ClientConsumerImpl.java:1113)
   at org.hornetq.utils.OrderedExecutorFactory$OrderedExecutor$1.run(OrderedExecutorFactory.java:100)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
   at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
   at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
   at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
   at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
   at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
   at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
   at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:475)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
   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)
   … 42 more

Thanks,
JDP

[jbarrez]

Do you get these exceptions when completing tasks of the same process instance on 3 different nodes. Can you share your process definition so we can understand what is happening?

[jdp]

Hi jbarrez,

Thanks for the reply. Sorry I am replying too late but was stuck with something else.

Now this error throwing frequently when multiple threads are accessing different process instances concurrently, I am getting same type of another error as well in same scenario :
2016-03-22 15:56:37,304 INFO  [STDOUT] org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.deleteTask-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_TASK where ID_ = ? and REV_ = ?
### Cause: java.sql.SQLException: Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Below I am providing some important details for the same :
Please refer my workflow Definition :  https://www.dropbox.com/s/w05i7wg9bxmtvs0/Workflow_Def.png?dl=0
Steps:
1. There are around 50 to100 instances of same process definition are running concurrently and below mentioned all steps runs for multiple process instances in multithreaded environment.
2. Tasks ‘User_task_1’ , ‘System_Task_1’, ‘System_Task_2’ are completed,
3. When system is going to complete ‘User_Task_4’, it calls  ‘System_Task_4’ in same transaction.
4. During execution of ‘System_Task_4’, completion call for ‘User_Task_2’ is reached and trying to complete that task as well. So at this point Activiti throws mentioned exception for few process instances.

- Multiple Activiti engines : yes, Two( 2 )
- Activiti version  : 5.15.1
- DB : MSSQL server
- Required indexes are created in DB
- Transaction configurations : https://www.dropbox.com/s/2ut2jd5et03ub7x/Transaction_Config.png?dl=0

DB calls for one node completion:
INSERT:
——-
[HistoricActivityInstanceEntity[activityId=sid-B63D3B56-4AAD-4B27-9597-0AF6488DB607, activityName=End]]

UPDATE:
——-
[HistoricVariableInstanceEntity[id=891548, name=fId, revision=0, type=integer, longValue=3750535, textValue=3750535], HistoricVariableInstanceEntity[id=891547, name=uId, revision=0, type=integer, longValue=413101, textValue=413101], HistoricVariableInstanceEntity[id=891539, name=docRef, revision=0, type=string, textValue=2_], HistoricVariableInstanceEntity[id=891549, name=status, revision=0, type=integer, longValue=1, textValue=1], HistoricVariableInstanceEntity[id=891538, name=uTId, revision=0, type=integer, longValue=1, textValue=1], HistoricVariableInstanceEntity[id=891540, name=oId, revision=0, type=integer, longValue=413101, textValue=413101], HistoricVariableInstanceEntity[id=891542, name=rId, revision=0, type=integer, longValue=2690043, textValue=2690043], HistoricVariableInstanceEntity[id=891541, name=dId, revision=0, type=integer, longValue=1821114, textValue=1821114], HistoricVariableInstanceEntity[id=891544, name=workflowType, revision=0, type=integer, longValue=1, textValue=1], HistoricVariableInstanceEntity[id=891543, name=proxyuId, revision=0, type=integer, longValue=413101, textValue=413101], HistoricVariableInstanceEntity[id=891546, name=pId, revision=0, type=integer, longValue=157603, textValue=157603], HistoricVariableInstanceEntity[id=891545, name=poi, revision=0, type=integer, longValue=0, textValue=0], HistoricActivityInstanceEntity[activityId=sid-F10DBF33-5A41-4A1B-B0BB-B1B883010FE1, activityName=User_Task_2], HistoricProcessInstanceEntity[superProcessInstanceId=null], org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity@1daae619]

DELETE:
——-
[delete Task[id=891551, name=User_Task_2], delete VariableInstanceEntity[id=891538, name=uTId, type=integer, longValue=1, textValue=1], delete VariableInstanceEntity[id=891539, name=docRef, type=string, textValue=2_], delete VariableInstanceEntity[id=891540, name=oId, type=integer, longValue=413101, textValue=413101], delete VariableInstanceEntity[id=891541, name=dId, type=integer, longValue=1821114, textValue=1821114], delete VariableInstanceEntity[id=891542, name=rId, type=integer, longValue=2690043, textValue=2690043], delete VariableInstanceEntity[id=891543, name=proxyuId, type=integer, longValue=413101, textValue=413101], delete VariableInstanceEntity[id=891544, name=workflowType, type=integer, longValue=1, textValue=1], delete VariableInstanceEntity[id=891545, name=poi, type=integer, longValue=0, textValue=0], delete VariableInstanceEntity[id=891546, name=pId, type=integer, longValue=157603, textValue=157603], delete VariableInstanceEntity[id=891547, name=uId, type=integer, longValue=413101, textValue=413101], delete VariableInstanceEntity[id=891548, name=fId, type=integer, longValue=3750535, textValue=3750535], delete VariableInstanceEntity[id=891549, name=status, type=integer, longValue=1, textValue=1], delete IdentityLinkEntity[id=891536, type=starter, uId=413101, processInstanceId=891535], delete IdentityLinkEntity[id=891552, type=participant, uId=U:413101, processInstanceId=891535], delete ProcessInstance[891535]]

I would appreciate if anyone can provide any hint/suggestions to get rid of this error.

Thanks,
JDP

[jbarrez]

Can you try this with a newer version of Activiti? You're using a quite old version … and a lot has changed in the meantime.

[jdp]

Thanks jbarrez,

Actually Activiti version upgrade is not possible for short time in my app, let see what we can do.

Regards,
JDP

[elygre]

This might be related to https://activiti.atlassian.net/browse/ACT-4178, although that one is discovered in an Oracle-database.