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

On Fri, Mar 16, 2018 at 8:00 AM, Enrico Thierbach <eno(at)open-lab(dot)org> wrote:

> 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.
>
​You probably considered this but the queuing mechanism I use doesn't hold
locks on records during processing. Workers claim tasks by locking them,
setting a claimed flag of some sort, the releasing the lock (including
worker identity if desired) - repeating the general procedure once
completed.

My volume is such that the bloat the extra update causes is not meaningful
and is easily handled by (auto-)vacuum​.

David J.