Does Postgres lock all rows in a query atomically, even across different tables via JOIN?-CodePudding

I am getting a deadlock error on my code. The issue is that this deadlock error is happening on the very first query of the transaction. This query joins two tables, TableA and TableB and should lock a single row in TableA with id==table_a_id, and all the rows on TableB that have a foreign key for table_a_id.

The query looks as follows (I am using SQLAlchemy, this output is from printing the equivalent query from it and will have its code below as well):

SELECT TableB.id AS TableB_id
FROM TableA JOIN TableB ON TableA.id = TableB.table_a_id 
WHERE TableB.id = %(id_1)s FOR UPDATE

The query looks as follows in SQLAlchemy syntax:

query = (
    database.query(TableB.id)
    .select_from(TableA)
    .filter_by(id=table_a_id)
    .join((TableB, TableA.id == TableB.table_a_id))
    .with_for_update()
)

return query.all()

My question is, will this query atomically lock all those rows from both tables? If so, why would I get a deadlock already exactly on this query, given it's the first query of the transaction?

CodePudding user response：

The query will lock the rows one after the other as they are selected. The exact order will depend on the execution plan. Perhaps you can add FOR UPDATE OF table_name to lock rows only in the table where you need them locked.

I have two more ideas:

rewrite the query so that it locks the rows in a certain order:

WITH b AS MATERIALIZED (
   SELECT id, table_a_id
   FROM tableb
   WHERE id = 42
   FOR NO KEY UPDATE
)
SELECT b.id
FROM tablea
WHERE EXISTS (SELECT 1 FROM b
              WHERE tablea.id = b.table_a_id)
ORDER BY tablea.id
FOR NO KEY UPDATE;

Performance may not be as good, but if everybody selects like that, you won't get a deadlock.

lock the tables:
```
LOCK TABLE tablea, tableb IN EXCLUSIVE MODE;
```
That lock will prevent concurrent row locks and data modifications, so you will be safe from a deadlock.

Only do that as a last-ditch effort, and don't do it too often. If you frequently take high table locks like that, you keep autovacuum from running and endanger the health of your database.