CODEWITHSUNDEEP
sql-servertriggersdeadlock
Raiden616
Raiden616

Reputation: 1564

T-SQL Triggers performing insert causing deadlock

I have an SQL table called Agent_Events, which logs "jobs" for lack of a better term reported by users of our system. Jobs will start and, some indetermine time later, end. We want to keep a record of all system jobs so we have a trigger that, when a row is deleted from the Agent_Events table, gets the record, and inserted into the report_Agent_Events table a record of the job with the time the job ended, determined by the current time. This is the trigger:

ALTER TRIGGER [dbo].[tAgentEvents]
ON [dbo].[Agent_Events]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON  
    DECLARE @callUID nvarchar(10)
    SELECT @callUID = [raw_call_ucid] FROM DELETED
    DECLARE @callID bigint
    SELECT TOP 1 @callID = [ID] FROM report_ExtensionCalls WHERE UCID = @callUID ORDER BY [time_finished] DESC

    INSERT INTO [report_agent_Events]
        ([Agent_Name],[time_started],[time_ended],[duration],[clientStatus],[agentStatus],[Call_ID],[Reference_ID])
        (   SELECT
                [Agent_Name],[time_started],GETDATE(),DATEDIFF(s,[time_started],GETDATE()),[clientStatus],[agentStatus],@callID as Call_ID,[Reference_ID]
                    FROM
                DELETED
        )
END

What we have discovered recently is that when there is a high volume of activity on the system, multiple jobs could end simultaneously. When this happens, the trigger will fail with the following deadlock error:

Procedure tAgentEvents:
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim RSS

It looks like the INSERT statement in the trigger is conflicting with the lock imposed by the same INSERT statement in a concurrent trigger. Is there anything I can do to avoid this problem? This appears to me like a very simple trigger.

Thanks in advance.

Upvotes: 1

Views: 953

Answers (2)

M. Grue
M. Grue

Reputation: 331

Just updated the trigger to make it work in one go, with multiple lines deleted and with multiple matches in report_ExtensionCalls.

ALTER TRIGGER [dbo].[tAgentEvents]
ON [dbo].[Agent_Events]
AFTER DELETE
AS
BEGIN
    WITH CTE AS (
        SELECT d.Id AS DeletedId
            , rec.[ID]
            , ROW_NUMBER() OVER (PARTITION BY rec.UCID ORDER BY rec.[time_finished] DESC) AS RowN
        FROM DELETED AS d
        JOIN report_ExtensionCalls AS rec ON d.[raw_call_ucid] = rec.UCID
    )
    INSERT INTO [report_agent_Events]
        ([Agent_Name],[time_started],[time_ended],[duration],[clientStatus],[agentStatus],[Call_ID],[Reference_ID])
        (   SELECT
                d.[Agent_Name]
                ,d.[time_started]
                ,GETDATE()
                ,DATEDIFF(s,d.[time_started],GETDATE())
                ,d.[clientStatus]
                ,d.[agentStatus]
                ,a.[ID] as Call_ID
                ,d.[Reference_ID]
                    FROM DELETED AS d
                    LEFT JOIN CTE AS a ON d.Id = a.DeletedId AND a.RowN = 1
        )
END

Hope it helps

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

This should accommodate all the business rules in your original trigger but without scalar variables.

Please note, this is incredibly unlikely to solve the deadlock issue. You still need to sort that out.

INSERT INTO [report_agent_Events]
(
    [Agent_Name]
    , [time_started]
    , [time_ended]
    , [duration]
    , [clientStatus]
    , [agentStatus]
    , [Call_ID]
    , [Reference_ID]
)
SELECT [Agent_Name]
    , [time_started]
    , GETDATE()
    , DATEDIFF(SECOND, [time_started], GETDATE())
    , [clientStatus]
    , [agentStatus]
    , raw_call_ucid
    , [Reference_ID]
FROM DELETED d
join
(
    select top 1 ID as CallID
    from report_ExtensionCalls ec
    where ec.UCID = d.raw_call_ucid
    order by [time_finished] DESC
) e on e.CallID = d.raw_call_ucid

Upvotes: 1

Related Questions