Database deadlock/slowness post upgrade to 15.9.1

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Frequent deadlocks are observed in app-ca.logs and slowness with queries getting blocked in the database.

Some symptoms you may see:

• Time slices running longer and failing 
• Bulk update of cost plans/allocations interfering with Time slices
• Group By Functionality Displays Aggregations for TSV Columns
• App/Bg logs filled with the below errors:

SQL error code: 0
Error message: ERROR: deadlock detected
Detail: Process 12632 waits for ExclusiveLock on tuple (384284,3) of relation 50436696 of database 50431660; blocked by process 14294.
Process 14294 waits for ShareLock on transaction 2562081638; blocked by process 17775.
Process 17775 waits for ExclusiveLock on tuple (1972,13) of relation 50436495 of database 50431660; blocked by process 13272.
Process 13272 waits for ShareLock on transaction 2560437388; blocked by process 12632.
  Hint: See server log for query details.
Executed:
update FIN_COST_PLAN_DETAILS
        set percentage = (CASE WHEN COALESCE(?,0) = 0 THEN NULL ELSE total_cost / ? END)
        ,LAST_UPDATED_DATE = ?
        ,LAST_UPDATED_BY = ?
        where FIN_COST_PLAN_DETAILS.plan_id = ?

Environment

Release : 15.9.1, 15.9.2 , 15.9.3 

Cause

DE60813 - Since the introduction of SQL Curves, a few defects are observed including this reported issue. 

Workaround: 

• For customers on 15.9.1 or 15.9.2, the SQL Curve feature has been optimized in 15.9.2 Patch #1 (15.9.2.1) and recommended to apply and test
• After applying 15.9.2 Patch 1, if deadlocks continue on the Cost Plan, disable the SQL Curve Feature
  • On Premise Customers, see the steps in KB: Enable or disable the SQL Curve feature toggle.
  • SaaS Customers, Request Broadcom Support to disable it

Resolution

Fixed in Release 16.0.0