Trigger causing a deadlock?

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(@Bala开发者_开发百科nce, 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?

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.

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;

Change the clustered key to userid in your UserBalanceHistory table and drop the non-clustered index because you are using userid to access the table there is no reason to use an identity column for the clustered index as it will always force the non-clustered index to be used and then a read from the clustered index to the change the money value. Clustered indexes are best for range searches which is what you are doing when you sum the balance. Your present situation may cause SQL to request every data page in the table just to get the user payments, some fragmentation in the clustered index is offset by the contigiously(sp) linked pages for a single userid. Changing the cluster and dropping the non-cluster will save time and memory.
Do not run any stored proc from the trigger because it will lock the triggered table while the SP finishes.

The balance table could be made from a view with a computed column (SO link here) on the UserBalanceHistory table .

Test in a development system, and then test again!