SQL Deadlock Issue on WA DE (dSeries)
Article ID: 193627
Updated On:
Products
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.
We can see extensive SQL deadlock messages in server tracelog;
202002XX 10:00:00.000 [relationaldatabase] [ERROR] DM.Appl.HKG_DWH.115: [2020-02-XX_10:00:00.000] SQL Exception for query: DELETE FROM ESP_PUBLISHERS WHERE ID = ?; the exception is: Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Environment
Release : 12.x
Component : CA WORKLOAD AUTOMATION DE (DSERIES)
Resolution
Suggested workaround/recommendations in DE (dSeries);
1) Stop DE Server
2) Take a backup of the database
3) Dropping the Foreign key in SQL Server
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.
4) Keep the existing Index as it is, which are created by dSeries installer.
5) 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.
6) Try to revisit the production Applications completion criteria, try to scatter at different intervals, not at the same time.
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
Additional Information
The deadlocks are regularly observed in MS SQL. They are cleared by SQL server side and DE has no control over them. Unfortunately, this results in performance issues and also in delay of jobs running on time. When deadlocks occur, the DE Server has to wait for the deadlock to clear.
For more details, please consult your DBA. Run SQL profiler and trace the lock flags 1222 and 1204. DBA may also setup clustered index on Primary key and non-clustered index on other columns.
Feedback
