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