Hello,
we are using camunda engine 7.16.
We have scheduled process termination every night for long running processes. Each process that runs longer then 60 days is being terminated with runtimeService.deleteProcessInstance.
We are sometimes (almost every night) facing deadlock errors in our postgres database such as this:
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];LOG: process 19474 detected deadlock while waiting for ShareLock on transaction 12299723 after 1000.075 ms
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];DETAIL: Process holding the lock: 21271. Wait queue: .
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];CONTEXT: while updating tuple (156,4) in relation "act_hi_incident"
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];STATEMENT: update ACT_HI_INCIDENT set
REMOVAL_TIME_ = $1
where ROOT_PROC_INST_ID_ = $2
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-40P01];ERROR: deadlock detected
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-40P01];DETAIL: Process 19474 waits for ShareLock on transaction 12299723; blocked by process 21271.
Process 21271 waits for ShareLock on transaction 12299724; blocked by process 19474.
Process 19474: update ACT_HI_INCIDENT set
REMOVAL_TIME_ = $1
where ROOT_PROC_INST_ID_ = $2
Process 21271: update ACT_HI_PROCINST set
PROC_DEF_ID_ = $1,
PROC_DEF_KEY_ = $2,
BUSINESS_KEY_ = $3,
END_ACT_ID_ = $4,
DELETE_REASON_ = $5,
SUPER_PROCESS_INSTANCE_ID_ = $6,
STATE_ = $7
, END_TIME_ = $8
, DURATION_ = $9
where ID_ = $10
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-40P01];HINT: See server log for query details.
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-40P01];CONTEXT: while updating tuple (156,4) in relation "act_hi_incident"
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-40P01];STATEMENT: update ACT_HI_INCIDENT set
REMOVAL_TIME_ = $1
where ROOT_PROC_INST_ID_ = $2
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];LOG: process 21271 still waiting for ShareLock on transaction 12299724 after 1000.062 ms
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];DETAIL: Process holding the lock: 19474. Wait queue: 21271.
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];CONTEXT: while updating tuple (453,16) in relation "act_hi_procinst"
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];STATEMENT: update ACT_HI_PROCINST set
PROC_DEF_ID_ = $1,
PROC_DEF_KEY_ = $2,
BUSINESS_KEY_ = $3,
END_ACT_ID_ = $4,
DELETE_REASON_ = $5,
SUPER_PROCESS_INSTANCE_ID_ = $6,
STATE_ = $7
, END_TIME_ = $8
, DURATION_ = $9
where ID_ = $10
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];LOG: process 21271 acquired ShareLock on transaction 12299724 after 1000.281 ms
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];CONTEXT: while updating tuple (453,16) in relation "act_hi_procinst"
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];STATEMENT: update ACT_HI_PROCINST set
PROC_DEF_ID_ = $1,
PROC_DEF_KEY_ = $2,
BUSINESS_KEY_ = $3,
END_ACT_ID_ = $4,
DELETE_REASON_ = $5,
SUPER_PROCESS_INSTANCE_ID_ = $6,
STATE_ = $7
, END_TIME_ = $8
, DURATION_ = $9
where ID_ = $10
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];LOG: duration: 1001.423 ms execute <unnamed>: update ACT_HI_PROCINST set
PROC_DEF_ID_ = $1,
PROC_DEF_KEY_ = $2,
BUSINESS_KEY_ = $3,
END_ACT_ID_ = $4,
DELETE_REASON_ = $5,
SUPER_PROCESS_INSTANCE_ID_ = $6,
STATE_ = $7
, END_TIME_ = $8
, DURATION_ = $9
where ID_ = $10
2020-11-23 04:40:03 CET;prod_main_ont_registration;prod_main_ont_registration;ip_address;[PG-00000];DETAIL: parameters: $1 = 'StandaloneRollback:3:d89509e1-e882-11ea-8ce6-0a58c0a840df', $2 = 'StandaloneRollback', $3 = 'ont-registration/f4525481-a28d-460f-9eee-782e339186bc', $4 = NULL, $5 = 'Process lifetime exceeded', $6 = NULL, $7 = 'INTERNALLY_TERMINATED', $8 = '2020-11-23 03:40:02.18', $9 = '5186401134', $10 = '097f857b-fe12-11ea-bcb8-0a58c0a84086'
Does anybody have any idea what could be the cause?
Thank you