On 12/29/25 01:15, Khan, Tanzeel wrote:
Hi,
I am trying to understand the SELECT FOR UPDATE behavior when it is not
returning rows back to client.
postgres=> CREATE TABLE t (col1 INT, col2 INT);
postgres=> INSERT INTO t VALUES (1, 1);
S1: BEGIN; UPDATE t SET col2 = col2 + 1 WHERE col1 = 1;
S2: BEGIN; WITH cte AS (SELECT * FROM t WHERE col1 = 1 FOR UPDATE)
UPDATE t SET col2 = t.col2 + 1 FROM cte AS t_self_join WHERE (t.col2 =
t_self_join.col2);
S1: COMMIT;
S2: zero rows updated
Why does session 2 update zero rows ? Shouldn’t 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 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 returned to the client.
https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-
COMMITTED <https://www.postgresql.org/docs/current/transaction-
iso.html#XACT-READ-COMMITTED>
Does this mean the new version for row is only returned when the SELECT
FOR SHARE is returning rows back to client ?
No I think you missed the sentence before the one you you show above:
"The search condition of the command (the WHERE clause) is re-evaluated
to see if the updated version of the row still matches the search
condition. If so, the second updater proceeds with its operation using
the updated version of the row."
So:
WHERE (t.col2 = t_self_join.col2)
The S1 UPDATE changed the value of col2 to something different then what
is is found by WITH cte AS (SELECT * FROM t WHERE col1 = 1 FOR UPDATE).
------
Thanks,
Tanzeel
--
Adrian Klaver
[email protected]
