How can I find out what is holding a PgLock advisory lock in the Postgres database?

Question

I'm using the pg_lock gem to manage Postgres advisory locks for coordinating between processes to avoid race conditions. I have a lock that is failing with an error about Was unable to aquire a lock "my_lock_name" after N attempts, which tells me that something else is already holding onto the lock. How can I look up this lock in the underlying database to find out more information about what's holding it?

Answer 1

Here’s how to go from a PgLock#lock! call to the actual lock in the database to get more info about what’s holding it:

1. Find the lock keys that PgLock is using for your lock name. This comes in two halves (the & 0xFFFF_FFFF is necessary because PgLock works with these numbers as signed ints, but Postgres treats them as unsigned):
   • The first one is 2147483648. This is simply PG_LOCK_SPACE & 0xFFFF_FFFF from pg_lock.rb.
   • The second one can be obtained by replacing your lock name in the following:

     PgLock.new(name: '').send(:key, "my_lock_name") & 0xFFFF_FFFF
     

2. Run this query in Postgres to find your key in the pg_locks table, replacing the objid with the one from the key you got above:

   SELECT *
   FROM pg_locks
   WHERE locktype = 'advisory' AND
     classid = 2147483648 AND -- First key, the static PG_LOCK_SPACE one
     objid = 2928511612 AND -- Second key, based on your lock name
     objsubid = 2; -- key is int pair, not single bigint
   

   This will show information about any active locks being held on this key. In particular the pid column is the posgres server pid of the connection holding the lock.
3. You can get more information about what the connection holding the lock is doing from pg_stat_activity:

   SELECT * FROM pg_stat_activity WHERE pid = PID;
   

4. In extremis you can terminate the connection and force the lock to release with:

   SELECT pg_terminate_backend(PID);