Rows lock order and deadlock risk using SELECT FOR UPDATE with JOIN

Question

Consider the following transaction:

BEGIN;

SELECT *
FROM A
JOIN B ON A.id = B.a_id
WHERE A.id = 123
ORDER BY A.id, B.id
FOR UPDATE;

-- more ops...

COMMIT;

I'm using the ORDER BY clause just to ensure that rows are locked in a consistent way so that, if multiple concurrent connections execute the same transaction, a deadlock would not happen.

My questions are:

1. Am I using the ORDER BY clause in the right way (considering I'm doing a JOIN)?
2. In what exact order are the rows locked? I'm triyng to lock all rows from table A before (in order by id) and then all rows from table B (again in order by id) but I guess this is not the right way to go.

I asked a similar question months ago, but JOIN clause was not used.

Answer 1

The rows will be locked in the order that they are emitted by the ORDER BY. One consequence of that is that it will lock rows from both tables – one result row of the join will consist of a row from each of the tables.

Note that if the id columns receive concurrent updates, the result may have an ordering different from the one specified in ORDER BY, so rows could get locked in a different order than you intend. But then, primary keys should not get modified anyway.

Assuming that id is never updated, and you want to lock the rows in a first, you could use a temporary table:

BEGIN;

CREATE TEMP TABLE t ON COMMIT DROP AS
SELECT a.id AS a_id, b.id AS b_id
FROM a
   JOIN b ON a.id = b.a_id
WHERE a.id = 123;

SELECT ...
FROM a
   JOIN t ON a.id = t.a_id
ORDER BY a.id
FOR NO KEY UPDATE;

SELECT ...
FROM b
   JOIN t ON b.id = t.b_id
ORDER BY b.id
FOR NO KEY UPDATE;

/* other processing */

COMMIT;