We had experienced deadlocks with some standard 'Select' queries provided by Activiti on the following tables: ACT_RU_TASK, ACT_RU_EXECUTION and ACT_RE_PROCDEF. Here is an example query:
### SQL: SELECT SUB.* FROM ( select distinct TEMPRES_ID_ as ID_, TEMPP_KEY_ as ProcessDefinitionKey, TEMPP_ID_ as ProcessDefinitionId, TEMPRES_REV_ as REV_, TEMPRES_ACT_ID_ as ACT_ID_, TEMPRES_BUSINESS_KEY_ as BUSINESS_KEY_, TEMPRES_IS_ACTIVE_ as IS_ACTIVE_, TEMPRES_IS_CONCURRENT_ as IS_CONCURRENT_, TEMPRES_IS_SCOPE_ as IS_SCOPE_, TEMPRES_IS_EVENT_SCOPE_ as IS_EVENT_SCOPE_, TEMPRES_PARENT_ID_ as PARENT_ID_, TEMPRES_PROC_INST_ID_ as PROC_INST_ID_, TEMPRES_SUPER_EXEC_ as SUPER_EXEC_, TEMPRES_SUSPENSION_STATE_ as SUSPENSION_STATE_, TEMPRES_CACHED_ENT_STATE_ as CACHED_ENT_STATE_, TEMPVAR_ID_ as VAR_ID_, TEMPVAR_NAME_ as VAR_NAME_, TEMPVAR_TYPE_ as VAR_TYPE_, TEMPVAR_REV_ as VAR_REV_, TEMPVAR_PROC_INST_ID_ as VAR_PROC_INST_ID_, TEMPVAR_EXECUTION_ID_ as VAR_EXECUTION_ID_, TEMPVAR_TASK_ID_ as VAR_TASK_ID_, TEMPVAR_BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, TEMPVAR_DOUBLE_ as VAR_DOUBLE_, TEMPVAR_TEXT_ as VAR_TEXT_, TEMPVAR_TEXT2_ as VAR_TEXT2_, TEMPVAR_LONG_ as VAR_LONG_ , row_number() over (ORDER BY TEMPRES_ID_ asc) rnk FROM ( select distinct RES.ID_ as TEMPRES_ID_, RES.REV_ as TEMPRES_REV_, P.KEY_ as TEMPP_KEY_, P.ID_ as TEMPP_ID_, RES.ACT_ID_ as TEMPRES_ACT_ID_, RES.PROC_INST_ID_ as TEMPRES_PROC_INST_ID_, RES.BUSINESS_KEY_ as TEMPRES_BUSINESS_KEY_, RES.IS_ACTIVE_ as TEMPRES_IS_ACTIVE_, RES.IS_CONCURRENT_ as TEMPRES_IS_CONCURRENT_, RES.IS_SCOPE_ as TEMPRES_IS_SCOPE_, RES.IS_EVENT_SCOPE_ as TEMPRES_IS_EVENT_SCOPE_, RES.PARENT_ID_ as TEMPRES_PARENT_ID_, RES.SUPER_EXEC_ as TEMPRES_SUPER_EXEC_, RES.SUSPENSION_STATE_ as TEMPRES_SUSPENSION_STATE_, RES.CACHED_ENT_STATE_ as TEMPRES_CACHED_ENT_STATE_, VAR.ID_ as TEMPVAR_ID_, VAR.NAME_ as TEMPVAR_NAME_, VAR.TYPE_ as TEMPVAR_TYPE_, VAR.REV_ as TEMPVAR_REV_, VAR.PROC_INST_ID_ as TEMPVAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as TEMPVAR_EXECUTION_ID_, VAR.TASK_ID_ as TEMPVAR_TASK_ID_, VAR.BYTEARRAY_ID_ as TEMPVAR_BYTEARRAY_ID_, VAR.DOUBLE_ as TEMPVAR_DOUBLE_, VAR.TEXT_ as TEMPVAR_TEXT_, VAR.TEXT2_ as TEMPVAR_TEXT2_, VAR.LONG_ as TEMPVAR_LONG_ from ACT_RU_EXECUTION RES inner join ACT_RE_PROCDEF P on RES.PROC_DEF_ID_ = P.ID_ left outer join ACT_RU_VARIABLE VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null WHERE RES.PARENT_ID_ is null and P.KEY_ = ? and RES.BUSINESS_KEY_ = ? )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ?
Activiti version is 5.15.1, but note that we cannot upgrade it due to a strict project timeline.
Does anyone experienced something similar, or does anyone have an idea to start understanding why this happens and how to prevent it?
When you say "deadlocks" are you referring to the Optimistic locking exceptions or actual deadlocks? If actual deadlocks, are you using MSSql server DB? I ask because the causes of the optimistic locking exceptions are relatively well known and can be almost always resolved with good process modeling.
If it is the MSSql deadlock, then it is possibly the same issue reported under the Camunda (an Activiti fork) here:
https://app.camunda.com/jira/si/jira.issueviews:issue-html/CAM-1646/CAM-1646.html
There are instructions for resolution in the Camunda Jira issue if it is this issue.
Cheers, Greg