What happens for "FOR SHARE" with nested select?

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


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.

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.