ETL9.2:Deadlocks Occurring During Timesheet Submission (real-time Rules)
Last updated on APRIL 09, 2018
Applies to:PeopleSoft Enterprise HCM Time and Labor - Version 9.2 and later
Information in this document applies to any platform.
When multiple users are submitting time we see extremely poor performance. What we can see in Grid Control is a row lock contention when trying to update the PS_WRK_ADHOC_TAO table:
UPDATE PS_WRK_ADHOC_TAO2 SET SCHED_HRS = ( SELECT S.SCHED_HRS FROM PS_WRK_SHIFT_TAO2 S WHERE S.PROCESS_INSTANCE = PS_WRK_ADHOC_TAO2.PROCESS_INSTANCE AND S.EMPLID = PS_WRK_ADHOC_TAO2.EMPLID AND S.EMPL_RCD = PS_WRK_ADHOC_TAO2.EMPL_RCD AND S.DUR = PS_WRK_ADHOC_TAO2.DUR AND S.SHIFTNUM = PS_WRK_ADHOC_TAO2.SEQ_NO) WHERE EXISTS ( SELECT 'X' FROM PS_WRK_SHIFT_TAO2 S1 WHERE S1.PROCESS_INSTANCE = PS_WRK_ADHOC_TAO2.PROCESS_INSTANCE AND S1.EMPLID = PS_WRK_ADHOC_TAO2.EMPLID AND S1.EMPL_RCD = PS_WRK_ADHOC_TAO2.EMPL_RCD AND S1.DUR = PS_WRK_ADHOC_TAO2.DUR AND S1.SHIFTNUM = PS_WRK_ADHOC_TAO2.SEQ_NO) AND PS_WRK_ADHOC_TAO2.PUNCH_TYPE='0' AND PS_WRK_ADHOC_TAO2.SHIFT_ID = ' '
We applied several bug patches and their dependencies including the bug id referenced in Doc ID 1943377.1. Based on this Doc ID the issue should have been resolved. However we are still encountering the same deadlock. In production this issue cause major service loss and time reporters sometimes wait upwards of 5 minutes for time to submit, while others ultimately time out, due to the deadlocks.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms