Subject: Re: Is there a known bug with SKIP LOCKED and "tuple to be locked was
 already moved to another partition due to concurrent update"?
To: pgsql-performance@lists.postgresql.org
References: <4f379a07-c49b-14dd-ddba-e0aaf37235d5@pragmaticdata.com>
From: Jim Jarvie <jim@talentstack.to>
Message-Id: <65b230ec-56a9-a2fc-d0c1-5ed9e31c2f48@talentstack.to>
Date: Tue, 11 Aug 2020 18:58:29 -0400
User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:68.0) Gecko/20100101
 Thunderbird/68.11.0
Mime-Version: 1.0
In-Reply-To: <4f379a07-c49b-14dd-ddba-e0aaf37235d5@pragmaticdata.com>
Content-Type: multipart/alternative;
 boundary=------------92BEE6281CEE0E224D5FA758
Content-Language: en-CA
Precedence: bulk

--------------92BEE6281CEE0E224D5FA758
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi Gunther & List,

I think I have an extremely similar issue and things point in the same=20
direction of a potential issue for skip locked on partitioned tables.

Background is I had a queue table on v9.6 with fairly high volume (>50M=20
items, growth in the 1+M/daily).

Processing the queue with FOR UPDATE SKIP LOCKED was reliable but=20
traffic volumes on v9.6 and the fact v12 is current let to migrating to=20
v12 and using a partitioned table.

Queue has distinct categories of items, so the table is partitioned by=20
list on each category.=C2=A0 Processing in 1 category results in it =
being=20
updated to the next logical category (i.e. it will migrate partition=20
once it is processed).

Within each category, there can be 10'sM rows, so the list partition is=20
hash partitioned as well.=C2=A0 I don't think this is the issue but is=20
mentioned for completeness.

Now, when processing the queue, there are regular transaction aborts=20
with "tuple to be locked was already moved to another partition due to=20
concurrent update".

 From everything I can trace, it really does look like this is caused by=20
rows which should be locked/skipped as they are processed by a different=20
thread.

I tried switching 'for update' to 'for key share' and that created a=20
cascade of deadlock aborts, so was worse for my situation.

For now, I roll back and repeat the select for update skip locked until=20
it succeeds - which it eventually does.

However, it really feels like these should just have been skipped by=20
PostgreSQL without the rollback/retry until success.

So, am I missing something/doing it wrong?=C2=A0 Or could there be a=20
potential issue that needs raised?

Thanks

Jim


On 30-Jun.-2020 12:10, Gunther Schadow wrote:
> Hi all,
>
> long time ago I devised with your help a task queuing system which=20
> uses SELECT ... FOR UPDATE SKIP LOCKED for many parallel workers to=20
> find tasks in the queue, and it used a partitioned table where the hot=20
> part of the queue is short and so the query for a job is quick and the=20
> skip locked locking makes sure that one job is only assigned to one=20
> worker. And this works pretty well for me, except that when we run=20
> many workers we find a lot of these failures occurring:
>
> "tuple to be locked was already moved to another partition due to=20
> concurrent update"
>
> This would not exactly look like a bug, because the message says "to=20
> be locked", so at least it's not allowing two workers to lock the same=20
> tuple. But it seems that the skip-locked mode should not make an error=20
> out of this, but treat it as the tuple was already locked. Why would=20
> it want to lock the tuple (representing the job) if another worker has=20
> already finished his UPDATE of the job to mark it as "done" (which is=20
> what makes the tuple move to the "completed" partition.)
>
> Either the SELECT for jobs to do returned a wrong tuple, which was=20
> already update, or there is some lapse in the locking.
>
> Either way it would seem to be a waste of time throwing all these=20
> errors when the tuple should not even have been selected for update=20
> and locking.
>
> I wonder if anybody knows anything about that issue? Of course you'll=20
> want to see the DDL and SQL queries, etc. but you can't really try it=20
> out unless you do some massively parallel magic. So I figured I just =
ask.
>
> regards,
> -Gunther
>
>
>
>
>

--------------92BEE6281CEE0E224D5FA758
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <p>Hi Gunther &amp; List,</p>
    <p>I think I have an extremely similar issue and things point in the
      same direction of a potential issue for skip locked on partitioned
      tables.</p>
    <p>Background is I had a queue table on v9.6 with fairly high volume
      (&gt;50M items, growth in the 1+M/daily).</p>
    <p>Processing the queue with FOR UPDATE SKIP LOCKED was reliable but
      traffic volumes on v9.6 and the fact v12 is current let to
      migrating to v12 and using a partitioned table.</p>
    <p>Queue has distinct categories of items, so the table is
      partitioned by list on each category.=C2=A0 Processing in 1 =
category
      results in it being updated to the next logical category (i.e. it
      will migrate partition once it is processed).<br>
    </p>
    <p>Within each category, there can be 10'sM rows, so the list
      partition is hash partitioned as well.=C2=A0 I don't think this is =
the
      issue but is mentioned for completeness.<br>
    </p>
    <p>Now, when processing the queue, there are regular transaction
      aborts with "tuple to be locked was already moved to another
      partition due to concurrent update".</p>
    <p>From everything I can trace, it really does look like this is
      caused by rows which should be locked/skipped as they are
      processed by a different thread.</p>
    <p>I tried switching 'for update' to 'for key share' and that
      created a cascade of deadlock aborts, so was worse for my
      situation.<br>
    </p>
    <p>For now, I roll back and repeat the select for update skip locked
      until it succeeds - which it eventually does.</p>
    <p>However, it really feels like these should just have been skipped
      by PostgreSQL without the rollback/retry until success.<br>
    </p>
    <p>So, am I missing something/doing it wrong?=C2=A0 Or could there =
be a
      potential issue that needs raised?</p>
    <p>Thanks</p>
    <p>Jim</p>
    <p><br>
    </p>
    <p><br>
    </p>
    <div class=3D"moz-cite-prefix">On 30-Jun.-2020 12:10, Gunther Schadow
      wrote:<br>
    </div>
    <blockquote type=3D"cite"
      cite=3D"mid:4f379a07-c49b-14dd-ddba-e0aaf37235d5@pragmaticdata.com"=
>Hi
      all,
      <br>
      <br>
      long time ago I devised with your help a task queuing system which
      uses SELECT ... FOR UPDATE SKIP LOCKED for many parallel workers
      to find tasks in the queue, and it used a partitioned table where
      the hot part of the queue is short and so the query for a job is
      quick and the skip locked locking makes sure that one job is only
      assigned to one worker. And this works pretty well for me, except
      that when we run many workers we find a lot of these failures
      occurring:
      <br>
      <br>
      "tuple to be locked was already moved to another partition due to
      concurrent update"
      <br>
      <br>
      This would not exactly look like a bug, because the message says
      "to be locked", so at least it's not allowing two workers to lock
      the same tuple. But it seems that the skip-locked mode should not
      make an error out of this, but treat it as the tuple was already
      locked. Why would it want to lock the tuple (representing the job)
      if another worker has already finished his UPDATE of the job to
      mark it as "done" (which is what makes the tuple move to the
      "completed" partition.)
      <br>
      <br>
      Either the SELECT for jobs to do returned a wrong tuple, which was
      already update, or there is some lapse in the locking.
      <br>
      <br>
      Either way it would seem to be a waste of time throwing all these
      errors when the tuple should not even have been selected for
      update and locking.
      <br>
      <br>
      I wonder if anybody knows anything about that issue? Of course
      you'll want to see the DDL and SQL queries, etc. but you can't
      really try it out unless you do some massively parallel magic. So
      I figured I just ask.
      <br>
      <br>
      regards,
      <br>
      -Gunther
      <br>
      <br>
      <br>
      <br>
      <br>
      <br>
    </blockquote>
  </body>
</html>

--------------92BEE6281CEE0E224D5FA758--