Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

On Thu, Aug 20, 2020 at 4:40 PM Jim Jarvie <jim(at)talentstack(dot)to> wrote:

> On 20-Aug.-2020 17:42, Michael Lewis wrote:
>
> Can you share an explain analyze for the query that does the select for
> update? I wouldn't assume that partition pruning is possible at all with
> hash, and it would be interesting to see how it is finding those rows.
>
> Well this got interesting - the already moved error showed up: Note, the
> actual process partitions are regular table partitions, these are not
> hashed. Only the incoming and completed are hashed due to row counts at
> either end of the processing; in flight (where the issue shows up) is quite
> small:
>
> [queuedb] # explain analyze select queueid,txobject,objectid,state from
> mq.queue where (state = 'tx_active' or state='tx_fail_retryable') and
> txobject = 'ticket' limit 250 for update skip locked;
> ERROR: 40001: tuple to be locked was already moved to another partition
> due to concurrent update
> LOCATION: heapam_tuple_lock, heapam_handler.c:405
> Time: 579.131 ms
>
That is super curious. I hope that someone will jump in with an explanation
or theory on this.

I still wonder why the move between partitions is needed though if the work
is either done (failed or successful) or not done... not started, retry
needed or in progress... it doesn't matter. It needs to get picked up by
the next process if it isn't already row locked.

>