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:

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

• HardCode: 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.
• JannikB: @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.

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.

• Learning_DBAdmin: +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.

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

• Charlieface: Why NOLOCK that's going to cause all manner of incorrect results.
• Caleb Carl: 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.