0

I have a After Update trigger where trigger insert a value in same table to find a duration column.My goal is whenever end_time value is inserted then trigger will find duration in hh:mm:ss format and insert into duration column.

Problem- Trigger causes so many deadlock therefore blocking another process.

Column datatype informatiom..

Start_time datetime, End_time datetime, Duration time(3)

ALTER TRIGGER [dbo].[GET_DURATION] ON [dbo].[INBOUND_CALL_xxx]
AFTER UPDATE AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Begin Transaction

               UPDATE INBOUND_CALL_xxx
               SET DURATION = convert(time(0),(END_TIME- START_TIME))                                                     
               FROM INBOUND_CALL_xxx with (nolock)
Commit;
10

You are missing the reference to the special table inserted which holds just the rows that were updated. Currently your trigger is updating all the rows of the tracking table, every time.

ALTER TRIGGER [dbo].[GET_DURATION] ON [dbo].[INBOUND_CALL_xxx]
AFTER UPDATE AS
BEGIN

    IF UPDATE(END_TIME) OR UPDATE(START_TIME)
    BEGIN

               UPDATE C
               SET DURATION = convert(time(0),(I.END_TIME - I.START_TIME))                                                     
               FROM 
                inserted AS I 
                INNER JOIN INBOUND_CALL_xxx AS C ON I.KeyColumn = C.KeyColumn

    END

END

You can also modify the trigger to update the DURATION only if the relevant columns were updated (as of this example).


Another option would be using a computed column (maybe persisted) with an expression of CONVERT(time(0), END_TIME - START_TIME), so you don't need a trigger to keep the values updated.

ALTER TABLE INBOUND_CALL_xxx DROP COLUMN DURATION

ALTER TABLE INBOUND_CALL_xxx ADD DURATION AS CONVERT(TIME(0), END_TIME - START_TIME) -- PERSISTED (if needed)
  • This server role as a replication subscriber which is also part of alwayson availbility group primary server that's why i am unable to use computed column. – Gulrez Khan Jun 28 '18 at 10:53
3

It seems that you are updating the WHOLE table every time there is an update. You should refer to Inserted and Deleted to only change the duration for records that were actually changed. I believe this is the main problem here.

Begin transaction / commit is unnecessary here if you only have a single update in this trigger, as the UPDATE alone is an atomic query thus cannot only partially succeed or fail.

If after resolving this issue you keep getting deadlocks then you might also review the other code that updates this data and/or consider changing isolation level to READ COMMITTED SNAPSHOT ISOLATION.

  • Read_Committed_snapshot will increase load on CPU. – Gulrez Khan Jul 3 '18 at 10:08
  • @GulrezKhan 1. Even if it does, would this be of any issue to you (keeping in mind that it would actually take most of deadlock issues away) ? 2. Do you have numbers to back that up that this would be an issue or just something you heared ? – Błażej Ciesielski Jul 4 '18 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.