Question
Anthem
US
Last activity: 28 Dec 2018 5:24 EST
Load Test dead lock queries @ pc_assign_worklist.
Hi Team,
Deadlock queries has been obtained on regular basis @ pc_assign_worklist during the load test.
PFB for the list of deadlock queries.
1) Database update took more than the threshold of 500 ms: 2,422 ms SQL: delete from dbo.pc_assign_worklist where pzInsKey = ? inserts:
Hi Team,
Deadlock queries has been obtained on regular basis @ pc_assign_worklist during the load test.
PFB for the list of deadlock queries.
1) Database update took more than the threshold of 500 ms: 2,422 ms SQL: delete from dbo.pc_assign_worklist where pzInsKey = ? inserts:
2) Database update took more than the threshold of 500 ms: 2,232 ms SQL: INSERT INTO dbo.pc_assign_worklist (pzInsKey , pxCommitDateTime , "ElapsedWL" , "Level" , "Market" , "MethodOfReceipt" , "ParentCaseID" , "Priority" , "ReceivedDate" , "RequestType" , "Type" , "UrgencyLevel" , "WOID" , "WOStatus" , "pxApplication" , "pxAssignedOperatorID" , "pxAssignedOrg" , "pxAssignedOrgDiv" , "pxAssignedOrgUnit" , "pxAssignedUserName" , "pxCreateDateTime" , "pxCreateOpName" , "pxCreateOperator" , "pxCreateSystemID" , "pxDeadlineExecute" , "pxDeadlineTime" , "pxFlowInsKey" , "pxFlowName" , "pxGoalExecute" , "pxGoalTime" , "pxInsName" , "pxLateTime" , "pxObjClass" , "pxRefObjectClass" , "pxRefObjectInsName" , "pxRefObjectKey" , "pxRefQueueKey" , "pxSaveDateTime" , "pxServiceLevelName" , "pxStageID" , "pxStageLabel" , "pxSystemFlow" , "pxTaskLabel" , "pxTaskName" , "pxTimeFlowStarted" , "pxUpdateDateTime" , "pxUpdateOpName" , "pxUpdateOperator" , "pxUpdateSystemID" , "pxUrgency" , "pxUrgencyAssign" , "pxWorkGroup" , "pyActionTime" , "pyAssignmentStatus" , "pyEffortActual" , "pyErrorAssignment" , "pyErrorMessage" , "pyErrorStep" , "pyFlowInError" , "pyFlowType" , "pyInstructions" , "pyInterestPageClass" , "pyLabel" , "pyProcessOnNode" , "pyResolutionCost" , pzPVStream) VALUES (? , CURRENT_TIMESTAMP , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) inserts: > > > > > > > > > > > > 2018-12-22 19:19:12.734> > > > > > > 2018-12-22 19:19:12.739> > > > Auth Review> 2018-12-22 19:16:40.631> > > > > > 10> > > > > > > > Auth Requirements> > > [B@4f88f0d2>
3) Database operation took more than the threshold of 500 ms: 2,796 ms SQL: SELECT "PC0"."pxUrgencyAssign" AS "pxUrgencyAssign" , "PC0"."pxRefObjectInsName" AS "pxRefObjectInsName" , "PC0"."pyLabel" AS "pyLabel" , "PC0"."pyAssignmentStatus" AS "pyAssignmentStatus" , "PC0"."pyInstructions" AS "pyInstructions" , "PC0"."pyAssignmentStatus" AS "pyAssignmentStatus" , "PC0"."pxDeadlineTime" AS "pxDeadlineTime" , "PC0"."pxAssignedOperatorID" AS "pxAssignedOperatorID" , "PC0"."pxUpdateOperator" AS "pxUpdateOperator" , "PC0"."pxUpdateDateTime" AS "pxUpdateDateTime" , "PC0"."pxGoalTime" AS "pxGoalTime" , "PC0"."pxCreateOpName" AS "pxCreateOpName" , "PC0"."pxCreateDateTime" AS "pxCreateDateTime" , "PC0"."pxTaskLabel" AS "pxTaskLabel" , "PC0"."pxAssignedUserName" AS "pxAssignedUserName" , "PC0"."pxRefObjectClass" AS "pxRefObjectClass" , "PC0"."pyAssignmentStatus" AS "pyAssignmentStatus" , "PC0"."pzInsKey" AS "pzInsKey" FROM dbo.pc_assign_worklist "PC0" WHERE ( ( "PC0"."pxAssignedOperatorID" = ? AND ( "PC0"."pxRefObjectClass" = ? OR "PC0"."pxRefObjectClass" = ? OR "PC0"."pxRefObjectClass" = ? ) ) OR ( "PC0"."pxAssignedOperatorID" = ? AND "PC0"."pyAssignmentStatus"
Please suggest.
Pegasystems Inc.
IN
Hi
From the queries posted I could see any deadlock error message, If you mean to say that above queries are involved in a deadlock scenario, Please refer to the article which might help you if you are MSSQL Database:
Pegasystems Inc.
FR
Yes, also can you work with your DBA on those SQL statements? He might be able to help you to increase performance if need be.
Pegasystems Inc.
FR
Yes, we do have OOTB tables but those tables have to be controlled by DBA. Especially on a Production system, depending on your application and the use of those tables, you may have to add an index sometimes in order to increase performance.
Not too much work for a DBA to run the query on the database side and get the execution plan to give you recommendations.