On Thu, Mar 15, 2018 at 4:48 PM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings, * Enrico Thierbach (eno@open-lab.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;
