What happens for "FOR SHARE" with nested select?
Problem Description:
Say, I have a nested select:
WITH candidate AS (
select * table_a where flag = 'a'
)
select * from candidate where other_flag = 'b' for share
This SQL indirectly select from table_a with for share. In this case, will row locks be acquired on returned rows from table_a?
Solution – 1
That query should not acquire any row locks, because candidate is not a base table. But you can see yourself: run
EXPLAIN WITH (...) SELECT ...
and see if there is a LockRows node in the execution plan.
If you want to lock the rows, you will have to use FOR SHARE in the subquery.