Can't understand deadlock between queries on two different tables-CodePudding

I have two tables:

Table A:
    - id
    - id_B  // foreign key to table B's id
    - id_C  // foreign key to table C's id

(id_B, id_C) defines unique constraint.

Table B:
    - id
    - id_C  // foreign key to table C's id

Both the tables have other columns as well.

Now I have the following loop executing in a single transaction in my code:

for {
    "INSERT INTO A VALUES (...) ON CONFLICT DO UPDATE SET (...)"
   
    "UPDATE TABLE B SET (...) WHERE id = (...) AND id_C = (...)"
}

Each iteration executes an insert and an update. After several iterations, the transaction commits.

Now in my production environment, where there can be concurrent requests, I am getting deadlock errors.

And the deadlock occurs because the above INSERT and UPDATE queries are waiting on each other. But I am not able to understand how they wait on each other even in concurrent settings as they are on different tables.

Any help or link to proper documentation which explains this type of scenario will really help.

CodePudding user response：

Any INSERT on table A and any UPDATE on A that modifies the foreign key will place a FOR KEY SHARE lock on the referenced row in B (and in C). Such a lock will conflict with any DELETE on B and with any UPDATE that modifies a primary or unique key.

Based on that, you should be able to determine which of your statements can lock with which other statements.

CodePudding user response：

Postgres stores data into pages so even if you aren't taking any lock they might face deadlock and postgres will fail one the txn.

https://stackoverflow.com/a/46312096/11012702

Sort ids you are trying to insert or update so it will always take lock in sequence and other txn will go into wait state instead of throwing error.

Example

for {
    "INSERT INTO A VALUES (1,..,2...,3..) ON CONFLICT DO UPDATE SET (...)"
   
    "UPDATE TABLE B SET (...) WHERE id = (1) AND id_C = (2)"
}