Re: SELECT .. FOR UPDATE: find out who locked a row

Greetings,

* Enrico Thierbach (eno(at)open-lab(dot)org) wrote:
> I am using `SELECT * FROM queue ... FOR UPDATE SKIP LOCKED` to implement a
> queueing system.
>
> Now I wonder if it is possible, given the id of one of the locked rows in
> the queue table, to find out which connection/which transaction owns the
> lock.

Sure, you can attempt to lock the record and then run pg_blocking_pids()
(in another session) against the pid which is trying to acquire the
lock.

Session #1:

Connect
SELECT * FROM queue ... FOR UPDATE SKIP LOCKED;
... gets back some id X
... waits

Session #2:

Connect
SELECT pg_backend_pid(); -- save this for the 3rd session
SELECT * FROM queue WHERE id = X FOR UPDATE;
... get blocked waiting for #1
... waits

Session #3:

SELECT pg_blocking_pids(SESSION_2_PID);
-- returns PID of Session #1

Obviously there's race conditions and whatnot (what happens if session
#1 releases the lock?), but that should work to figure out who is
blocking who.

If you're on a version of PG without pg_blocking_pids then you can look
in the pg_locks view, though that's a bit more annoying to decipher.

Thanks!

Stephen