Why SELECT FOR UPDATE returns 0 rows in scenario below? But if I just execute sql query from second transaction it always returns 1 row.
TRANSACTION 1:
--------------------------
BEGIN;
UPDATE t1 SET t1c3 = "string_value_1" WHERE t1c1 = 123456789;
-- Query returned successfully: one row affected, 51 msec execution time.
TRANSACTION 2:
--------------------------
WITH
cte1 AS (
SELECT t2c2 FROM t2 WHERE t2c1 = "string_value_2"
),
cte2 AS (
SELECT * FROM t1
WHERE
t1c1 = 123456789
AND t1c2 = (SELECT t2c2 FROM cte1)
FOR UPDATE
)
SELECT * FROM cte2
-- Waiting
TRANSACTION 1:
--------------------------
COMMIT;
-- Query returned successfully with no result in 41 msec.
TRANSACTION 2:
--------------------------
-- Returned 0 rows