Time Slicing job fails with deadlock (Oracle, MSSQL, PostgreSQL)

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

The Time Slicing job fails intermittently (may be a one time event), and errors is thrown in bg-ca.log. The error can be thrown repeatedly.

Examples of the error:

Oracle:

ERROR 2019-12-31 09:37:58,113 [Dispatch Time Slicing : bg@XXXXXXXXX (tenant=clarity)] niku.blobcrack (clarity:scheduler:13286425%Session%:Time Slicing) Error processing slices 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

MSSQL (SQL Server):

ERROR 2022-09-13 15:05:28,417 [Dispatch Time Slicing : bg@XXXXXX (tenant=clarity)] niku.blobcrack (clarity:admin:39670%Session%Time Slicing) Error while inserting slices.
com.niku.union.persistence.PersistenceDeadlockException:
SQL error code: 1205
Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Transaction (Process ID <XX>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

PostgreSQL:

ERROR 2022-09-20 02:20:46,286 [Dispatch Time Slicing (tenant=clarity)] niku.njs (clarity:admin:22825688__62818%Session%) Database error for job 5278930
com.niku.union.persistence.PersistenceDeadlockException:
SQL error code: 0
Error message: ERROR: deadlock detected
Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process XXXXXX waits for ShareLock on transaction XXXXXX; blocked by process XXXX.

Environment

Release : All supported Clarity releases, all database vendors (SQL Server (MSSQL), PostgreSQL, Oracle)

Cause

This is not a critical error but more of a benign warning
What happens is that a row is attempted to be updated by Time Slicing job and by another action, job or process simultaneously
A concurrent update can lead to data consistency issues, so Oracle/MSSQL/PostgreSQL have the deadlock mechanism to prevent it
With deadlock, one of the update requests will fail and the other one will go through
The Time Slicing job is designed to be the "victim" of the deadlock, so out of two updates for the same row, it will be the lowest priority and will fail, which will allow the other action/job/process to proceed and update the row

Resolution

Broadcom Support recommends ignoring the error if it just happened a few times. If the job fails as a one-time event, or just few times per day, this can be safely ignored.
The next run of Time Slicing job will pick up and update where it last left off.
Time Slicing is typically scheduled to run every minute which is 1440 times per day - it is fine if it updates on the next run, it will not affect the data.
Single deadlock failures are part of the design of Time Slicing job being the victim process with lower priority to manual updates, etc.
If you have large XOGs you're running, or a custom manual process and you're getting deadlocks we recommend considering to pause the Time Slicing job whilst the XOG/process is running