我在添加触发器后遇到了死锁问题。存在一个 UserBalanceHistory
表,其中每个交易都有一行,并且有一个 Amount
列。一个触发器被添加到这个表中,来对 Amount
列求和并将结果放入相关的 User
表中的 Balance
列。
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;
我也已经打开了 READ_COMMITTED_SNAPSHOT
:
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;
我有一个并行进程正在创建UserBalanceHistory
条目,如果它在同一时间处理相同的User
,死锁会发生。有什么建议吗?