触发器引起死锁？

Question

我在添加触发器后遇到了死锁问题。存在一个 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，死锁会发生。有什么建议吗？

- Josh M.

---

我希望你能理解，你的触发器代码非常糟糕和危险。你永远不能假设插入或删除中只有一条记录。这段代码会在第一次有人需要运行基于集合的插入（例如从新客户导入历史数据）时造成严重的数据完整性问题。 - HLGEM

好的，很好的建议 - 我会修复它。 - Josh M.

Answer 1

这是一个古老的问题，但我认为我刚找到了答案，如果其他人遇到了同样的问题，这肯定是我的答案。

问题可能是UserBalanceHistory和User之间存在外键约束。在这种情况下，对UserBalanceHistory的两个并发插入可能会发生死锁。

这是因为在对UserBalanceHistory进行插入时，数据库将在User上获取共享锁以查找FK的ID。然后当触发器触发时，它将在User上获取独占锁。

如果这发生在并发情况下，它就是经典的锁升级死锁，其中两个事务都无法升级到独占锁，因为另一个持有共享锁。

我的解决方案是在更新和插入时无端地加入到User表中，并在该表上使用WITH（UPDLOCK）提示。

- Darren Clark

Answer 2

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

根本原因可能是对UserID和Amount的UserBalanceHistory扫描。我将在UserBalanceHistory上添加一个索引（UserID）包括（Amount）来更改此内容。

快照隔离模型仍然可能死锁：存在示例（例如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;

- gbn

---

我已经实现了这个，但仍然出现死锁。 - Josh M.

@Josh M.：相同的死锁图？带索引？带触发器？ - gbn

是的，我实际上已经有了索引，但是忘记在我的问题中包含它。 - Josh M.

Answer 3

将UserBalanceHistory表中的聚集键更改为userid，并删除非聚集索引，因为您正在使用userid访问表，所以没有理由使用标识列作为聚集索引，因为它将始终强制使用非聚集索引，然后从聚集索引读取以更改金额值。当您对余额求和时，聚集索引最适合范围搜索。您目前的情况可能会导致SQL请求表中的每个数据页，以获取用户付款，聚集索引中的一些碎片化通过单个userid链接的页面进行抵消。更改聚集并删除非聚集将节省时间和内存。
不要从触发器运行任何存储过程，因为它会在SP完成时锁定触发的表。

余额表可以从UserBalanceHistory表上的计算列视图（SO链接here）中制作。

在开发系统中进行测试，然后再次测试！

- RC_Cleland