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