Slow Cost Plan Population causing deadlocks

book

Article ID: 221472

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

SUMMARY: Slow Query execution from cost plan updates causing blocks/deadlock at database 

STEPS TO REPRODUCE: 

  1. In the modern ux create a project with multiple cost plans 
  2. In the cost plan details have at least 200+ line items
  3. Start copying the exist cost plan, you can start a cost plan copy in a couple 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 new copied cost plan appears.
  5. Now you can go into the target cost plan and if the asynchronous copy action hasn't 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: Several blocked queries and slow performing queries can be seen at database and dead locks can be seen at 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 ...

Actual Results: While several UI actions are performed, in the database there will be several queries in blocked state waiting for the first execution to be completed  

 

Cause

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

Environment

Release : 15.9.2, 15.9.3

Component : CLARITY PROJECT MANAGEMENT

Resolution

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