Trigger causing a deadlock?

Question

I'm running into a deadlock after I added a trigger. There is a UserBalanceHistory table which has one row for each transaction and an Amount column. A trigger was added to sum the Amount column and place the result in the related User table, Balance column.

CREATE TABLE [User]
(
    ID INT IDENTITY,
    Balance MONEY,
    CONSTRAINT PK_User PRIMARY KEY (ID)
);

CREATE TABLE UserBalanceHistory
(
    ID INT IDENTITY,
    UserID INT NOT NULL,
    Amount MONEY NOT NULL,
    CONSTRAINT PK_UserBalanceHistory PRIMARY KEY (ID),
    CONSTRAINT FK_UserBalanceHistory_User FOREIGN KEY (UserID) REFERENCES [User] (ID)
);

CREATE NONCLUSTERED INDEX IX_UserBalanceHistory_1 ON UserBalanceHistory (UserID) INCLUDE (Amount);

CREATE TRIGGER TR_UserBalanceHistory_1 ON UserBalanceHistory AFTER INSERT, UPDATE, DELETE AS
BEGIN
    DECLARE @UserID INT;

    SELECT TOP 1 @UserID = u.UserID
    FROM
    (
            SELECT UserID FROM inserted
        UNION
            SELECT UserID FROM deleted
    ) u;

    EXEC dbo.UpdateUserBalance @UserID;
END;

CREATE PROCEDURE UpdateUserBalance
    @UserID INT
AS
BEGIN
    DECLARE @Balance MONEY;

    SET @Balance = (SELECT SUM(Amount) FROM UserBalanceHistory WHERE UserID = @UserID);

    UPDATE [User]
    SET Balance = ISNULL(@Balance, 0)
    WHERE ID = @UserID;
END;

I've also turned on READ_COMMITTED_SNAPSHOT:

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;

I have a parallel process running which is creating UserBalanceHistory entries, apparently if it is working on the same User at the same time, the deadlock occurs. Suggestions?

Answer 1

The deadlock happens because you are accessing UserBalanceHistory -> UserBalanceHistory -> User whereas some other update is User -> UserBalanceHistory. It's more complex than that because of lock granularity and index locks etc.

The root cause is probably a scan on UserBalanceHistory for UserID and Amount. I'd have an index on (UserID) INCLUDE (Amount) on UserBalanceHistory to change this

SNAPSHOT isolation models can still deadlock: there are examples out there (One, Two

Finally, Why not do it all in one to avoid different and multiple update paths?

CREATE TRIGGER TR_UserBalanceHistory_1 ON UserBalanceHistory AFTER INSERT, UPDATE, DELETE AS
BEGIN
    DECLARE @UserID INT;

    UPDATE U
    SET Balance = ISNULL(t2.Balance, 0)
    FROM
       (
         SELECT UserID FROM INSERTED
         UNION
         SELECT UserID FROM DELETED
       ) t1
       JOIN
       [User] U ON t1.UserID = u.UserID
       LEFT JOIN
       (
        SELECT UserID, SUM(Amount) AS Balance
        FROM UserBalanceHistory
        GROUP BY UserID
       ) t2 ON t1.UserID = t2.UserID;

END;

Answer 2

Way old question, but I think I just found the answer if anyone else comes across it. Certainly was the answer for me.

The issue is probably that there is a FK constraint between UserBalanceHistory and User. In this case two concurrent inserts to UserBalanceHistory can deadlock.

This is because on the insert to UserBalanceHistory the database will take a shared lock on User to lookup the ID for the FK. Then when the trigger fires, it will take an exclusive lock on User.

If this happens concurrently, it's a classic lock escalation deadlock, where neither transaction can escalate to an exclusive lock because the other is holding a shared lock.

My solution was to gratuitously join to the User table on updates and inserts and use a WITH (UPDLOCK) hint on that table.