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?
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;