Slow query execution from cost plan updates causing blocks/deadlock in the database
STEPS TO REPRODUCE:
Expected Results: While several UI actions are performed, in the database there will be several queries in a blocked state waiting for the first execution to be completed
Actual Results: Several blocked queries and slow performing queries can be seen in the database and deadlocks can be seen in app ca logs
Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 18866 waits for ShareLock on transaction 276629593; blocked by process 14410.
Process 14410 waits for ExclusiveLock on tuple (2109,16) of relation 50436495 of database 50431660; blocked by process 4537.
Process 4537 waits for ShareLock on transaction 276614278; blocked by process 18866.
Hint: See server log for query details.
Where: while updating tuple (369181,4) in relation "fin_cost_plan_details"
update fin_plans set CODE = case when $1 = 2 then CODE else $2 end, STATUS_CODE = case when $3 = 2 then STATUS_CODE else $4 end, START_PERIOD_ID = case when $5 = 2 then START_PERIOD_ID else $6 end, TOTAL_COST = case when $7 = 2 then TOTAL_COST else $8 end, IS_PLAN_OF_RECORD = case when $9 = 2 then IS_PLAN_OF_RECORD else $10 end, DESCRIPTION = case when $11 = 2 then DESCRIPTION else $12 end, BENEFIT_PLAN_ID = case when $13 = 2 then BENEFIT_PLAN_ID else $14 end, END_PERIOD_ID = case when $15 = 2 then END_PERIOD_ID else $16 end, TOTAL_UNITS = case when $17 = 2 then TOTAL_UNITS else $18 end, PLAN_BY_2_CODE = case when $19 = 2 then PLAN_BY_2_CODE else $20 end, BUDGET_SUBMIT_OPTION = case when $21 = 2 then BUDGET_SUBMIT_OPTION else $22 end, OBJECT_ID = case when $23 = 2 then OBJECT_ID else $24 end, PLAN_TYPE_CODE = case when $25 = 2 then PLAN_TYPE_CODE else $26 end, REVISION = case when $27 = 2 then REVISION else $28 end, TOTAL_REVENUE = case when $29 = 2 then TOTAL_REVENUE else $30 end, PLAN_BY_1_CODE = case when ...
The Copy cost plan function is very slow. It takes 15 minutes to copy a cost plan on a project with 441 team rows. It looks like it uses the CopyEngine/PlanCopyEngine to copy the original cost plan detail rows (and causes insta slice) and then it loops through each record again processing each fiscal tsv (and once again causing insta slicing).
This is logged as defect DE62169
Release : 15.9.2, 15.9.3
Component : CLARITY PROJECT MANAGEMENT
The defect is fixed in 15.9.3 pre patch 1 for GCP SaaS and for other platform this will be fixed in 15.9.3 Patch 1 and Clarity 16.0