Migration lock acquisition error

[obazoud]

Describe the bug
Currently, when all three services (API, Delivery, Log) restart at the same time, there is a rare case where all of them attempt to run the migration concurrently. This leads to a migration lock acquisition error for the other services, causing them to fail to start.

migrate.New: failed to open database: try lock failed in line 0: SELECT pg_advisory_lock($1) (details: pq: unnamed prepared statement does not exist)

To Reproduce
Steps to reproduce the behavior:

1. Redeploy all three services (API, Delivery, Log) simultaneously
2. Observe the startup logs for each service
3. Occasionally, only one service successfully acquires the migration lock and completes the migration; the other services fail to start due to a migration lock acquisition error

Expected behavior
If a service cannot acquire the migration lock, it should retry after a delay
Optionally, it should be possible to configure only one service to perform the migration, while others skip this step. Or enforce only API to perform this migration task.

Screenshots
N/A

Additional context
The migration lock is managed by the golang-migrate library here

[alexbouchardd]

Or enforce only API to perform this migration task.

This seems reasonable, since it's horizontally scalable, though it doesn't fully solve the issue. We'll still need to account for possible race conditions here 👍

[alexluong]

Or enforce only API to perform this migration task.

hmm I'm not 100% sure about this. The objective of running the migrator is to make sure services are working with the latest schema. If only API handles migration, then wouldn't that introduce a race condition where delivery/log service may run DB operations with invalid schema?

---

This issue only occurs when there are new migrations to be applied. This should be quite rare, only during Outpost upgrade (or initial installation). I wonder if a simple, one-time retry (say 3s wait) can address this.

Even on occasions where the server fails during startup, I think a good orchestration platform will provision a different node and it will work correctly next time, so in theory this isn't an issue?

[obazoud]

If a service cannot acquire the migration lock, it should retry after a delay

Yes this quick fix would already be very helpful 👍

Note that each replica of each service runs its own migration process.

[alexluong]

Note that each replica of each service runs its own migration process.

Yes, each replica of each service runs its own the migration check, but only 1 will successfully acquire a lock and proceed with the migration. I think a 5s delay if there's a locking error should be sufficient for now?

[obazoud]

Yes will be fine.

[alexluong]

Hi @obazoud, just want to let you know this issue is resolved in the new release v0.6.1, thanks again for sharing this with us.