Cluster Log Error - "Lock wait timeout exceeded; try restarting transaction."

Products

• CA API Gateway

Issue/Introduction

Customer has two API Gateway clusters, each with four nodes.

Customer is seeing

"Lock wait timeout exceeded; try restarting transaction."

related to MySQL only on one cluster, but not the other.

Upon investigation, we see that customer has following in the my.cnf

replicate-ignore-table=ssg.service_metrics
replicate-ignore-table=ssg.service_metrics_details
replicate-ignore-table=ssg.service_usage

These means that customer is not replicating the three ssg MySQL tables above.

Environment

Release: 10.1.00+
Component: CA API Gateway

Cause

Since the service_metrics and service_metrics_details are not being replicated, if one cluster is having data issue in these tables, then the other cluster will not receive these bad data and hence will not exhibit the same issue. Historically, there have had other reporting where bad data existed in these two tables caused issues.

Resolution

In this case customer is actually not relying on the Policy Manager Dashboard feature, this is partly confirmed by the fact the service_metrics and service_metrics_details are not replicated. Then this feature can be disabled by  setting the CWP(Cluster Wide Property) serviceMetrics.enabled to false.

Further the two tables should be truncated and analyzed, see the following KB for details:

layer7 ca api gateway restart of ssg services takes a long time to initialize

The ssg service should be restarted afterward.