ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Slow Cost Plan population causing deadlocks


Article ID: 221472


Updated On:


Clarity PPM SaaS Clarity PPM On Premise


Slow query execution from cost plan updates causing blocks/deadlock in the database


  1. In the Modern User Experience (UX), create a project with multiple cost plans 
  2. In the cost plan details, have at least 200+ line items
  3. 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.
  4. 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.
  5. 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
  6. 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
  7. 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 ...


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



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