postgresqllockingpostgresql-9.6

What happens for "FOR SHARE" with nested select?

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

  • 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.