Re: SELECT .. FOR UPDATE: find out who locked a row - Mailing list pgsql-general

select l.* from pg_locks l
left join pg_database d on l.database=d.oid
where pid <> pg_backend_pid()  and d.datname = current_database()  and relation::regclass='<tablename>'::regclass;

locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |     mode     | granted | fastpath 
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------+---------+----------relation |   629976 |   638971 |      |       |            |               |         |       |          | 3/983554           | 60515 | RowShareLock | t       | t
(1 row)

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;

--

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