Flyway, Postgresql, Unable to acquire Flyway advisory lock

Question

We had a transaction that updates 2000000 rows in a postgresql 10 database using flyway (v6). It failed with Unable to acquire Flyway advisory lock .

2000000 records is not that big and it is 1 logical transaction.

I was able to reproduce the problem only once, since then I can't reproduce the problem. This is a micro-sercice, and the target table is alone in it's schema.

Any clue ?

Valedictorian

I faced with the same problem in my Digital Ocean managed postgres deployment. The error disappeared when i closed my PgAdmin III tool session or maybe it was something else. – Maimaia

Answer 1

If this flyway is running in a kubernetes pod, when the pod is created for the first time, it may take long time and cause timeout while kubernetes validating the pod; and the next pod creating attempt may get this exception while the first one is already trying to complete the flyway on the other hand with an advisory lock.

In this case after waiting a proper time, it should go to normal, since one of pods would apply the flyway script to the database, at least(assuming that you do not have a problem in your flyway scripts).

Maimaia

Answer 2

I had the same issue. Kindly check the events section to see if you notice the below "killing: Container name XXX failed livenss probe , will be restarted". If so kindly increase the initialDelay seconds under liveness probe config to higher value. This will allow some time for flyway to finish and avoid getting killing halfway from liveness probe check feature.

Please see this link to understand more - link

Davie

Answer 3

running this query, released the lock :

SELECT pg_advisory_unlock_all();

Jacques

This should only be done with caution. Locks, especially from sources like flyway, are acquired for a reason, and removing them without due diligence might cause data loss. – Inkstand