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 Does this mean the new version for row is only returned when the SELECT FOR SHARE is returning rows back to client ? ------ Thanks, Tanzeel
