Deadlock on complete multiple user tasks concurrently

a7mad3rbeed · April 28, 2022, 12:11am

Hello all,

I’m using Embedded Camunda Engine 7.16.0.
I have an issue when completing multiple tasks concurrently using multiple threads, a deadlock exception from the DB returned.
Scenario: A pending user tasks are waiting for action. A user complete them at once, then I created a thread pool of size 10 that will handle the completion, 1 thread for each task.
Sometimes a deadlock is happening.
I’m using Microsoft Sql Server database, also I configured the database as the following configuration
Configuration For Microsoft Sql Server

Can you please advice?

Thank you in advance.

Log:

 --- [ool-15-thread-2] c.w.w.p.task.TaskCompletableServiceImpl  : An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace.

  DELETE_BULK deleteAuthorizationsForResourceId {resourceId=2a1afc6f-c536-11ec-ab74-0a580a1404d3, resourceType=7}
]
	at org.camunda.bpm.engine.impl.db.EnginePersistenceLogger.flushDbOperationUnexpectedException(EnginePersistenceLogger.java:666) ~[camunda-engine-7.16.0.jar:7.16.0]
	... 50 common frames omitted
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error flushing statements.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 114) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
  UPDATE VariableInstanceEntity[2a1aae4b-c536-11ec-ab74-0a580a1404d3]
  DELETE TaskEntity[2a1afc6f-c536-11ec-ab74-0a580a1404d3]
  UPDATE ExecutionEntity[2a1a8738-c536-11ec-ab74-0a580a1404d3]
  UPDATE HistoricActivityInstanceEventEntity[Activity_1dclkja:2a1afc6e-c536-11ec-ab74-0a580a1404d3]
  UPDATE HistoricTaskInstanceEventEntity[2a1afc6f-c536-11ec-ab74-0a580a1404d3]
  DELETE IdentityLinkEntity[2a1c0df0-c536-11ec-ab74-0a580a1404d3]
  DELETE IdentityLinkEntity[2a1c3404-c536-11ec-ab74-0a580a1404d3]
  DELETE IdentityLinkEntity[2a1c8228-c536-11ec-ab74-0a580a1404d3]
  DELETE IdentityLinkEntity[2a1ca93c-c536-11ec-ab74-0a580a1404d3]
  DELETE IdentityLinkEntity[2a1cd050-c536-11ec-ab74-0a580a1404d3]
  UPDATE VariableInstanceEntity[2a1aae49-c536-11ec-ab74-0a580a1404d3]
  INSERT IdentityLinkEntity[e7f2417b-c5f7-11ec-9373-0a580a14022e]
  INSERT IdentityLinkEntity[e7f2688f-c5f7-11ec-9373-0a580a14022e]
  DELETE IdentityLinkEntity[2a1afc70-c536-11ec-ab74-0a580a1404d3]
  DELETE IdentityLinkEntity[2a1b71a4-c536-11ec-ab74-0a580a1404d3]
  DELETE IdentityLinkEntity[2a1b98b8-c536-11ec-ab74-0a580a1404d3]
  DELETE IdentityLinkEntity[2a1be6dc-c536-11ec-ab74-0a580a1404d3]
  INSERT IdentityLinkEntity[e7f12ff5-c5f7-11ec-9373-0a580a14022e]
  INSERT IdentityLinkEntity[e7f17e1e-c5f7-11ec-9373-0a580a14022e]
  INSERT IdentityLinkEntity[e7f1a533-c5f7-11ec-9373-0a580a14022e]
  INSERT IdentityLinkEntity[e7f1f357-c5f7-11ec-9373-0a580a14022e]
  INSERT TaskEntity[e7f108dd-c5f7-11ec-9373-0a580a14022e]
  INSERT VariableInstanceEntity[e7ed5fd1-c5f7-11ec-9373-0a580a14022e]
  INSERT IdentityLinkEntity[e7f108de-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricDecisionOutputInstanceEntity[e7efaa08-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricDecisionInstanceEntity[e7efaa05-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricDecisionInputInstanceEntity[e7efaa06-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricDecisionInputInstanceEntity[e7efaa07-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricActivityInstanceEventEntity[Activity_0zdtjwg:e7f108db-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricIdentityLinkLogEventEntity[e7f17e20-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricIdentityLinkLogEventEntity[e7f1a534-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricIdentityLinkLogEventEntity[e7f1f358-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricIdentityLinkLogEventEntity[e7f2417c-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricIdentityLinkLogEventEntity[e7f26890-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricIdentityLinkLogEventEntity[e7f2ddc5-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricVariableUpdateEventEntity[e7ecc38e-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricVariableUpdateEventEntity[e7ed5fd2-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricVariableUpdateEventEntity[e7ed5fd3-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricTaskInstanceEventEntity[e7f108dd-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricIdentityLinkLogEventEntity[e7f108df-c5f7-11ec-9373-0a580a14022e]
  INSERT HistoricIdentityLinkLogEventEntity[e7f12ff6-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f2688d-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f2688e-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f28fa1-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f28fa2-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f2ddc3-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f2ddc4-c5f7-11ec-9373-0a580a14022e]
  INSERT UserOperationLogEntryEventEntity[e7ee2328-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f17e1d-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f1a531-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f1a532-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f1f355-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f1f356-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f24179-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f2417a-c5f7-11ec-9373-0a580a14022e]
### Error flushing statements.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 114) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 114) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.'. Flush summary: 
 [
  INSERT HistoricVariableInstanceEntity[e7ed5fd1-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f12ff3-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f12ff4-c5f7-11ec-9373-0a580a14022e]
  INSERT AuthorizationEntity[e7f17e1b-c5f7-11ec-9373-0a580a14022e]

### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 114) 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:30) ~[mybatis-3.5.6.jar:3.5.6]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:254) ~[mybatis-3.5.6.jar:3.5.6]
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.flushBatchOperations(DbSqlSession.java:444) ~[camunda-engine-7.16.0.jar:7.16.0]
	at org.camunda.bpm.engine.impl.db.sql.BatchDbSqlSession.executeDbOperations(BatchDbSqlSession.java:74) ~[camunda-engine-7.16.0.jar:7.16.0]
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.flushDbOperations(DbEntityManager.java:341) ~[camunda-engine-7.16.0.jar:7.16.0]
	... 49 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 114) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-9.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[mssql-jdbc-9.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:2811) ~[mssql-jdbc-9.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:2675) ~[mssql-jdbc-9.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418) ~[mssql-jdbc-9.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272) ~[mssql-jdbc-9.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) ~[mssql-jdbc-9.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) ~[mssql-jdbc-9.2.1.jre8.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2076) ~[mssql-jdbc-9.2.1.jre8.jar:na]
	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
	at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:123) ~[mybatis-3.5.6.jar:3.5.6]
	at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:129) ~[mybatis-3.5.6.jar:3.5.6]
	at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:122) ~[mybatis-3.5.6.jar:3.5.6]
	at org.apache.ibatis.executor.CachingExecutor.flushStatements(CachingExecutor.java:114) ~[mybatis-3.5.6.jar:3.5.6]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:252) ~[mybatis-3.5.6.jar:3.5.6]
	... 52 common frames omitted

aravindhrs · April 28, 2022, 12:42am

@a7mad3rbeed How’s your bpmn model looks like?

a7mad3rbeed · April 28, 2022, 12:48am

It’s a simple workflow,
start event then a user task which has 2 flows for each flow has an expression if a variable x == constant then move to next task that’s it

aravindhrs · April 28, 2022, 3:10am

@a7mad3rbeed could you upload your minimalistic BPMN file which reproduces this issue?

a7mad3rbeed · April 28, 2022, 7:38am

Sandeepkmt · May 3, 2022, 2:10pm

Hello,

Could you try with Async Continuation option, meaning apply Async Before on that Task (which is bound is complete via parallel thread).

Idea behind is: Async before would retry completing the task which might have been in error state due to internal dead lock.

Please let us know.