Greetings, * Enrico Thierbach (eno@xxxxxxxxxxxx) 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
Attachment:
signature.asc
Description: PGP signature
