How does SELECT FOR UPDATE behave with views in PostgreSQL?

Question

To retrieve domain model data from PostgreSQL, I sometimes select against a view rather than a table. I do this so that I can return as much information as possible in one query, rather than fire off multiple queries to resolve all relational dependencies. Mostly this is because there is a 1-to-1 relationship between tables, or they are lookup tables and I just need certain columns, etc. To prevent certain race conditions, one strategy I'm using is SELECT ... FOR SHARE/UPDATE queries. I'm wondering how these behave when we start using them against views.

---

Suppose we have two tables A and B, each with a column ID. Now, suppose we have a view AB that is a join operation between those two tables. Let's assume this is a Cartesian join:

SELECT
 "A"."ID" AS "AID",
 "B"."ID" AS "BID"
FROM "A"
CROSS JOIN "B"

If we then SELECT FOR UPDATE against AB what rows of what tables will get locked in the following instances?

• SELECT * FROM "AB" WHERE "AB"."AID" = :ID
• SELECT * FROM "AB" WHERE "AB"."BID" = :ID
• SELECT * FROM "AB" WHERE "AB"."AID" = :ID1 AND "AB"."BID" = :ID2

Answer 1

Short answer

The following rows will be locked for update in the three cases:

• a row with "ID" = :ID in table "A" and all rows in table "B"
• a row with "ID" = :ID in table "B" and all rows in table "A"
• a row with "ID" = :ID1 in table "A" and a row with "ID" = :ID2 in table "B"

The results are as one can expect after reading this statement in the documentation:

If a locking clause is applied to a view or sub-query, it affects all tables used in the view or sub-query.

How to test similar cases

Install the extension pgrowlocks..

create extension if not exists pgrowlocks;

Run two instances of psql. In the first one start a transaction and execute a query.

psql #1:

begin;
select *
from "AB"
where "AID" = 3 and "BID" = 103
for update;

You can see locked rows in the other instance of psql.

psql #2:

select * from pgrowlocks('"B"');
 locked_row | locker | multi | xids | modes | pids
------------+--------+-------+---------+----------------+--------
 (0,4) | 88577 | f | {88577} | {"For Update"} | {8068}
(1 row) 

Do not forget to close the transaction at the end (before a next test).

psql #1:

rollback;

The first column of the above result is ctid. You can select the original row with the query:

select *
from "B"
where ctid = '(0,4)';

• e_i_pi: Thanks for the detailed answer, and yes it's as I expected. I couldn't find that note in the documentation, I must have been looking in the wrong place. I guess it's time to look into a more robust locking strategy, starting with research into posts like this: dba.stackexchange.com/questions/81791/…
• user1822: @e_i_pi: you could use Advisory Locks instead of "real" locks if all your code can be changed to comply with that.
• e_i_pi: @a_horse_with_no_name I was thinking more along the lines of having locking tables that 1-to-1 with the main table, and locking that instead. I've used that technique before with good results, and since my app is DDD, this should work well with aggregate roots.