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