ETL 9.2:Timesheet Save - Performance Test - Deadlock Error On UPDATE PS_TL_IPT13 SET TASK_PROFILE_ID (Doc ID 2193125.1)

Last updated on JULY 21, 2023

Applies to:

Symptoms

On : 9.2 version, Time Reporting

Timesheet saves - Performance test - Deadlock error on UPDATE PS_TL_IPT13 SET TASK_PROFILE_ID
Every time, we are getting the deadlock error at the same location Step TL_TA000501.XA000.Step010 (SQL) and temp table instance keep changing (UPDATE PS_TL_IPT13 or UPDATE PS_TL_IPT15 etc) and the employee would be different. We are selecting a pool of 10 or 14 employees at a time.

We are upgrading to HCM 9.2, PT 8.54 from 9.0 / PT 8.51

We are in the process of running performance tests on a TEST environment using IBM Rational Performance tool.

1. When tester logs in and reports time, timesheet saves, without any issues
2. TL_TIMEADMIN application engine program is not customized
3. There are custom Time & Labor rules configured in the system
4. Time Admin Batch process does not have any issues. Payable time is created and time is being available for approval
5. When tests are done using performance scripts (12 users logs in, reports time and saves timesheet. This takes around 2.5 minutes duration), We are receiving deadlock errors for at least one of the employees. We tried different set of employees, different pay periods etc, still, deadlock error is received
6. Temp Table instances(total) and online are set to 7. Current HRMS 9.0 production also set to 7
7. Application server log extract, Performance tests error message, and instances screen shots are attached
8. SQL error received: Failed SQL stmt: UPDATE PS_TL_IPT13 SET TASK_PROFILE_ID = ( SELECT PW.TASK_PROFILE_ID FROM PS_TL_PROF_WRK3 PW WHERE PW.PROCESS_INSTANCE = 100007824 AND PW.EMPLID=PS_TL_IPT13.EMPLID AND PW.EMPL_RCD=PS_TL_IPT13.EMPL_RCD AND PW.TASKGROUP = PS_TL_IPT13.TASKGROUP AND PS_TL_IPT13.DUR BETWEEN PW.EFFDT AND PW.END_EFFDT) WHERE PS_TL_IPT13.TASK_PROFILE_ID = ' ' AND PS_TL_IPT13.COMPANY = ' ' AND PS_TL_IPT13.BUSINESS_UNIT = ' ' AND PS_TL_IPT13.SETID_LOCATION = ' ' AND PS_TL_IPT13.LOCATION = ' ' AND PS_TL_IPT13.SETID_DEPT = ' ' AND PS_TL_IPT13.DEPTID = ' ' etc......
 

ERROR
-----------------------
Message

File: e:\pt85411c-retail\peopletools\src\psappeng\aedebug.hSQL error.
Stmt #: 1703 Error Position: 0 Return: 8603 - [Microsoft][SQL Server Native Client 11.0][SQL Server]
Transaction (Process ID 133) was deadlocked on lock resources with another process and has been chosen
as the deadlock victim. Rerun the transaction. (SQLSTATE 40001) 1205
Failed SQL stmt: UPDATE PS_TL_IPT13 SET TASK_PROFILE_ID = ( SELECT PW.TASK_PROFILE_ID FROM PS_TL_PROF_WRK3 PW
WHERE PW.PROCESS_INSTANCE = 100003593 AND PW.EMPLID=PS_TL_IPT13.EMPLID AND PW.EMPL_RCD=PS_TL_IPT13.EMPL_RCD AND
PW.TASKGROUP = PS_TL_IPT13.TASKGROUP AND PS_TL_IPT13.DUR BETWEEN PW.EFFDT AND PW.END_EFFDT)
WHERE PS_TL_IPT13.TASK_PROFILE_ID = ' ' AND PS_TL_IPT13.COMPANY = ' ' AND PS_TL_IPT13.BUSINESS_UNIT = ' '
AND PS_TL_IPT13.SETID_LOCATION = ' ' AND PS_TL_IPT13.LOCATION = ' ' AND PS_TL_IPT13.SETID_DEPT = ' '
AND PS_TL_IPT13.DEPTID = ' ' AND PS_TL_IPT13.SETID_JOBCODE = ' ' AND PS_TL_IPT13.JOBCODE = ' '
 AND PS_TL_IPT13.POSITION_NBR = ' ' AND PS_TL_IPT13.PRODUCT = ' ' AND PS_TL_IPT13.CUSTOMER = ' '
 AND PS_TL_IPT13.ACCT_CD = ' ' AND PS_TL_IPT13.BUSINESS_UNIT_PC = ' ' AND PS_TL_IPT13.BUSINESS_UNIT_PF = ' '
 AND PS_TL_IPT13.PROJECT_ID = ' ' AND PS_TL_IPT13.SETID_ACTIVITY = ' ' AND PS_TL_IPT13.ACTIVITY_ID = ' '
 AND PS_TL_IPT13.RESOURCE_TYPE = ' ' AND PS_TL_IPT13.SETID_RESOURCE = ' ' AND PS_TL_IPT13.RESOURCE_CATEGORY = ' '
 AND PS_TL_IPT13.RESOURCE_SUB_CAT = ' ' AND PS_TL_IPT13.TASK = ' ' AND PS_TL_IPT13.USER_FIELD_1 = ' '
 AND PS_TL_IPT13.USER_FIELD_2 = ' ' AND PS_TL_IPT13.USER_FIELD_3 = ' ' AND PS_TL_IPT13.USER_FIELD_4 = ' '
 AND PS_TL_IPT13.USER_FIELD_5 = ' ' AND EXISTS ( SELECT 'X' FROM PS_TL_PROF_WRK3 PW WHERE PROCESS_INSTANCE = 100003593 AND
 PW.EMPLID=PS_TL_IPT13.EMPLID AND PW.EMPL_RCD=PS_TL_IPT13.EMPL_RCD AND PW.TASKGROUP = PS_TL_IPT13.TASKGROUP AND PS_TL_IPT13.DUR
 BETWEEN PW.EFFDT AND PW.END_EFFDT)


]]> Message

Process 100003593 ABENDED at Step TL_TA000501.XA000.Step010 (SQL) -- RC = 8603 (108,524)


]]> Message

CallAppEngine: Application TL_CALL_TA: AppEngine error. (2,653) FUNCLIB_TL_WEEK.APPLY_RULES.FieldFormula
 Name:RunTimeAdmin PCPC:3255 Statement:36
Called from:TL_MSS_EE_SRCH_PRD.GBL.SavePostChange Statement:9

During a CallAppEngine builtin function call, the Application Engine detected an error.
 The Application Engine log may provide more information.


STEPS
-----------------------

5. When tests are done using performance scripts (12 users logs in, reports time and saves timesheet. This takes around 2.5 minutes duration), We are receiving deadlock errors for at least one of the employees. We tried different set of employees, different pay periods etc, still deadlock error is received
The issue can be reproduced at will with the following steps:
1. Multiple users need to log in during performance test (around 12) .
2. Report time by navigating to the timesheet page: Self Service, then select Time Reporting, then select Report Time, then select Timesheet
3. Employees save timesheet by clicking on save.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.