Error running Liquibase: Could not acquire change log lock

Products

• CA API Gateway

Issue/Introduction

The container ssg cannot start.  The log has the following error: 

error running Liquibase: Could not acquire change log lock.  Currently locked by 5365b08ff53f (fe80:0:0:0:42:acff:fe12:4%eth1) since 9/10/22 12:33 AM.

Environment

API Gateway 10.X
MySQL DB (local, AWS RDS, etc.) 

Cause

Concurrent update/rollback/etc. operations to a database will interfere with each other. Liquibase has a LockService which is used to coordinate access so only one instance can run against a database at time.

Examples of when you can run into concurrency trouble:

• Liquibase is embedded on startup of an application server, and you have a cluster of machines that may be starting at once
• Liquibase runs as part of a build job, and concurrent builds can happen at once

The StandardLockService is the normal implementation that uses a databasechangeloglock table. Liquibase inserts a record and commits it at startup time, and if other instances see that record they wait until it is cleared.

The big problem with this standard implementation is that IF the instance doing the update is killed before it commits the clearing of the record, that value is stuck until liquibase clearLocks is ran.

Container gateway portal uses Liquibase when the server is starting, so it is possible the database is locked due to unexpected exception. 

Resolution

In ssg database, run the query:

select * from DATABASECHANGELOGLOCK;

It shows the DB is LOCKED (locked>0). 

It's possibly due to a killed process not releasing its lock on the DATABASECHANGELOGLOCK table.

Run the following query: UPDATE DATABASECHANGELOGLOCK SET locked=0;