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

Enrico,

* Enrico Thierbach (eno(at)open-lab(dot)org) wrote:
> >*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?

Evidently my second email got lost somewhere along the way- what you're
looking for is an extension called 'pgrowlocks':

https://www.postgresql.org/docs/10/static/pgrowlocks.html

My prior email on that subject is here:

https://www.postgresql.org/message-id/20180315220512.GV2416%40tamriel.snowman.net

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

You can write it into the database, of course, but you can't read that
information out by some other process. Depending on what you're doing,
you can use RAISE NOTICE to send messages back to the connected client
from within a stored procedure or similar.

Thanks!

Stephen