Clarity Processes stuck in Running state caused by database deadlock
Article ID: 186774
Updated On:
Products
Issue/Introduction
This KB deals with a subset of issues that can arise out of Process Engines, due to specific conditions in some environments. For a good explanation on how Process Engines in Clarity work, refer to KB article with ID 42145.
The general issue being dealt here is Processes stuck, or processing very slowly, even when Process Engine and The BG Service is up and running.
The specific set of conditions that could be observed are as below:
- A large number of process instances getting triggered at the same time, competing for Process Engine's resources
- These process instances containing Gel Scripts within them
- The Gel Scripts containing UPDATE statements within them
Sample error messages that could be noticed in the bg-ca.log:
WARN 2020-03-10 19:12:25,251 [Custom script execution pool-8-thread-3] utils.Utils (clarity:user:session:none) ----> The exception trace is too big to be attached to the process error message. Only a truncated exception trace will be attaced to the process error message. Here is the complete exception trace: org.apache.commons.jelly.JellyTagException: null:152:55: <sql:update> UPDATE ODF_CA_IDEA ......Caused by: java.sql.SQLTransactionRollbackException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
This could be accompanied by a large number of hits on "Post Condition Transition Pipeline" and "Custom Script execution pool", amounting to several hundred hits per minute.
Cause
- A deadlock at the DB, caused due to a trigger on odf_ca_idea table. The table was being updated by the Gel Script directly.
Resolution
Monitor the DB for:
- Long running queries
- Deadlock on specific tables being directly updated by Gel Scripts
- Fragmentation of BPM_ tables or tables that are being touched by Gel Scripts
Feedback
