we recently upgraded from activiti 5.2.x to activiti 6 and now we have deadlock issue on out database.
The sql statement which is the source of deadlock is as follows:
(@P0 nvarchar(4000),@P1 int)delete from ACT_RU_TIMER_JOB where ID_ = @P0 and REV_ = @P1
We have tried to add non-clustured index on ID_ AND REV_ however the lock still exists.
the above is the source of issue which cause lock on the followings:
(@P0 nvarchar(4000))select * from ACT_RU_JOB where ID_ = @P0
(@P0 datetime2,@P1 int,@P2 int)SELECT SUB.* FROM ( select RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.* from ACT_RU_JOB RES where RES.LOCK_EXP_TIME_ is not null and RES.LOCK_EXP_TIME_ < @P0 )RES ) SUB WHERE SUB.rnk >= @P1 AND SUB.rnk < @P2
(@P0 int,@P1 int)SELECT SUB.* FROM ( select RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.* from ACT_RU_JOB RES where LOCK_EXP_TIME_ is null )RES ) SUB WHERE SUB.rnk >= @P0 AND SUB.rnk < @P1
Please let me know what further information you require and will provide you