CA Workload Automation DE hangs due to database deadlocks when using MS-SQL

Document ID : KB000102987
Last Modified Date : 21/06/2018
Show Technical Document Details
Issue:
CA Workload Automation DE tracelogs reports multiple database deadlocks.  The jobs are delayed and manager appears to have stopped scheduling new events.
Environment:
CA Workload Automation DE R11.3/R12
Database: MS-SQL 2012 and above
Cause:
The issue is due to the database deadlocks which is mainly seen in MS-SQL.  The following error messages may be seen in the DE tracelogs.
201806XX HH:MM:27.111 [relationaldatabase] [ERROR] DM.Appl.SAMPLE_APPL.100: [201806XX HH:MM:27.120] SQLCode: 1205 SQLState: 40001 Message: Transaction (Process ID 172) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 

201806XX HH:MM:27.111 [relationaldatabase] [ERROR] DM.Appl.SAMPLE_APPL.100: [2018-06-XX_HH:MM:27.120] SQL Exception for query: DELETE FROM ESP_SUBSCRIPTIONS WHERE ID = ?; the exception is: Transaction (Process ID 172) 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 172) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 
Resolution:
CA is aware of this issue.  Contact CA Support for further updates.
Current workaround is to restart CA Workload Automation DE.  You may also need to restart the MS-SQL database.