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

Hi Melvin, hi everyone else,

thank you for your support, and for your query example. And oh yes, I
forgot to mention the postgres version, which is 9.6; but if I find a
solution which works in Version 10 then I could probably update.

I guess with your query I can figure out which connection holds a lock,
but it seems I cannot correlate those locks to the rows which actually
are locked, since `pg_locks` seems not to reference this in any way.

To be more explicit: I can find out about all locks in the current
database that are held by other connections using

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

which, with one locked row, results in something like this:

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

And here is where I am stuck.

`database`, `relation` and `pid` are accounted for - the only value I
can't make sense of is the `virtualtransaction` entry.

I was hoping that objid or objsubid would contain the OID of the locked
row, but obviously I miss a crucial piece of understanding :)
(Note that I tried this both WITH OID and without oid in my table.)

Best,
/eno

--
me at github: https://github.com/radiospiel
me at linked.in: https://www.linkedin.com/in/radiospiel

On 15 Mar 2018, at 22:12, Melvin Davidson wrote:

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