Deadlock in AFTER INSERT trigger

Question

I have an AFTER INSERT trigger for table A that updates multiple rows in table B (incrementing a column in table B). Normally, the web server would insert multiple rows into table A using a single insert statement with the READ COMMITTED isolation level, which would cause the trigger to fire for each row inserted. As there are multiple web servers at once, there will always be multiple concurrent insert statements/transactions.

Initially, this led to Postgres reporting deadlocks when the trigger was updating the rows in table B. To address this, I changed the trigger function to update the rows in table B in the order of their PK:

UPDATE table_b
SET counter = counter + 1
WHERE ctid = ANY(ARRAY(
  SELECT ctid FROM table_b
  WHERE name = 'some name' AND store = 'store id'
  ORDER BY id
  FOR UPDATE
))

The deadlocks were still happening after this change. I thought the above statement didn't work, so I changed it to use a PL/pgSQL FOR loop:

FOR row_b IN (
    SELECT id
    FROM table_b
    WHERE name = 'some name' AND store = 'store id'
    ORDER BY id
)
LOOP
    UPDATE table_b
    SET counter = counter + 1
    WHERE id = row_b.id;
END LOOP;

But this also didn't work and deadlocks were still happening. The source of the deadlock was reported to be at the update statement of table B (when incrementing the counter of the row). This was the exact error reported by Postgres:

Process 316 waits for ShareLock on transaction 850467907; blocked by process 426.
Process 426 waits for ShareLock on transaction 850467903; blocked by process 316.

I'm confused about how a deadlock could still happen in this situation.

Answer 1

You were on the right track with trying to acquire locks in a consistent sort order. The manual:

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

However, your implementation does not actually achieve that. You write:

• AFTER INSERT trigger for table A that updates multiple rows in table B ...
• ... insert multiple rows into table A using a single insert statement with the READ COMMITTED isolation level

By sorting target rows in table B per inserted row in table A, you only achieve a partial sort. Nothing keeps earlier rows from table A in one transaction to update later rows in table B and vice versa. You would have to enforce a consistent sort order for the whole set of rows that are write-locked in table B over the course of the same transaction. And you would have to take all locks on table A first, also in consistent sort order.

Assuming a trigger FOR EACH ROW, that's not how it works. The manual:

PostgreSQL offers both per-row triggers and per-statement triggers. With a per-row trigger, the trigger function is invoked once for each row that is affected by the statement that fired the trigger. In contrast, a per-statement trigger is invoked only once when an appropriate statement is executed, regardless of the number of rows affected by that statement.

There are various ways to get around this.

• Insert only a single row in table A in the same transaction. (More expensive. May be tedious.)
• Use the SERIALIZABLE isolation level. (More expensive. And you need to be prepared to repeat transactions on serialization failure.)
• Use a statement-level trigger. (May be tricky or impossible.)
• Manual locking on involved rows in table A and table B before executing the INSERT (and the triggered UPDATE) statements.

But first I would investigate if you actually need that trigger to increment multiple rows in table B. That's expensive even under ideal circumstances. Maybe there is a smarter approach, starting with a better DB schema?

Related:

• Why am I getting a deadlock for a single UPDATE query?
• How to prevent deadlocks in many-to-many insert/update trigger function?