Date: Sat, 21 Sep 2024 00:17:18 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: IO related waits
Message-ID: <20240920221718.ctq5whqfyqlrjwr2@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAB+=1TV-b8=RGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6=PQ@mail.gmail.com>
 <fd6afa3b-f5db-4632-8e25-678ef66703d5@aklaver.com>
 <CAB+=1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA@mail.gmail.com>
 <CAB+=1TWhGgybtCdU7oR3oghhXG6vULUEkebG1DbT+bc1v7yp4w@mail.gmail.com>
 <586d988d-978b-4b08-84b4-ef3ebb8736a8@aklaver.com>
 <CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com>
 <CAKAnmmJhsNL=f+z7R018L6NrLQuq_7+quqZkGUKbzHRctHaY3w@mail.gmail.com>
 <bc8363b5-28d0-4218-b3d6-73c7d78717da@aklaver.com>
 <CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
 <4cfe720e-4283-43fc-8d72-8fa11e2f741a@aklaver.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="gwlg373diuz4rgkc"
Content-Disposition: inline
In-Reply-To: <4cfe720e-4283-43fc-8d72-8fa11e2f741a@aklaver.com>
Archived-At: <https://www.postgresql.org/message-id/20240920221718.ctq5whqfyqlrjwr2%40hjp.at>
Precedence: bulk


--gwlg373diuz4rgkc
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-09-20 14:11:38 -0700, Adrian Klaver wrote:
> On 9/20/24 1:01 PM, veem v wrote:
> > Able to reproduce this deadlock graph as below.=A0 Now my question is ,
> > this is a legitimate=A0scenario in which the same ID can get inserted f=
rom
> > multiple sessions and in such cases it's expected to skip that (thus "On
> > conflict Do=A0nothing" is used) row. But as we see it's breaking the co=
de
>=20
> Yeah, as I see it that would not work with concurrent uncommitted sessions
> as it would be unresolved whether a conflict actually exists until at lea=
st
> one of the sessions completes.
>=20
> > with deadlock error during race=A0conditions where a lot of parallel
> > threads are operating. So how should we handle this scenario? Will
> > setting the "lock_timeout" parameter at session level will help us
> > anyway here?
>=20
> Serializable transaction?:
>=20
> https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALI=
ZABLE

Doesn't help here, at least not directly. It would help indirectly
because isolation level serializable makes it very proable that
serialization errors occur. So an application designed for serializable
would have some kind of retry logic already in place.=20

SO that leads as to another solution:

Retry each batch (possibly after reducing the batch size) until it
succeeds.

        hp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--gwlg373diuz4rgkc
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmbt9GcACgkQ8g5IURL+
KF03fBAAn7OzwnYquSm7FaNj51RUgXfIXfWQwIZlYtf+H3sN7wr/2z6+p+DJs1Rg
ATl3QxIj4SxVDrAyb8ICrejIv75QS1R0trGEPHiG0WBK/3sx+FGdkahqJydV/XlL
BHwIkr68JRtNvqbedaqPTnTCXwudSMyk7Hub/kaDSJtP/ygGvjnW+/4OjUujlsK/
GQmiRTYcYmTdInw1YFjhB7n+WQDLm7eNpSyIJOV1cNCSAHOj+F3UO3/TNnZQ80q9
CwD07CYH0GPx+g4kTnj12ycm5VPuthWwfljxiFcx4ywWIosfjZo0E3TFX+/qlLhv
lkd9SreMTfICFhBa/B7rxmiTqxGRzndBX5GNlu9ttENze7A1JOKJbLHgQOIKExMO
X7uRhoARbejgc/NDGyxCcqCMClpXvYTTo3GRXsn4MSwnBp3SD2yp5nuqgrtCynMO
oc0qzgV1LueEEyzU+mZpTNREKxjBpoBb8IrT12xg7A13RXqoIVkTSstlNkZTTsxy
zYCnn6/izBFiF+OWll6klVeOno31YHQpH87qcer6Czb26s/o7vxah4FE40d1Ikn6
ERGwgExQuIpXu/vMOeM6QtLi3oBbMVSQQVEPukLTrme2x/JQ+voAtawvBnLtsLPX
6q8pqDWkUf3xIVgktiZCeX+S8DDWqgAgxd+/7d/GBv85GOHGaA0=
=9K86
-----END PGP SIGNATURE-----

--gwlg373diuz4rgkc--