Usage pg_advisory_lock can lead to race conditions in migrations resulting in deadlocks #5755
Description
I noticed that sometimes migrations using Prisma can result in deadlocks when more than one instance is trying to run the migrations at a time. This is attempted to be mitigated through the use of advisory locks as can be seen
Unfortunately the ADVISORY_LOCK_TIMEOUT
As per the documentation of Postgres: https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS there are two types of advisory locks that can be obtained here, one that is currently in use pg_advisory_lock and one that (IMHO) is the better one since it would return instantly with a boolean f if it fails to obtain the lock. Essentially mitigating the risk of race conditions and deadlocks altogether.
Proposal would be to change from pg_advisory_lock to pg_try_advisory_lock and handle the change from a timeout error type of scenario to a simple boolean shortcircuit of the failure to obtain the lock as observed here:
pg_advisory_lock ( key bigint ) → void
pg_advisory_lock ( key1 integer, key2 integer ) → void
Obtains an exclusive session-level advisory lock, waiting if necessary.
...
pg_try_advisory_lock ( key bigint ) → boolean
pg_try_advisory_lock ( key1 integer, key2 integer ) → boolean
Obtains an exclusive session-level advisory lock if available. This will either obtain the lock immediately and return true, or return false without waiting if the lock cannot be acquired immediately.
One can mimic the failure on a local Postgres quite easily by following below steps:
-- Shell 1:
CREATE TABLE derp (fname text);
-- Shell 1:
SELECT pg_advisory_lock(72707369);
-- Shell 2:
SELECT pg_advisory_lock(72707369); -- This will hang
-- Shell 1:
CREATE INDEX CONCURRENTLY idx_fname_derp ON derp (fname);Produces following logs:
2026-02-05 10:42:23.375 UTC [18224] ERROR: deadlock detected
2026-02-05 10:42:23.375 UTC [18224] DETAIL: Process 18224 waits for ShareLock on virtual transaction 6/1259; blocked by process 18225.
Process 18225 waits for ExclusiveLock on advisory lock [13757,0,72707369,1]; blocked by process 18224.
Process 18224: CREATE INDEX CONCURRENTLY idx_fname_derp ON derp (fname);
Process 18225: SELECT pg_advisory_lock(72707369);
2026-02-05 10:42:23.375 UTC [18224] HINT: See server log for query details.
2026-02-05 10:42:23.375 UTC [18224] STATEMENT: CREATE INDEX CONCURRENTLY idx_fname_derp ON derp (fname);