Clarity PPM: Time Slicing job fails with deadlock error

Document ID : KB000052175
Last Modified Date : 23/01/2019
Show Technical Document Details
Issue:

The Time Slicing job fails with a deadlock error in the BG logs.

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

Cause:

One or more of the following causes are plausible:

1. Large amount of XOG activity was introduced while the Time Slicing job is running.
2. An custom process or job is running at the same time as the Time Slicing job.
3. A custom process or trigger was placed on an action that initiates the Time Slicing job.

Resolution:

Solution 1:

Check the Time Slicing job incompatibility list. For OnPremise customers, the following query can also be run to check the incompatible jobs set:

select
--I.JOB_DEFINITION_ID,
j.job_code, K.JOB_CODE incompatible
--I.NON_CONCUR_JOB_DEF_ID
FROM
CMN_SCH_JOB_NONCONCUR I,
CMN_SCH_JOB_DEFINITIONS J,
CMN_SCH_JOB_DEFINITIONS K
Where I.Job_Definition_Id = J.Id
and I.NON_CONCUR_JOB_DEF_ID = K.id
and J.JOB_CODE in ('Time_Slicing','inv_alloc_job')
order by J.JOB_CODE
 
Solution 2:
As referenced in KB000027257, pause the Time Slicing job while doing a large amount of XOG activity 

Note: The failure is not critical, as the job will recover and process the records the next time it runs.

Obtain the results of:
select * from PRJ_BLB_SLICEREQUESTS order by id

Solution 3:
Set the Time Slicing job from running every minute 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.

For MSSQL databases, reach out to the DBA team to see what configuration can be set to view the capture and view the deadlock graphs.
https://support.microsoft.com/en-us/help/224453/inf-understanding-and-resolving-sql-server-blocking-problems

If a custom trigger is used, disable the trigger and check if the issue happens again.
Have the DBA team review the DDL of the custom trigger.