Sample Header Ad - 728x90

SQL Server: Trigger with UPDATE statement causes deadlocks

2 votes
2 answers
373 views
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](https://i.imgur.com/2CZZmm0.png) 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:
sql
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
Asked by JannikB (21 rep)
Nov 13, 2023, 03:39 PM
Last activity: Nov 16, 2023, 08:16 AM