Adding auditing can cause deadlocks in postgresql

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Adding auditing can cause deadlocks in Postgresql. Which in turn can cause application slowness. It can include users starting to see white pages in the Modern UX, or pages not loading.

Steps to Reproduce:

• Start a long running query that takes a long time to complete
• Add an attribute to be audited.

Expected Results: Attribute audit is added.

Actual Results: Deadlock occurs with long query.

Environment

Clarity SaaS

Resolution

Workaround: Perform auditing changes after business hours.

 

Solution: 

• Engage Broadcom Support to enable a protection feature toggle.
• There is an internal option that can be switched on and it will enable the below functionality which will avoid any situation in which a deadlock blocks the entire system: 
  • feature toggle DE80850_ABORT_REQUESTS_AVOIDING_LONG_WAITS

With this enabled:
The system now checks for database locks in two key areas:

• Attribute Management: When you create, update, or delete an attribute, the system looks for locks on the relevant database tables (like ODF_CA_INVESTMENTS). If locks are found and persist for 15 seconds, an error message appears, and the action is blocked. This prevents changes to the system's structure while it's busy.
• Audit Configuration: Similarly, when you configure auditing for an attribute (choosing to track create, update, or delete operations), the system checks for locks on the tables involved (like INV_INVESTMENTS). Again, if locks remain for 15 seconds, an error is shown, and the audit configuration is not saved. This ensures that audit settings aren't changed while the underlying data is in use.

In essence, these checks ensure smooth operation by preventing changes to attributes or their audit settings when database tables are locked, likely by other ongoing processes.