Prevent Deadlock Errors with Trigger on high concurrent write table

Question

I have a table that is getting around 1000+ inserts per minute. There is a trigger on it to update a column on another table.

 CREATE or replace FUNCTION clothing_price_update() RETURNS trigger AS $clothing_price_update$
    BEGIN
       INSERT INTO
        clothes(clothing_id, last_price, sale_date)
        VALUES(NEW.clothing_id, new.price, new."timestamp")
    ON CONFLICT (clothing_id) DO UPDATE set last_price = NEW.price, sale_date = NEW."timestamp";
        RETURN NEW;
    END;
$clothing_price_update$ LANGUAGE plpgsql;


CREATE TRIGGER clothing_price_update_trigger BEFORE INSERT OR UPDATE ON sales
    FOR EACH ROW EXECUTE PROCEDURE clothing_price_update();

However, I'm randomly getting a Deadlock error. This seems pretty straightforward and there are no other triggers in play. Am I missing something?

sales has data constantly being inserted into it, but it relies on no other tables and no updates occur once data has been added.

A verbatim error msg, your version of Postgres and the definition of table `clothes` (`CREATE TABLE` statement) would make this question more meaningful. Also, probably most important: How many rows are inserted in the same `INSERT INTO sales ...` command (or same transaction), and in what ***sort order***. — Erwin Brandstetter, Oct 23 '21 at 14:24

score 3 · Accepted Answer · answered Oct 23 '21 at 15:00

Going out on a limb, the typical root cause for deadlocks is that the order of written (locked) rows is inconsistent among concurrent transactions.

Imagine two exactly concurrent transactions:

T1:


INSERT INTO sales(clothing_id, price, timestamp) VALUES
  (1, 11, '2000-1-1')
, (2, 22, '2000-2-1');

T2:

INSERT INTO sales(clothing_id, price, timestamp) VALUES
  (2, 23, '2000-2-1')
, (1, 12, '2000-1-1');

T1 locks the row with `clothing_id = 1` in `sales` and `clothes`.

      T2 locks the row with `clothing_id = 2` in `sales` and `clothes`.

T1 waits for T2 to release locks for `clothing_id = 2`.

      T2 waits for T1 to release locks for `clothing_id = 1`.

 Deadlock.

Typically, deadlocks are still extremely unlikely as the time window is so narrow, but with bigger sets / more concurrent transaction / longer transactions / more expensive writes / added cycles for triggers (!) etc. it gets more likely.

The trigger itself is not the cause in this scenario (unless it introduces writes out of order!), it only increases the probability of a deadlock actually happening.

The cure is to insert rows in consistent sort order within the same transaction. Most importantly within the same command. Then the next transaction will wait in line until the first one finishes (COMMIT or ROLLBACK) and releases its locks. 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.

See:

How to simulate deadlock in PostgreSQL?

Long-running transactions typically add to the problem. See:

Table Locking in PostgreSQL

Aside, you use:

ON CONFLICT (clothing_id) DO UPDATE set last_price = NEW.price ...

You may want to use EXCLUDED instead of NEW here:

ON CONFLICT (clothing_id) DO UPDATE set last_price = EXCLUDED.price ...

Subtle difference: this way, effects of possible triggers ON INSERT are carried over, while pasting NEW again overwrites that. Related:

How to UPSERT multiple rows with individual values in one statement?

Prevent Deadlock Errors with Trigger on high concurrent write table

1 Answers1