Time Slicing job fails with deadlock errors - troubleshooting
Article ID: 52175
Updated On:
Products
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
Note: The failure is not fatal, as the job will recover and process the records the next time it runs. It's recommended to ignore this error
Cause
This can be due to one or more of the following causes:
- A large amount of XOG activity was introduced while the Time Slicing job is running.
- A custom process or job is running at the same time as the Time Slicing job.
- A custom process or trigger was placed on an action that initiates the Time Slicing job.
Environment
All Databases: MS SQL, Oracle, and Postgres
Resolution
Table of Contents
- Step 1: Run the job again
- Step 2: Make the job incompatible with required and recommended jobs
- Step 3: Check for long running process or XOG activity
- Step 4: Change the frequency of the time slicing job
- Step 5: For MSSQL databases: Reach out to the DBA team
- Step 6: If a custom trigger is used, disable the trigger
- Step 7: Review the following KB for other known causes
Step 1: Run the job again
If the job fails as a one-time event, or just few times per day, this can be 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 error: "ORA-00060: deadlock detected..." for more details.
Step 2: Try making the job incompatible if a repeat event of deadlock with a known 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. For step by step instructions, see below:
- In Clarity, go to Administration > Data Administration > Reports and Jobs
- Filter for the Time Slicing job, then click on the Time Slicing job
- Click on the Incompatible Jobs tab
- Click Add and filter for the job you want to make incompatible
- Check the check box next to the job and click Add
- Repeat this step for other recommended and require jobs listed below
The following jobs should be set per best practices:
- Datamart Extraction (Default)
- Delete Investments (Default)
- Datamart Rollup (Default)
The following jobs may be set if you notice a persistent deadlock at the same time of run. Do not set them unless you confirm this the culprit
- Rate Matrix Extraction (Recommended only if you determine there is an issue)
- Post Timesheets (Recommended only if you determine there is an issue)
- Post Transactions to Financials (Recommended only if you determine there is an issue)
- Investment Allocation (Recommended only if you determine there is an issue)
- Import Financial Actuals (Recommended only if you determine there is an issue)
- Update Earned Value and Cost Totals (Recommended only if you determine there is an issue)
- Load Data Warehouse (Recommended only if you determine there is an issue)
Any job in the list will wait for the Time Slicing job to finish before it is launched. Unless there is a repeated deadlock we do not recommend to set jobs as incompatible
The following query can also be used:
select
I.JOB_DEFINITION_ID,
I.NON_CONCUR_JOB_DEF_ID,
k.job_code
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')
order by J.JOB_CODE
Step 3: Check for long running process or XOG activity
If there is a long-running process/XOG activity, pause the Waiting/Scheduled Time Slicing job.
Obtain the results of:
select * from PRJ_BLB_SLICEREQUESTS order by id
Step 4: Change the frequency of the time slicing job
Change the Time Slicing job scheduled interval 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.
Step 5: For MSSQL databases: Reach out to the DBA team
Reach out to your DBA team to see what configuration can be set to view the capture and view the deadlock graphs. See KB: Time Slicing job fails due to deadlock issue on MSSQL for more details.
Step 6: If a custom trigger is used, disable the trigger
Have the DBA team review the DDL of the custom trigger. Disable the trigger and determine if the issue still happens
Step 7: Review the following KB for other known causes
Additional Information
Feedback
