SQL Server: Trigger with UPDATE statement causes deadlocks

Question

I have added a trigger to perform a change tracking mechanism on a table that has a relatively high load. During testing, we encountered no issues but now as it is in production there are deadlocks happening from time to time.

There is an old MS Access frontend accessing the database via OLEDB / ADODB that is not fault tolerant to the deadlock which leads to data integrity issues (and we cannot address this in short time as it is not our project).

This is the deadlock graph:

The SQL statements (updates) on the left and right side of the deadlock graph are not within the trigger but of course triggering it. The deadlock occurs on the PK of the change tracking table.

This is the trigger:

ALTER TRIGGER [dbo].[TR_IND_ChangeTracking_OrderingItems_PickOrders]
ON [dbo].[OrderingItems]
FOR UPDATE, INSERT, DELETE
AS 
    DECLARE @InsertedOrderNumber int
    DECLARE @DeletedOrderNumber int

    SELECT @InsertedOrderNumber = OrderNumber FROM inserted
    SELECT @DeletedOrderNumber = OrderNumber FROM deleted

    -- Check if the order exists in IND_PickOrders, otherwise skip
    DECLARE @ExistsInPickOrders bit
    SET @ExistsInPickOrders = 0

    SELECT @ExistsInPickOrders = 1
        FROM [dbo].[IND_PickOrders]
        WHERE Auftragsnummer = @InsertedOrderNumber OR Auftragsnummer = @DeletedOrderNumber

    IF NOT @ExistsInPickOrders = 1
    BEGIN
        RETURN
    END

    DECLARE @Timestamp datetime
    DECLARE @ToUpdate bit
    DECLARE @State int

    SET @Timestamp = GETUTCDATE()

    -- Need to update an existing entity in queue?
    SELECT @ToUpdate = 1 
        FROM [dbo].[ChangeTracking_PickOrders] 
        WHERE OrderNumber = @InsertedOrderNumber OR OrderNumber = @DeletedOrderNumber

    -- There was an action happening, but no value has been inserted or deleted.
    -- I.e. trying to delete an entity that not exists.
    IF @InsertedOrderNumber IS NULL AND @DeletedOrderNumber IS NULL
    BEGIN
        SET @State = 0
    END

    -- Entity has been inserted.
    IF (@InsertedOrderNumber IS NOT NULL AND @DeletedOrderNumber IS NULL)
    BEGIN
        SET @State = 1
    END

    -- Entity has been updated.
    IF @InsertedOrderNumber IS NOT NULL AND @DeletedOrderNumber IS NOT NULL
    BEGIN
        SET @State = 2
    END

    -- Entity has been deleted.
    IF @InsertedOrderNumber IS NULL AND @DeletedOrderNumber IS NOT NULL
    BEGIN
        SET @State = 3
    END

    IF @State > 0
    BEGIN
        IF @ToUpdate = 1
        BEGIN
            -- Update the entity on change tracking table.
            UPDATE [dbo].[ChangeTracking_PickOrders]
                SET UpdateTimestamp = @Timestamp, State = @State
                WHERE OrderNumber = ISNULL(@InsertedOrderNumber, @DeletedOrderNumber)
        END
        ELSE
        BEGIN
            -- Insert if no entry exists.
            INSERT INTO [dbo].[ChangeTracking_PickOrders] (OrderNumber, UpdateTimestamp, State) 
                VALUES (ISNULL(@InsertedOrderNumber, @DeletedOrderNumber), @Timestamp, @State)
        END
    END

The trigger first checks if the order number exists in another table and if not, returns. Then I check if the order number exists in the change tracking table and insert or update the specific row in the change tracking table.

The logic of the Access frontend iterates over a set of ordering items of an order, calculates some fields and updates the order row by row by using an ADORecordSet.

I suspect the Access frontend to not wait until the update and the trigger have completed (releasing the locks) and update the next row from a different connection of the ConnectionPool (thus leading to a differnt server process?).

As me not being a DBA, is there anything I may have overseen? Thank you for any tips in advance!

Edit: The schema of [dbo].[ChangeTracking_PickOrders]

CREATE TABLE [dbo].[ChangeTracking_PickOrders](
    [OrderNumber] [int] NOT NULL,
    [State] [int] NOT NULL,
    [UpdateTimestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_OrderNumber] PRIMARY KEY CLUSTERED 
(
    [OrderNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

Your trigger has a fatal flaw: it only deals with a single insert.

You need to rewrite it to join by the PK of inserted and deleted.

To avoid the deadlock, add both UPDLOCK and HOLDLOCK hints to the first reference to ChangeTracking_PickOrders.

CREATE OR ALTER TRIGGER [dbo].[TR_IND_ChangeTracking_OrderingItems_PickOrders]
ON [dbo].[OrderingItems]
FOR UPDATE, INSERT, DELETE
AS

SET NOCOUNT ON;

-- early bailout
IF NOT EXISTS (SELECT 1
    FROM inserted i
    JOIN dbo.IND_PickOrders po ON po.Auftragsnummer = i.OrderNumber
  ) AND NOT EXISTS (SELECT 1
    FROM deleted d
    JOIN dbo.IND_PickOrders po ON po.Auftragsnummer = d.OrderNumber
  )
        RETURN;


DECLARE @Timestamp datetime = GETUTCDATE();

-- Need to update an existing entity in queue?
UPDATE ct
SET State =
  CASE
    WHEN i.OrderNumber IS NOT NULL AND d.OrderNumber IS NOT NULL
      THEN 2
    WHEN i.OrderNumber IS NULL
      THEN 3
    ELSE 1
  END
FROM dbo.ChangeTracking_PickOrders ct WITH (HOLDLOCK, UPDLOCK)
JOIN (
    inserted i
    FULL JOIN deleted d ON d.OrderNumber = i.OrderNumber
)
  ON ISNULL(i.OrderNumber, d.OrderNumber) = ct.OrderNumber;

-- Insert if no entry exists.
INSERT INTO dbo.ChangeTracking_PickOrders
  (OrderNumber, UpdateTimestamp, State)
SELECT
  ISNULL(i.OrderNumber, d.OrderNumber),
  @Timestamp,
  CASE
    WHEN i.OrderNumber IS NOT NULL AND d.OrderNumber IS NOT NULL
      THEN 2
    WHEN i.OrderNumber IS NULL
      THEN 3
    ELSE 1
  END
FROM inserted i
FULL JOIN deleted d ON d.OrderNumber = i.OrderNumber
WHERE NOT EXISTS (SELECT 1
    FROM dbo.ChangeTracking_PickOrders ct
    WHERE ct.OrderNumber = ISNULL(i.OrderNumber, d.OrderNumber)
);

I'd advise you to consider using CDC or Change Tracking rather than rolling your own, it's much more efficient.

Answer 2

@HardCode is right when they say that updates in a change tracking table are less common, but design choices and performance aside -

The deadlock looks to be occurring between the SELECT statement you use to determine if there is a record to update, and the UPDATE statement you use to update the record if there is one. Since OrderNumber is the primary key on the ChangeTracking table, it should never be updated - either the value exists from inserted, deleted, or both. Instead of comparing them, count them to determine what type of action the trigger is performing. To avoid having those be separate statements that deadlock each other, you could employ a MERGE statement with the HOLDLOCK hint -

ALTER TRIGGER dbo.TR_IND_ChangeTracking_OrderingItems_PickOrders
ON dbo.OrderingItems
FOR UPDATE, INSERT, DELETE
AS
DECLARE @count TABLE
(
    OrderNumber INT
  , State INT
);

INSERT INTO @count
SELECT q.OrderNumber
     , CASE
           WHEN q.insertedCount = q.deletedCount THEN
               2
           WHEN q.insertedCount > q.deletedCount THEN
               1
           WHEN q.insertedCount < q.deletedCount THEN
               3
       END AS State
FROM
(
    SELECT SUM(cnt.insertedCount) AS insertedCount
         , SUM(cnt.deletedCount) AS deletedCount
         , cnt.OrderNumber
    FROM
    (
        SELECT 1 AS insertedCount
             , 0 AS deletedCount
             , i.OrderNumber
        FROM inserted AS i
        UNION ALL
        SELECT 0 AS insertedCount
             , 1 AS deletedCount
             , d.OrderNumber
        FROM deleted AS d
    ) cnt
    GROUP BY cnt.OrderNumber
) AS q;

-- Check if the order exists in IND_PickOrders, otherwise skip
DECLARE @ExistsInPickOrders BIT = 0;

SELECT @ExistsInPickOrders = 1
FROM dbo.IND_PickOrders AS ind_po WITH (NOLOCK)
WHERE EXISTS
(
    SELECT 1 FROM @count AS c WHERE ind_po.Auftragsnummer = c.OrderNumber
);

IF @ExistsInPickOrders = 0
BEGIN
    RETURN;
END;

DECLARE @Timestamp DATETIME = GETUTCDATE();

-- Need to update an existing entity in queue?
MERGE INTO ChangeTracking_PickOrders WITH (HOLDLOCK) AS ct_po
USING @count AS c
ON ct_po.OrderNumber = c.OrderNumber
-- Update the entity on change tracking table.
WHEN MATCHED THEN
    UPDATE SET ct_po.UpdateTimestamp = @Timestamp
             , ct_po.State = c.State
-- Insert if no entry exists.
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (
        OrderNumber
      , UpdateTimestamp
      , State
    )
    VALUES
    (c.OrderNumber, @Timestamp, c.State);

The HOLDLOCK hint on the table being merged into will ensure atomicity through the entirety of the merge.

Alternatively, you could simply put a WITH (NOLOCK) hint on the portion of the code that determines if there is a record to update, but that can lead to dirty reads and some concurrency issues -

...
SET @Timestamp = GETUTCDATE();

-- Need to update an existing entity in queue?
SELECT @ToUpdate = 1
FROM dbo.ChangeTracking_PickOrders WITH (NOLOCK)
WHERE OrderNumber = @InsertedOrderNumber
      OR OrderNumber = @DeletedOrderNumber;
...

Documentation for MERGE
Documentation for HOLDLOCK, NOLOCK