MIME-Version: 1.0
References: 
 <CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com>
In-Reply-To: 
 <CAFhXkLG=izjYB5_YK3x3yjcVLF1SCe7YfBaBDf8U0zFtRscr6w@mail.gmail.com>
From: Peter Hendriks <peter@mindloops.nl>
Date: Thu, 29 Sep 2022 15:51:31 +0200
Message-ID: 
 <CAFhXkLH2u+xSsRpym3m1s_dXsXvVgn1Lcgd3Brzcq1ASLDdADw@mail.gmail.com>
Subject: Re: Delete from locking ordering differences
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006054e205e9d12df5"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFhXkLH2u%2BxSsRpym3m1s_dXsXvVgn1Lcgd3Brzcq1ASLDdADw%40mail.gmail.com>
Precedence: bulk

--0000000000006054e205e9d12df5
Content-Type: text/plain; charset="UTF-8"

Yes, we do not really care about order, but to prevent locking issues.
Multiple transactions may run this query at the same time. It should never
contend because of the skip locked, so maybe we should try this without an
order by too. We can not get this query to fail in test so far, just in
production, wo we are bit hesitant to change now that it is finally
working... Our guess is to why this makes such a difference is that the
delete statement in postgres does not guarantee ordering, so maybe the
optimizer makes different choices than expected. Asking the question here,
so maybe someone with more understanding can explain why we need the CTE.

Op vr 23 sep. 2022 om 09:31 schreef Peter Hendriks <peter@mindloops.nl>:

> We are wondering if anyone can explain the difference we are having in
> production with the following queries:
>
> DELETE FROM store
> WHERE id IN (
>   SELECT id FROM store
>   FOR UPDATE SKIP LOCKED
>   ORDER BY ID
>   LIMIT 1000
> )
> RETURNING id, payload
>
> This query is sometimes executed with high concurrency, and then can hang
> indefinitely, we assume because of a locking problem that postgresql is not
> detecting as a deadlock.
>
> This alternative query does not have the hanging problem:
>
> WITH store_ids AS (
>   SELECT id FROM store
>   FOR UPDATE SKIP LOCKED
>   ORDER BY ID
>   LIMIT 1000
> )
> DELETE FROM store s
> USING store_ids si
> WHERE s.id = si.id
> RETURNING s.id, s.payload
>
> Can anyone explain why the first query is expected to fail (hang), and the
> second query does not have this problem? We would be interested in more
> understanding on this. Thanks!
>


-- 
Met vriendelijke groet,

Peter Hendriks
Mindloops B.V.

T. +31 (0)6 37 23 26 73
E. peter@mindloops.nl
KvK: 85061921

--0000000000006054e205e9d12df5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Yes, we do not really care about order, but to prevent loc=
king issues. Multiple transactions may run this query at the same time. It =
should never contend because of the skip locked, so maybe we should try thi=
s without an order by too. We can not get this query to fail in test so far=
, just in production, wo we are bit hesitant to change now that it is final=
ly working... Our guess is to why this makes such a difference is that the =
delete statement in postgres does not guarantee ordering, so maybe the opti=
mizer makes different choices than expected. Asking the question here, so m=
aybe someone with more understanding can explain why we need the CTE.</div>=
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">Op vr =
23 sep. 2022 om 09:31 schreef Peter Hendriks &lt;<a href=3D"mailto:peter@mi=
ndloops.nl">peter@mindloops.nl</a>&gt;:<br></div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex"><div dir=3D"ltr">We are wondering if anyone can exp=
lain the difference we are having in production with the following queries:=
<div><br></div><div>DELETE FROM store</div><div>WHERE id IN (</div><div>=C2=
=A0 SELECT id FROM store=C2=A0</div><div>=C2=A0 FOR UPDATE SKIP LOCKED=C2=
=A0</div><div>=C2=A0 ORDER BY ID</div><div>=C2=A0 LIMIT 1000</div><div>)</d=
iv><div>RETURNING id, payload</div><div><br></div><div>This query is someti=
mes executed with high concurrency, and then can hang indefinitely, we assu=
me because of a locking problem that postgresql is not detecting as a deadl=
ock.</div><div><br></div><div>This alternative query does not have the hang=
ing problem:</div><div><br></div><div>WITH store_ids AS (</div><div><div>=
=C2=A0 SELECT id FROM store=C2=A0</div><div>=C2=A0 FOR UPDATE SKIP LOCKED=
=C2=A0</div><div>=C2=A0 ORDER BY ID</div><div>=C2=A0 LIMIT 1000</div></div>=
<div>)</div><div><div>DELETE FROM store s</div><div>USING store_ids si=C2=
=A0</div><div>WHERE <a href=3D"http://s.id" target=3D"_blank">s.id</a> =3D =
<a href=3D"http://si.id" target=3D"_blank">si.id</a></div><div>RETURNING <a=
 href=3D"http://s.id" target=3D"_blank">s.id</a>, s.payload</div></div><div=
><br></div><div>Can anyone explain why the first query is expected to fail =
(hang), and the second query does not have this problem? We would be intere=
sted in more understanding on this. Thanks!=C2=A0</div></div>
</blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"=
 class=3D"gmail_signature"><div dir=3D"ltr"><div style=3D"font-size:small">=
Met vriendelijke groet,</div><div style=3D"font-size:small"><br></div><div =
style=3D"font-size:small">Peter Hendriks</div><div style=3D"font-size:small=
"><div style=3D"font-size:12.8px">Mindloops B.V.</div><div style=3D"font-si=
ze:12.8px"><span style=3D"font-size:12.8px"><br></span></div><div style=3D"=
font-size:12.8px">T. +31 (0)6 37 23 26 73</div><div style=3D"font-size:12.8=
px">E.=C2=A0<a href=3D"mailto:peter@mindloops.nl" style=3D"color:rgb(17,85,=
204)" target=3D"_blank">peter@mindloops.nl</a><br>KvK:=C2=A085061921</div><=
/div></div></div>

--0000000000006054e205e9d12df5--