I am trying to achieve some way of
FOR UPDATE locking rows returned in a
LEFT JOIN. Postgresql does not support combining
FOR UPDATE with an other join, since in the case of null rows it has nothing to lock on.
In my current query I had thought it would be enough to just lock on table
TableA in a subquery, and left join in table
TableB outside of it. The query runs, but it seems that postgresql fetches the rows from the
LEFT JOIN regardless of whether the lock on the rows in table
TableA could be obtained or not.
My current query looks something like this:
SELECT * FROM ( SELECT j.correlation_id, j.current_state FROM ( SELECT * FROM "Db"."TableA" WHERE "correlation_id" = @p0 FOR UPDATE ) AS j LIMIT 2 ) AS t LEFT JOIN "Db"."TableB" AS j0 ON t.correlation_id = j0.correlation_id ORDER BY t.correlation_id
So, while Alice is currently running this query and holding the lock in her transaction, Bob tries to execute the same query. The result seems to be that Bob immediately fetches the data specified in the
LEFT JOIN, and then waits for the
FOR UPDATE lock to release.
Ideally I would want the rows in the
JOIN to be locked as well, but at the very least the data from the
JOIN should be fetched after the
FOR UPDATE lock has been obtained.