2

System: Postgres 13.4 on Azure Database for PostgreSQL flexible server

I set up triggers to aggregate data for different products into one aggregation table each. There is also one trigger that adjusts the aggregation tables whenever a discount is entered in the discounts table. Finally, there is one more trigger per product that transfers the data from the aggregation table to a master table. So the master table should always reflect the latest state and can be queried by users.

Now I am experiencing a deadlock. Error information from NodeJS application.

    error: deadlock detected {
        length: 869,
        severity: 'ERROR',
        code: '40P01',
        detail: 'Process 6477 waits for ShareLock on transaction 9458671; blocked by process 6480.\n' +
        'Process 6480 waits for ShareLock on transaction 9458668; blocked by process 6477.',
        hint: 'See server log for query details.',
        position: undefined,
        internalPosition: undefined,
        internalQuery: undefined,
        where: 'while locking tuple (6427,2) in relation "accounts_master"' [...],
        schema: undefined,
        table: undefined,
        column: undefined,
        dataType: undefined,
        constraint: undefined,
        file: 'deadlock.c',
        line: '1155',
        routine: 'DeadLockReport'
    }

The reported SQL statements in my trigger functions are as follows:

UPDATE accounts_master
SET product1_stock = NEW.user_balance,
product1_orders = COALESCE( NEW.daily_orders, NEW.weekly_orders )
WHERE user = NEW.user
PL/pgSQL function updatefunc_aggregation_product1()

UPDATE product1_aggregation
SET daily_orders = daily_orders * NEW.discounts,
last_update = NEW.timestamp
PL/pgSQL function updatefunc_discounts_product1()

There are two questions:

  • Why does the first code block have a SHARE lock on the table? The documentation states that an UPDATE command acquires a ROW EXCLUSIVE lock but Postgres reports a SHARE lock.

  • How can I solve this deadlock? The updatefunc_aggregation_commodity1() only needs to read the data and transfer it to the master table. So my approach would be to explicitly acquire a lower level lock e.g. ROW SHARE that does not conflict with the SHARE lock of updatefunc_discounts_product1().

If someone could give feedback on this approach or even a code sample, I would be very grateful. An improved approach is also welcome.

#Edit1: Adding trigger definitions and a chain of events (which statements fire the triggers, to the best of my knowledge).

Trigger definitions:

updatetrigger_discounts_product1
BEFORE INSERT OR UPDATE 
ON product1_discounts
FOR EACH ROW
EXECUTE FUNCTION updatefunc_discounts_product1();

updatetrigger_aggregation_product1
AFTER INSERT OR UPDATE 
ON product1_aggregation
FOR EACH ROW
EXECUTE FUNCTION updatefunc_aggregation_product1();

updatetrigger_master_update_token
BEFORE UPDATE OF product1_stock, product1_orders, [...]
ON accounts_master
FOR EACH ROW
EXECUTE FUNCTION updatefunc_master_update_cols();

Chain of events:

INSERT OR UPDATE on product1_discounts
-> fires updatetrigger_discounts_product1
-> executes updatefunc_discounts_product1()

    UPDATE on product1_aggregation
    -> fires updatetrigger_aggregation_product1
    -> executes updatefunc_aggregation_product1()
        
        UPDATE on accounts_master
        -> fires updatetrigger_master_update
        -> executes updatefunc_master_update_cols()
        
            UPDATE on accounts_master
            [does not fire another trigger because updatetrigger_master_update only fires on specific columns]
riderinred
  • 53
  • 1
  • 6
  • 1
    The Share lock is not "had", it is "unhad" (waited for) and is not on a table, it is on a transaction. When you need to wait for a row lock to end, you do it by waiting for the transaction locking the row to end. That way every locked row doesn't need a lock-table entry. – jjanes Dec 13 '21 at 19:48
  • 1
    I think we need to know not just the statements inside the triggers, but also the trigger definitions themselves and which statements are firing the triggers. Do you have recursive triggers? (The statement inside one trigger is causing another trigger to fire.) – jjanes Dec 13 '21 at 19:51
  • Will add information on trigger definitions and statements that fire the triggers. Yes, the triggers are recursive. updatefunc_discounts_product1() updates product1_aggregation and ON UPDATE of product1_aggregation a trigger fires updatefunc_aggregation_product1(), this trigger then updates accounts_master with the new values. – riderinred Dec 14 '21 at 15:18
  • @jjanes edit done. What I wanted to achieve was that a trigger makes a transaction from the product1_discounts table to the product1_aggregation table and ends. This transaction then fires another trigger that makes a transaction from the product1_aggregation table to the accounts_master table. IF the triggers do not end as I intended them to, then I can see how that might be blocking them. But I don't know yet how to make them behave as intended. – riderinred Dec 14 '21 at 16:21
  • Spent today researching and thinking about this. So far my best guess for the cause of this deadlock is that the statement in updatefunc_discounts_product1() causes an UPDATE for every row of product1_aggregation. This fires many (at least several thousand) updatetrigger_aggregation_product1 triggers. These all try to UPDATE accounts_master with a WHERE condition and then it deadlocks. Still no idea how/why the updatefunc_discounts_product1() function wants/needs a lock on accounts_master despite not editing it. Any and all help is deeply appreciated. – riderinred Dec 15 '21 at 00:24

0 Answers0