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

On Thu, Mar 15, 2018 at 4:48 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> 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
>

> 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

You have not specified which version of PostgreSQL, but try this query.

SELECT c.datname,
c.pid as pid,
c.client_addr,
c.usename as user,
c.query,
c.wait_event,
c.wait_event_type,
/* CASE WHEN c.waiting = TRUE
THEN 'BLOCKED'
ELSE 'no'
END as waiting,
*/
l.pid as blocked_by,
c.query_start,
current_timestamp - c.query_start as duration
FROM pg_stat_activity c
LEFT JOIN pg_locks l1 ON (c.pid = l1.pid and not l1.granted)
LEFT JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
LEFT JOIN pg_stat_activity l ON (l2.pid = l.pid)
LEFT JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
WHERE pg_backend_pid() <> c.pid
ORDER BY datname,
query_start;

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!