Slow Cost Plan population causing deadlocks

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Performance Issue. Slow Query execution from Cost Plan updates is causing database deadlocks. When we try to copy a large cost plan the action may take some time and users may go and do other activities to update the cost plan at the same time and it could cause the deadlocks. Since there are multiple UI actions that can be performed users don't know what exactly is going on with the system. Deadlocks can bring down the system and cause outage and the performance degradation.

Steps to Reproduce:

In the Modern User Experience (UX), create a project with multiple cost plans
In the cost plan details, have at least 200+ line items
Start copying the existing cost plan, you can start a cost plan copy in a couple of different ways, either from inside the cost plan to copy or at the cost plan list page right click on the source plan.
The copy cost plan is asynchronous so the plan doesn't show up until it is created unless you refresh the page and then a new copied cost plan appears.
Now you can go into the target cost plan and if the asynchronous copy action hasn't been completed you will see no cost plan rows, So now you can choose Populate from Allocations and the spinner shows activity
While that is going on you can navigate back to the cost plan list page and again choose populate from allocations for the target cost plan
While that action is running you can click back into the target cost plan you can once again choose populate from allocations.

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 ...

Environment

Release : 15.9.2, 15.9.3

Component : CLARITY PROJECT MANAGEMENT

Cause

DE62169 - Performance Issue. 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).

Resolution

Fixed in Release 16.0.0
Fixed in Release 15.9.3 Patch #1 (15.9.3.1)

Additional Information

Reference 16.0.0 Clarity Release Notes > Resolved Defects