This question was
closed
by
nidheesh.r.pillai for the following reason: The question is answered, right answer was accepted
asked
Trigger optimization so as to prevent deadlocks
I have been observing a trigger (Type = AFTER UPDATE) in our application that occasionally fails due to deadlock on lock resources. Here is a sample error message. Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I thought of studying the trigger. The logic is that if the primary table has an update performed, then all child tables (20 of them) have soft deleted rows such that only one of them remains the latest (deleted = 0) and others are marked (deleted > 0), such that they can be hard-deleted later in the day when ETL cleanups run. I do see that the trigger is a big one with over 20 tables being affected after the primary table on which the trigger is created is updated (with at most 100K rows), and that the COMMIT to all these tables happen only in the end. Below is a simplified sample version of the Trigger with only the essential edits CREATE TRIGGER [dbo].[TRG_OG_Update] ON [dbo].[og] AFTER UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @MaxDeleted TINYINT BEGIN TRY IF UPDATE(Deleted) BEGIN BEGIN TRANSACTION -- MTS SELECT @MaxDeleted = MAX(mts.Deleted) FROM ov INNER JOIN mov ON
ov.ID = mov.OVID INNER JOIN mts ON
mov.ID = mts.MOVID WHERE
ov.ID IN (SELECT ID FROM INSERTED) --For undelete (deleted = 0 in Inserted) UPDATE mts SET Deleted = 0 FROM Inserted i INNER JOIN ov ON
i.ID =
ov.ID INNER JOIN mov ON
ov.ID = mov.OVID INNER JOIN mts ON
mov.ID = mts.MOVID WHERE mts.Deleted = @MaxDeleted AND i.Deleted = 0 --For delete (deleted <> 0 in Inserted) SET @MaxDeleted = @MaxDeleted + 1 UPDATE mts SET Deleted = @MaxDeleted FROM Inserted i INNER JOIN ov ON
i.ID =
ov.ID INNER JOIN mov ON
ov.ID = mov.OVID INNER JOIN mts ON
mov.ID = mts.MOVID WHERE mts.Deleted = 0 AND i.Deleted <> 0 /******************* SIMILAR 3 STEP LOGIC FOR 20 different tables WITH @MaxDeleted being reused as above ******************/ COMMIT TRANSACTION END END TRY BEGIN CATCH IF @@TranCount > 0 ROLLBACK TRANSACTION END CATCH END GO I think that since the trigger remains open for a long period of time, thus the resources are held thereby creating a deadlock. I'm trying to find out a way to optimize this trigger such that it completes fast and returns the resources without holding them up for long. Any ideas on how to begin with? I was thinking of using the Declarative Referential Integrity (DRI) approach, is it applicable to the situation?
Your trigger has two MAJOR flaws. First it is doing logic using UPDATE(SomeColumn). This only means that the column is in the update statement, it does NOT mean that the value actually changed. The other problem which is far more serious is your trigger code assumes there will only ever be a single row in inserted. This is not how triggers work. You need to write proper set based logic that can handle multiple row operations.
Honestly....this trigger needs to be thrown away. There are more things wrong than right in here. I somehow managed to gloss over how awful it is to have transactions in a trigger. If your calling code also a transaction it will break because the trancount will be off when exiting your trigger. I can't even begin to say what should be done because it is hard to tell what this trigger is trying to accomplish. I would start with an empty query window and ask the business unit what is trying to be done.
17 People are following this question.
