The Time Slicing job fails intermittently with a deadlock error in the BG logs. This KB provides guidance on troubleshooting 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. It's recommended to ignore this error
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, 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.
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:
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
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:
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