From: "Khan, Tanzeel" <tzlkhan@amazon.com>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: SELECT FOR UDPATE behavior inside joins
Thread-Topic: SELECT FOR UDPATE behavior inside joins
Thread-Index: AQHceKFQG1f4JGngWEKdz2KQb5dGTw==
Date: Mon, 29 Dec 2025 09:15:58 +0000
Message-ID: <CH5PR18MB927659676A86C64F0EF16E91A8CDBFA@CH5PR18MB927659.namprd18.prod.outlook.com>
Accept-Language: en-IN, en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: =?Windows-1252?Q?v+f4baFxeLN1i9YYYtpTmon96MPzwEKlRe43TQxD8gvwHBBUB/A6uFTa?=
 =?Windows-1252?Q?E1Zy3WKKXGGDz4KEgLfO2x4TEX9jOGLwVzgi1axRE5l/rn7rKXhHFbTa?=
 =?Windows-1252?Q?vkiCfj3K7DtdlCHoL2b+7ewerSHSs6JuRcoixNz4DDccpAbfk3ALhRrA?=
 =?Windows-1252?Q?7sDJorA+7YiNrPjA0xoAST2wcSYVpAxK4WwTv/NWt9fMcWMRSjT6qhOd?=
 =?Windows-1252?Q?mi4VWQ7KgFkflh21UTrYqFjybfsNvLBWroWEj8AQCrLQ0eBnbVxqiQVC?=
 =?Windows-1252?Q?x1VpHQFmf4njtt+VqsoujpPno+Am2m5N7jVcwI4KpgvOaEriAhAfW/GX?=
 =?Windows-1252?Q?sgHc6G4DDAx8yDp5LYmlRrFlMtqZ85i9nSgrO2FTYYgMrjd/IjZKUbay?=
 =?Windows-1252?Q?4HxZ8PpqYpsUNDjtdi85hdTcV3yWnKOAKTSskReby5q38NEqCTsrAuZU?=
 =?Windows-1252?Q?k0y/B7Z+6N4aGRVR5oDhpBQ8qxgzCbbj8rava7Eew5SVKxkMu3b8BNPN?=
 =?Windows-1252?Q?h7bAwybI+eADU9/EoHI1J224a77M+CQhJDr+ngXhchmRIE4QFkNUITEq?=
 =?Windows-1252?Q?+pitLoUQuXoBk4ApIVsvlxpvYKatp496syLJaPJyIxe8Q2Qu5n2nmoY7?=
 =?Windows-1252?Q?qZ21APRonWgf2YDhJMxCW/8UFAqXVHuoxxkzNB6oBjO6c5VPOpXaGdQ7?=
 =?Windows-1252?Q?Tq0n2nk3r8kcx1BF8TmQMfYDrjcDgxkiTgM8L1Q68v3KQr2dxxbnpdi4?=
 =?Windows-1252?Q?080jHX71tvt2q2dkCzzzGJv/TpLW9mkw98f8K1BkpTGCfRKn45v6iXVp?=
 =?Windows-1252?Q?u+g5skuRQ3VWyKil6YZqCCySuL+En+89llw1c6qL/o8nUL6qGJ1uF8J7?=
 =?Windows-1252?Q?RGckjXwteQKjveV/r+I0A7Rdr5xOKUNYTLp7YufPxiOqqlFgXMWz41L5?=
 =?Windows-1252?Q?boEk/kV8HJx8cLVlLvdHSKeZhO7CGM9Aa67bWC6Y911zxZmamQ5xbcib?=
 =?Windows-1252?Q?9Mjf2n9nUKu37vGC0qQhhvd9jHJkix9Y8tZsl8RdTtKvAUDeq015a2iq?=
 =?Windows-1252?Q?n3DKbhEplBKC02hPzHqJsiP4cSLu3pCnndCy6BdzsJ4ystRy6N40+KKr?=
 =?Windows-1252?Q?0Tr/nSCb1DwuVSoIxY9M/zD1F1lvlyv1kRI6i5kxPuLGpnC5ziG7a7x/?=
 =?Windows-1252?Q?KQ9bjiFM1l0KhL7OQ9zmRbkLkdtvkby+dpdz/79ht/xSmfVtjKmgFT9p?=
 =?Windows-1252?Q?+4suGFRQCIKL17f1lhUKlH3Y/9VWfgyBpi49QeHKv7D07K9NTyKykQ2A?=
 =?Windows-1252?Q?+eSgvsG2+ZrFGz8cCLiBAhESWnDJpIHO/Nbaksi+bUjpDAn5J99aUlS9?=
 =?Windows-1252?Q?zbwLluAICz3+5yGJznyvlumKFpAQSXpbRhLr+856OnHQcY+5QHwoC5Rk?=
 =?Windows-1252?Q?6uGUvzcc43nIVKdF3b8lf7ZUTQkjOYHtoewZ6h/pc8ZuFMaYITHwx0/C?=
 =?Windows-1252?Q?+M7IRRuY7wvtzrh2x2HLDqWF+3JXlWd6JDbAYizH0LSKzxDrHiTr/XMi?=
 =?Windows-1252?Q?hwKuIQo+zAJXc85QaJS3daxJu+IvW3LqueNi7I2EasfOBPiz4Nc95bat?=
 =?Windows-1252?Q?dY2r+psq1kdQjQWorGH/a7g7nqobH/uutiOTy2WGMJyMbx3jC6lsiUkQ?=
 =?Windows-1252?Q?6VkKPhH+8uV/7QkDqLv2X5qjHVsNucsNYSKoDAuD0SYfocNng/+GpgYB?=
 =?Windows-1252?Q?9xvuQTmp2ywEszkgx0enEFmfxV3fcVrwy6YZPm/O9x/9KQsuccXBcAgh?=
 =?Windows-1252?Q?IHzPKH/thY4hh1VHqb7RU7ei7vPp2LR70jw5A32ZimQDZ52OVnRL6mWl?=
 =?Windows-1252?Q?6MTDiw7YINT7T7eunGKwfbaVsY8a7rc60+k=3D?=
Content-Type: multipart/alternative;
	boundary="_000_CH5PR18MB927659676A86C64F0EF16E91A8CDBFACH5PR18MB927659_"
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: CH5PR18MB927659.namprd18.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: b3de673e-415b-4f19-7038-08de46bae106
X-MS-Exchange-CrossTenant-originalarrivaltime: 29 Dec 2025 09:15:58.6251
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 5280104a-472d-4538-9ccf-1e1d0efe8b1b
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 4Vrr232r4BS5yicNIDD9f/c6IV9AGlYwOLe2DtB2onpRlvIj/z8KcZSWPzC9yQeJEJplmqNnZMNtqQW1KSrKEw==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SN7PR18MB3968
X-OriginatorOrg: amazon.com
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/CH5PR18MB927659676A86C64F0EF16E91A8CDBFA%40CH5PR18MB927659.namprd18.prod.outlook.com>
Precedence: bulk

--_000_CH5PR18MB927659676A86C64F0EF16E91A8CDBFACH5PR18MB927659_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

Hi,

I am trying to understand the SELECT FOR UPDATE behavior when it is not ret=
urning rows back to client.

postgres=3D> CREATE TABLE t (col1 INT, col2 INT);
postgres=3D> INSERT INTO t VALUES (1, 1);

S1: BEGIN; UPDATE t SET col2 =3D col2 + 1 WHERE col1 =3D 1;
S2: BEGIN; WITH cte AS (SELECT * FROM t WHERE col1 =3D 1 FOR UPDATE) UPDATE=
 t SET col2 =3D t.col2 + 1 FROM cte AS t_self_join WHERE (t.col2 =3D t_self=
_join.col2);
S1: COMMIT;
S2: zero rows updated

Why does session 2 update zero rows ? Shouldn=92t the SELECT FOR UPDATE and=
 UPDATE read the new version of the row as per
> If so, the second updater proceeds with its operation using the updated v=
ersion of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE, t=
his means it is the updated version of the row that is locked and returned =
to the client.
https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMM=
ITTED

Does this mean the new version for row is only returned when the SELECT FOR=
 SHARE is returning rows back to client ?

------
Thanks,
Tanzeel

--_000_CH5PR18MB927659676A86C64F0EF16E91A8CDBFACH5PR18MB927659_
Content-Type: text/html; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DWindows-1=
252">
</head>
<body>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
Hi,</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
I am trying to understand the SELECT FOR UPDATE behavior when it is not ret=
urning rows back to client.</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
postgres=3D&gt; CREATE TABLE t (col1 INT, col2 INT);</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 color: rgb(0, 0, 0);">
<span style=3D"font-size: 16px; background-color: rgb(255, 255, 255);">post=
gres</span><span style=3D"font-size: 12pt;">=3D&gt; INSERT INTO t VALUES (1=
, 1);</span></div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
S1: BEGIN; UPDATE t SET col2 =3D col2 + 1 WHERE col1 =3D 1;</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
S2: BEGIN; WITH cte AS (SELECT * FROM t WHERE col1 =3D 1 FOR UPDATE) UPDATE=
 t SET col2 =3D t.col2 + 1 FROM cte AS t_self_join WHERE (t.col2 =3D t_self=
_join.col2);</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
S1: COMMIT;</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
S2: zero rows updated</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
Why does session 2 update zero rows ? Shouldn=92t the SELECT FOR UPDATE and=
 UPDATE read the new version of the row as per</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
&gt; If so, the second updater proceeds with its operation using the update=
d version of the row. In the case of SELECT FOR UPDATE and SELECT FOR SHARE=
, this means it is the updated version of the row that is locked and return=
ed to the client.</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
<a href=3D"https://www.postgresql.org/docs/current/transaction-iso.html#XAC=
T-READ-COMMITTED" data-outlook-id=3D"dcc26d7d-7448-4321-8882-664b9853ff52">=
https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMM=
ITTED</a></div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
Does this mean the new version for row is only returned when the SELECT FOR=
 SHARE is returning rows back to client ?</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
------</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
Thanks,</div>
<div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;=
 font-size: 12pt; color: rgb(0, 0, 0);">
Tanzeel</div>
</body>
</html>

--_000_CH5PR18MB927659676A86C64F0EF16E91A8CDBFACH5PR18MB927659_--