2

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:

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
2
  • 2
    It is more common for a change tracking table to simply INSERT a new row each time a row in the auditee table is inserted or updated. Is there any particular reason you must update rows in the change tracking table vs adding a new row? In your current configuration, you don't really have a change tracking table. You lose history once a row is updated.
    – HardCode
    Nov 13, 2023 at 16:11
  • @HardCode That is actually my plan if we cannot get the inteded behaviour to work. I do not care about the history, I just need a representation of the rows that have been updated for other systems.
    – JannikB
    Nov 14, 2023 at 7:25

2 Answers 2

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.

1
  • +1, If you are not using inserted or deleted table in trigger then how will you ensure that the operation is happening on only modified row and not to whole table. Nov 16, 2023 at 10:51
0

@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

2
  • Why NOLOCK that's going to cause all manner of incorrect results. Nov 15, 2023 at 13:50
  • Not necessarily - If you consider the logic, the trigger will either update the record or insert it. NOLOCK on that statement isn't grabbing data, just determining if the record is there to be updated or not. If OrderNumber is truly a PK, then every OrderNumber is guaranteed unique and won't be the same for two different records across two different transactions. One process would not be modifying the record before the process creating it completes.
    – Caleb Carl
    Nov 15, 2023 at 15:43

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.