Déclencheur provoquant une impasse?

Question

Je suis confronté à une impasse après avoir ajouté un déclencheur. Il y a une table UserBalanceHistory qui a une ligne pour chaque transaction et une colonne Amount. Un déclencheur a été ajouté pour faire la somme de la colonne Amount et placer le résultat dans la table User associée, colonne 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;

J'ai également activé READ_COMMITTED_SNAPSHOT :

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;

J'ai un processus parallèle en cours qui crée des entrées UserBalanceHistory, apparemment si cela fonctionne sur le même User en même temps, l'impasse se produit. Des suggestions ?

Answer 1

Ancienne question, mais je pense avoir trouvé la réponse si quelqu'un d'autre tombe dessus. C'était certainement la réponse pour moi.

Le problème est probablement qu'il existe une contrainte FK entre UserBalanceHistory et User. Dans ce cas, deux inserts concurrents dans UserBalanceHistory peuvent entraîner un deadlock.

Cela est dû au fait que lors de l'insertion dans UserBalanceHistory, la base de données prendra un verrou partagé sur User pour rechercher l'ID de la FK. Ensuite, lorsque le déclencheur se déclenche, il prendra un verrou exclusif sur User.

Si cela se produit de manière concurrente, c'est un deadlock d'escalade de verrou classique, où aucune transaction ne peut basculer vers un verrou exclusif car l'autre détient un verrou partagé.

Ma solution a été de joindre de manière délibérée la table User sur les mises à jour et les inserts et d'utiliser un indice WITH (UPDLOCK) sur cette table.

Answer 2

Le deadlock se produit car vous accédez à UserBalanceHistory -> UserBalanceHistory -> Utilisateur alors qu'une autre mise à jour est en cours sur Utilisateur -> HistoriqueSoldeUtilisateur. C'est plus complexe en raison de la granularité du verrouillage et des verrous d'index, etc.

La cause root est probablement un scan sur UserBalanceHistory pour UserID et Amount. J'aurais un index sur (UserID) INCLUDE (Amount) sur UserBalanceHistory pour changer cela

Les modèles d'isolation SNAPSHOT peuvent quand même entraîner des deadlocks : il y a des exemples là-bas (Un, Deux

Enfin, pourquoi ne pas tout faire en une seule fois pour éviter des chemins de mise à jour différents et multiples?

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;

Answer 3

Modifiez la clé groupée en userid dans votre table UserBalanceHistory et supprimez l'index non regroupé car vous utilisez userid pour accéder à la table, il n'y a aucune raison d'utiliser une colonne d'identité pour l'index groupé car cela forcera toujours l'utilisation de l'index non regroupé puis une lecture de l'index groupé pour changer la valeur de l'argent. Les index groupés sont les meilleurs pour les recherches par plage, c'est ce que vous faites lorsque vous additionnez le solde. Votre situation actuelle peut amener SQL à demander chaque page de données de la table simplement pour obtenir les paiements des utilisateurs, une certaine fragmentation dans l'index regroupé est compensée par les pages liées de manière contiguë pour un seul userid. Changer le regroupement et supprimer le non-regroupé vous fera gagner du temps et de la mémoire.
Ne lancez aucune procédure stockée à partir du déclencheur car cela verrouillera la table déclenchée pendant que la procédure stockée se termine.

La table de solde pourrait être créée à partir d'une vue avec une colonne calculée (lien SO ici) sur la table UserBalanceHistory.

Testez dans un système de développement, et testez à nouveau!