AnsweredAssumed Answered

Activiti deadlock sql server

Question asked by ambrina82 on Apr 7, 2017
Latest reply on Apr 7, 2017 by gdharley

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, I have changed the version now I use 5.22.0 but the problem it isn't resolved.

Does anyone experienced something similar, or does anyone have an idea to start understanding why this happens and how to prevent it?

I use sql server.

Outcomes