CA API Gateway 9.3 CR1/CR2 - SSG Cluster_info table, DeadLock Error

Document ID : KB000111956
Last Modified Date : 10/10/2018
Show Technical Document Details
Issue:

After installing Gateway 9.2 Cumulative Release patch CR1 or CR2, a database error is seen in the ssg logs, stating that a deadlock has occurred against Gateway cluster_info table.

 
Resolution:
This is a known defect (DE361245), affecting CA API Gateway 9.3 CR1 and CR2.
The deadlock is due to a clearStaleNodes executed right before updateServiceUsage and they end up locking each other.
Note: On the point of view of Gateway, this is not a major concern as we use InnoDB, which is designed to detect the condition and rolls back one of the transactions. 

Here an example of the error from the logs:
 
2018-08-17T09:19:55.487+0900 SEVERE  127 org.hibernate.util.JDBCExceptionReporter: Deadlock found when trying to get lock; try restarting transaction
2018-08-17T09:19:55.487+0900 WARNING 127 com.l7tech.server.cluster.ClusterInfoManagerImpl: error cleaning cluster statuses
org.springframework.dao.CannotAcquireLockException: could not execute update query; SQL [delete from cluster_info where statustimestamp<?]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute update query
  

SOLUTION: This has been now fixed in CA API Gateway 9.3 Cumulative Release 3.
Hence, to solve the issue, please apply CR3 patch or higher to the Gateway

Solution can be download directly from "CA API Management Solutions and Patches" web site:
https://support.ca.com/us/product-content/recommended-reading/technical-document-index/ca-api-management-solutions-and-patches.html