1

I am using Microsoft SQL Server 2019 with an UPDATE trigger that reads the table that is being updated. If the trigger is called simultaneously, there will be a deadlock situation.

The trigger roughly looks like this (This is a very simplified design of the query. I didn't want to include too much unnecessary information in this question):

ALTER TRIGGER [dbo].[trg_ORDERS] 
   ON [dbo].[ORDERS] 
   AFTER update 
AS  
BEGIN 
    update tour
    set
        tour.COUNT_PLACES = sum(ord....),
        tour.COUNT_CUSTOMERS = sum(ord.....),
        tour.WEIGHT_KILOGRAMS = sum(ord......),
        ...
    from
        TOURS tour
    left join
        ORDERS ord on tour.TOUR_ID = ord.TOUR_ID
    where
        ord.TOUR_ID > 0 and ord.CANCELLED = 0
END;

Rough concept:

  • The ORDERS table contains orders from customers.
  • The TOURS table contains data about the tours, for example, the truck, the driver, and the delivery date. In addition, it contains things like the weight of all orders.

Problem:

  • This trigger works if it is used by a single user.
  • As soon as two workstations execute an "update ORDERS ...." query at the same time, there will be a deadlock situation.
  • As far as I understand, the deadlock simulation comes because "update ORDERS" locks the table, and "select ... left join ORDERS" (full table scan) locks the table, too. Therefore, the two triggers conflict.

Is there anything I can do, to solve the deadlock situation?

An idea I had is to pull out things like weight into a VIEW while keeping other data (driver, truck etc.) in the TOURS table. However, this would impact the performance very much since the VIEW would cause a full table scan on the ORDERS table, every time it is accessed. I would highly prefer to have things like the weight-sum in the TOURS table, if possible.

I know about the inserted and deleted tables. However, I want to read the whole table, not just the updated rows. For example, an order is added to an existing tour. Then the inserted table would only contain the added order. But I want to accumulate all other orders of the same tour, so I can determine the weight of the updated tour.

4
  • 2
    Could you not just add the value of the inserted record to the current value in tour? Shouldn’t be a need to re-sum the whole table each time, you just need to know the difference. Commented Feb 8, 2022 at 22:51
  • 3
    But I want to accumulate all other orders of the same tour No you don't. You simply need to "adjust" something (not sure what) by subtracting the values in the deleted table and adding the values in the inserted table. And it seems impossible to need a left join at all here. If a row in Order does not join to Tour then there is nothing to update.
    – SMor
    Commented Feb 9, 2022 at 3:21
  • 1
    Unless I misunderstood something, instead of calculating and storing tour.WEIGHT_KILOGRAMS = sum(ord......) as such, why not use a view to define this logic instead? It'll always be current on the accumulation calculation when needed to be read from and you won't have to waste resources materializing the accumulation via a trigger anymore, which appears to be the source of where your deadlock is happening.
    – J.D.
    Commented Feb 9, 2022 at 4:17
  • Sounds like you should use an aggregated view instead. Perhaps create an indexed view if you want to improve performance. Don't denormalize by storing the same data in multiple places Commented Feb 9, 2022 at 14:24

1 Answer 1

3

Is there anything I can do, to solve the deadlock situation?

If you want to stick with the trigger approach, use an INSTEAD OF trigger (or a stored procedure), not an AFTER trigger. By the time an AFTER trigger runs, the concurrent session already owns the incompatible lock and it's too late to prevent the deadlock.

In the trigger update both tables using an exclusive table lock or an application lock to ensure that only one session can run the trigger at a time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.