The Time Slicing job fails intermittently with a deadlock error in the BG logs. This KB provides guidance on how to avoid this issue.
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
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.
This can be due to one or more of the following causes:
All Databases: MS SQL, Oracle, and Postgres
If the job fails as a one-time event, this can be ignored. See Time Slicing job fails with error: "ORA-00060: deadlock detected..." for more details.
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:
The following jobs should be set per best practices:
Any job in the list will wait for the Time Slicing job to finish before it is launched.
The following query can also be used:
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
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
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.
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.
Have the DBA team review the DDL of the custom trigger. Disable the trigger and determine if the issue still happens