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

Hi Melvin, Stephen, hi list,

> *FWIW, I really don't understand your need to identify the actual rows
> that
> are locked. Once you have identified the query that is causing a block
> (which is usually due to "Idle in Transaction"), AFAIK the only way to
> remedy the problem is to kill the offending query, or wait for it to
> complete. I am not aware of any way available to a user to "unlock"
> individual rows". Indeed, if you could, it would probably lead to
> corruption of some form.*

The goal is to run a job queue, with a potentially largish number of
workers that feed of the queue. So it would be useful to find out which
queue entry is being processed right now (I can easily find out: when a
row cannot be read via SKIP UNLOCKED it is locked, and probably being
worked upon.) It would also be great to understand which worker holds
the lock. The intention is NOT to kill the worker or its query.

With what the conversation brought up here (still trying to catch up
with everything) I can:

1) determine all workers that currently are holding a lock (via
Melvin’s);
2) on an individual base try to lock the row in a second connection and
use a third connection to figure out which worker connection holds a
lock on a specific single row (via Stephen’s).

This is probably good enough to cover the necessary basic functionality,
so thank you for your input.

Am I correct to assume that there is no other way to determine who is
holding a lock on a specific row and/or determine this for many rows in
one go?

(I guess I am also correct to assume that whatever the worker is doing
there is no way to somehow write this information into the database
**via the same connection**. (Using a second connection would be
obviously easy)

Best,
/eno

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