SQL Deadlock Issue

book

Article ID: 193627

calendar_today

Updated On:

Products

CA Workload Automation DE - Business Agents (dSeries) CA Workload Automation DE DSERIES- SERVER CA Workload Automation DE - System Agent (dSeries) CA Workload Automation DE - Scheduler (dSeries)

Issue/Introduction

With regards to SQL deadlock, SQL DBA was able to pin point what is causing it. We need your help on how we can stop this.

Environment

Release : 12.2

Component : CA WORKLOAD AUTOMATION DE (DSERIES)

Resolution

Following recommendations suggested from dSeries team after meeting with SQL Server DBA.

1) Stop dSeries Server

2) Dropping the Foreign key in SQLServer

ALTER TABLE ESP_PUBSUB DROP CONSTRAINT FK_PUB

ESP_PUBSUB having two foreign keys, with the other key FK_SUB all the operation will be done. So No code change is required.

3) Keep the existing Index as it is, which are created by dSeries installer. 

4) Once these changes are done on DB, start the dSeries Server and continuously monitor for 2-3 days, whether jobs are getting triggered and completed as expected.

5) Try to revisit the production Applications completion criteria, try to scatter at different intervals, not at the same time.

6) Broadcom dSeries Dev team tested on Microsoft SQL Server 2017(RTM)-14.0.1000.169 (X64).

7) Regular Re-Indexing on below tables for better performance. Schedule a SQLServer Job to re-index the following table based on workload.

ESP_GENERIC_JOB
ESP_APPLICATION
ESP_RTWOB
ESP_PUBLISHERS
ESP_SUBSCRIPTIONS
ESP_PUBSUB
ESP_HOME_JOB
ESP_L2O_MAP