A dead lock occurred on ACT_HI_ACTINST

[william_thierry]

Hello,

We are currently facing deadlock on ACT_HI_ACTINST on full asynchronous/exclusive node processes

org.activiti.engine.impl.asyncexecutor.ExecuteAsyncRunnable Job 1169985 failed
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 270) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
To summarize on what happened:
1. 2 jobs of notification are inserted in ACT_RU_JOB. The jobs are created and the transactions are commited .
2. The job concerning PID=1168762 (exec=1181856) is executed firstly and the subwokflow is terminated so the engine is updating ACT_HI_ACTINST to set an endtime "2016-08-25 15:36:15"
update ACT_HI_ACTINST set
      EXECUTION_ID_ = @P0,
      ASSIGNEE_ = @P1,
      END_TIME_ = @P2,
      DURATION_ = @P3
    where ID_ = @P4',4112,8193,@p7 output,N'1181856',NULL,'2016-08-25 15:36:15.0620000',50805,N'1181857'
select @p1, @p2, @p7
3. The job concerning PID=1181910 (exec=1181952) is executed and tries to query ACT_HI_ACTINST with a simple select (after 15:35:44 date of creation of the event)
select RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.*
    from ACT_HI_ACTINST RES
     WHERE  RES.EXECUTION_ID_ = @P0
        and RES.ACT_ID_ = @P1
        and RES.END_TIME_ is null
    )RES ) SUB WHERE SUB.rnk >= @P2 AND SUB.rnk < @P3',4112,8193,@p7 output,N'1181952',N'messageintermediatecatchevent4',1,2
4. A deadlock is detected between the select and the update and the exec=1181952 is killed
5. the event notification job=1169985 is in error and the workflow is stuck on the node

[william_thierry]

we are using actviti 5.20

[jbarrez]

deadlocks can occur under very high loads, but very infrequent. What kind of load are we talking about.

Also worth mentioning: in the latest v6 beta3 we've been exactly working on the job executor to be way more performant than before. We also saw deadlocks under very high load. We've been able to improve on that greatly. Especially the new message based executor is much faster and can handle these things better than the one in 5.20 version.

[william_thierry]

Thank you for your answer.

In order to have our REST API to being called twice which would cause unexpected behavior.
We Override Activiti DefaultFailedJobCommandFactory to modify Job retries policy to turn off every retries.

We are currently thinking about on SQL exceptions  ( ibatis PersistenceException )  letting activiti engine handle the retry with JobRetryCmd , the other working solution is to change the activiti database isolation level to READ_COMMITTED_SNAPSHOT ( we are using SQL Server ).

But we are worried about unexpected behavior on activiti part if we change the isolation level.

And yes we are facing deadlocks only on heavy load.

[jbarrez]

> But we are worried about unexpected behavior on activiti part if we change the isolation level.

Yeah, I would too. Activiti expects. READ_COMMITTED., so that's good. However, with SQL server I seem to recall not everything is 100% the same wrt isolation levels compared to other databases.

[william_thierry]

Thank you for your answer, sorry i was away last week.

If I understood correctly we should stay on READ_COMMITTED and not READ_COMMITTED_SNAPSHOT ?

So our only option would be to let activi retry SQL Exceptions ?

Regards,