> On 17 Sep 2021, at 8:32, Roman Guryanov <r.guryanov.integrix@gmail.com> wrote:
>
> Hello, could you check my problem.
> Why does SELECT FOR UPDATE return 0 rows in the scenario below? (execution in transaction)
>
> If delete 'FOR UPDATE', 1 row returned
>
> Test case:
> DROP TABLE IF EXISTS
> t1;
>
> CREATE TABLE t1 (_pk serial, t1c1 integer, t1c2 integer
> , t1c3 text);
>
> insert into t1 (t1c1, t1c2, t1c3) values(123456789, 100, 'string_value_1'
> );
(…cut everything related to unused t2…)
>
> WITH
> cte1
> AS
> (
> UPDATE
> t1
> SET t1c3 = 'string_value_1'
> WHERE t1c1 = 123456789
> returning t1c1, t1c2
> ),
> cte2
> AS
> (
> SELECT * FROM
> t1
> WHERE t1c1 = 123456789
> AND t1c2 = (SELECT t1c2 FROM cte1)
> FOR UPDATE
> )
>
> SELECT * FROM cte2;
Most likely the outer select returns 0 rows because you locked the rows you expected in cte2 and didn’t perform an
updateon those locked rows yet.
I suspect your intention for this query is to first lock the rows, then update them and then select them, but instead
youstart with updating them, then lock those rows after the fact and then you try to select those locked rows.
Also, selecting the updated rows by t1c2 in cte2 seems rather risky, as that is a rather different selection criterium
thanyou use for the actual update. It’s okay for this single-row example, but if you had a table full of data, you
wouldnow have locked all rows with the value t1c2 = 100 for update. If that update never happens (or the locking
doesn’tget rolled back), well…
Regards,
Alban Hertroys
--
There is always an exception to always.