Time Slicing job fails with deadlock errors - troubleshooting

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Time Slicing job fails intermittently with a deadlock error in the BG logs. This KB provides guidance on troubleshooting this issue. 

MSSQL:

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Oracle:

com.niku.union.persistence.PersistenceDeadlockException:
SQL error code: 60
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00060: deadlock detected while waiting for resource

PostgreSQL:

com.niku.union.persistence.PersistenceDeadlockException: 
SQL error code: 0
Error message: ERROR: deadlock detected

Note: The failure is not fatal, as the job will recover and process the records the next time it runs. It's generally recommended to ignore this error

Environment

All Databases: MSSQL, Oracle, and Postgres

Resolution

1. Run the job again

• If the job fails as a one-time event, or just few times per day, this can be safely ignored.
• The job runs 1440 times per day so it is fine if it updates on the next minute run.
• See Time Slicing job fails with deadlock for more details and no further troubleshooting is required.

2. Work with DBA to see what is the other process 

If you see repeated failures: Contact the DBA with the full message from the bg-ca.log to ask him what was the other process that is getting deadlocked. This is going to help you understand how to deal with the issue going further. 

• If identified long running process or XOG activity

  • Pause the Waiting/Scheduled Time Slicing job when the XOG is running or make sure they don't run at the same time. 

• If a repeat event of deadlock with a known or custom job)

  • Use the following page (Administration > Data Administration > Reports and Jobs > Time Slicing job > Incompatible Jobs tab) to check or edit the Time Slicing job incompatibility list.
  • Only make any changes if you notice a persistent deadlock at the same time with the very same job
  • Do not set them unless you confirm this the culprit and it's a frequently repeated event
  • Any job set as incompatible will wait for the Time Slicing job to finish before it is launched, so this can cause delays on large jobs - proceed with caution.
  • Unless there is a frequently repeated deadlock with the same job with more than 5 or 10 occurrences we do not recommend to set OOTB jobs as incompatible

• If a customization - custom trigger or database job

  • Disable/remove/redesign the customization accordingly

3. Try changing the frequency of the time slicing job

• Change the Time Slicing job scheduled interval from running every minute (default)  to every 5 minutes. This will generate the same data without impact.
• This theoretically would alleviate the contention considerably as the Time Slicing is not forced to process bulk data change, e.g. a XOG or process that reads/updates data such as allocation
• If your Time Slicing is running every 5 minutes or less often than every minute and you are concerned about data being updated and having multiple deadlocks, then change the frequency to 1 min

4. Ensure your database performance is good

• If the database performance is not great, then the deadlocks may happen more often as all processes are more long running.
• Work with the DBA team to ensure your database is having the best performance, all the stats jobs are run etc.
• If needed, the DBA may want to raise a case with Oracle or Microsoft Support to help improve the database performance

 

Additional Information

See also:

• Time Slicing job fails with deadlock (MSSQL, Oracle, PostgreSQL)
• Time Slicing job appears to be hanging - troubleshooting
• Time Slicing job slow performance - troubleshooting