sql-server - 触发导致死锁？

添加触发器后，我陷入了僵局。有一个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上运行，则会发生死锁。有什么建议吗？

最佳答案

死锁的发生是因为您正在访问UserBalanceHistory-> UserBalanceHistory-> User，而其他一些更新是User-> UserBalanceHistory。由于锁的粒度和索引锁等原因，它比这更复杂。

根本原因可能是在UserBalanceHistory上扫描了UserID和Amount。我将在UserBalanceHistory的(UserID) INCLUDE (Amount)上建立一个索引来更改此设置

SNAPSHOT隔离模型仍然可能陷入僵局:那里有示例(One，Two

最后，为什么不做一个整体来避免不同和多个更新路径？

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;