deadlock issues with triggers

I've been getting deadlock errors in my live app, and have traced them (using sql server profiler's "deadlock graph") to after insert triggers defined on my tables.

basically the scenario is like so- I want to keep track of records inserted into certain tables, grouped by a time frame. (i.e between 12:00-12:10, 7 records were inserted into Users).

The way I've implemented it was to create after insert triggers on those tables, so when a record is inserted, I'd update the appropriate record in my statistics table. (see below).

As i've said, this seems to create deadlock situations. What happens is (I think, I haven't found a way to be sure about that) is that each transaction may insert / update开发者_高级运维 several records in several tables before commiting.

So transaction 1 comes along, updates a certain record in the statistics table (thus locking it), and goes on to update a record in table B.

Meanwhile, transaction 2 inserts a record into table B (thus locking it), and attempts to update a record in the statistics table- resulting in a deadlock.

(this is of course a very simplified version of what could be happening. In reality I'm not 100% certain yet).

Now my initial thought was to see whether it's possible to have the trigger execute after the commit, so that the transaction no longer holds any locks.

but, as far as I could figure out, there's no such option.

another solution would be to eliminate the triggers altogether, and use some kind of batch job instead.

any other ideas / thoughts about preferred solution would be welcome.

trigger code:

SELECT  @TimeIn = I.TimeIn,
    @TimeOut = I.[TimeOut],
    FROM    Inserted AS I

        SET     @NoOfPAX = 1

        SET     @Day = DATEADD(dd,0,DATEDIFF(dd,0,@TimeOut))
        SET     @HourOfTheDay = DATEPART (HOUR, @TimeOut) 
        SET     @MinuteOfTheHour = DATEPART (MINUTE, @TimeOut)  

        SELECT  @HourlyStatsExists = COUNT(*)
        FROM    dbo.DataWarehouse_HourlyStats
        WHERE   [Day] = @Day
            AND HourOfTheDay = @HourOfTheDay
            AND MinuteOfTheHour = @MinuteOfTheHour

        IF      @HourlyStatsExists = 0
        BEGIN

                INSERT INTO dbo.DataWarehouse_HourlyStats
                (
                 HourOfTheDay, 
                 MinuteOfTheHour, 
                 [Day], 
                 Total
                )
                VALUES (
                 @HourOfTheDay, 
                 @MinuteOfTheHour, 
                 @Day, 
                 @NoOfPAX 
                )

        END
        ELSE
        BEGIN    


                UPDATE  DataWarehouse_HourlyStats
                SET     Total = Total + @NoOfPAX,
                        LastUpdate = GetDate()

                WHERE   [Day] = @Day
                    AND HourOfTheDay = @HourOfTheDay
                    AND MinuteOfTheHour = @MinuteOfTheHour

        END

The worst thing you were doing was getting an extremely expensive count just to test for existence. This is costly and unnecessary - especially since you were using that to decide if you were going to update OR insert. In a multi-row insert case, you may need to do both.

Step 1. Update the ones that exist

;WITH x AS 
(
    SELECT d = DATEADD(DAY, 0, DATEDIFF(DAY, 0, i.TimeOut)),
           h = DATEPART(HOUR,   i.TimeOut),
           m = DATEPART(MINUTE, i.Timeout)
    FROM inserted
),
y AS 
(
    SELECT d, h, m, Total = COUNT(*)
    FROM x GROUP BY d, h, m
)
UPDATE h
    SET Total += y.Total,
    LastUpdate = CURRENT_TIMESTAMP        
FROM dbo.DataWarehouse_HourlyStats AS h
INNER JOIN y
    ON h.[Day] = y.d
    AND h.HourOfTheDay = y.h
    AND h.MinuteOfTheHour = y.m;

Step 2. Insert the ones that don't

;WITH x AS 
(
    SELECT d = DATEADD(DAY, 0, DATEDIFF(DAY, 0, i.TimeOut)),
           h = DATEPART(HOUR,   i.TimeOut),
           m = DATEPART(MINUTE, i.Timeout)
    FROM inserted
),
y AS 
(
    SELECT d, h, m, Total = COUNT(*)
    FROM x WHERE NOT EXISTS
    (
      SELECT 1 FROM dbo.DataWarehouse_HourlyStats
      WHERE [Day] = x.d
      AND HourOfTheDay = x.h
      AND MinuteOfTheHour = x.m
    ) 
    GROUP BY d, h, m
)
INSERT dbo.DataWarehouse_HourlyStats
(
    HourOfTheDay, 
    MinuteOfTheHour, 
    [Day],
    Total
)
SELECT h,m,d,Total
    FROM y;

It looks like more code but I assure you this is more efficient and more accurate than your existing version.

That said, it is going to be a pretty expensive trigger regardless if you have a lot of inserts. I hope at the very least there is a good supporting index on the Day, Hour, Minute columns.

Maybe better to compile the stats using an hourly or daily batch job. The trigger itself is part of the transaction by definition, so you cannot delay the commit unless you just stuff the data into a queue or background table and have some other job fix it.