Déclencheur provoque un blocage?

Je suis en cours d'exécution dans une impasse après j'ai ajouté un déclencheur. Il y a un UserBalanceHistory table qui a une ligne pour chaque transaction et un Amount colonne. Un déclencheur a été ajouté à la somme Amount colonne et placer le résultat dans le User table, Balance colonne.

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 tourné sur READ_COMMITTED_SNAPSHOT:

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;

J'ai un parallèle processus en cours d'exécution qui est de la création de UserBalanceHistory entrées, apparemment, si elle est de travailler sur le même User dans le même temps, le blocage se produit. Des Suggestions?

Je veux que vous compreniez que votre code de déclencheur est extrêmement pauvre et dangereux. Vous ne pourrez JAMAIS faire l'hypothèse qu'il n'y aura qu'un seul enregistrement dans insérées ou supprimées. Ce code pourrait causer beaucoup de problèmes d'intégrité des données la première fois que quelqu'un nécessaires à l'exécution d'un ensemble basé sur insérer (comme inmporting données historiques à partir d'un nouveau client)
Yep, bon je vais corriger ça.

OriginalL'auteur Josh M. | 2011-06-08

2

Le blocage se produit parce que vous accédez à UserBalanceHistory -> UserBalanceHistory -> l'Utilisateur tandis que d'autres mise à jour de l'Utilisateur -> UserBalanceHistory. Il est plus complexe que cela, car de verrouillage de granularité et de l'indice de serrures, etc.

La cause est probablement un scan sur UserBalanceHistory pour l'id utilisateur et le Montant. J'aimerais avoir un indice sur (UserID) INCLUDE (Amount) sur UserBalanceHistory de modifier cette

Isolement d'INSTANTANÉ modèles peuvent encore impasse: il existe de nombreux exemples (Un, Deux

Enfin, Pourquoi ne pas tout faire en une seule afin d'éviter diverses et multiples chemins de mise à jour?
```
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;
```
J'ai mis en place cela, mais je suis toujours dans une impasse.
M.: même graphique de blocage? Avec l'index? Avec déclencheur?
Oui. J'ai effectivement déjà eu l'index en place, mais a négligé de les inclure dans ma question.

OriginalL'auteur gbn
0

Changer la clé cluster de nom d'utilisateur dans votre UserBalanceHistory de la table et de la chute de l'index non cluster parce que vous êtes à l'aide de l'id utilisateur pour accéder à la table il n'y a pas de raison d'utiliser une colonne d'identité pour l'index cluster comme il le fera toujours la force de l'index non cluster à être utilisé et ensuite une lecture de l'index cluster pour le changement de la valeur de l'argent. Les index en cluster sont les meilleurs pour faire des recherches, qui est ce que vous faites lorsque vous somme de la balance. Votre situation actuelle risque de SQL à la demande de chaque page de données dans la table, juste pour obtenir les paiements des utilisateurs, de la fragmentation dans l'index cluster est compensée par la contigiously(sp) pages liées pour un seul identifiant. La modification de la grappe et de l'abandon de la non-cluster permettra d'économiser du temps et de la mémoire.

Ne lancez pas de procédures stockées de la gâchette, car il permet de verrouiller le déclenchée table tout en la SP de finitions.

Le solde de ce tableau peut être faite à partir d'un point de vue avec une colonne calculée (DONC, le lien ici) sur la UserBalanceHistory table .

Test dans un système de développement, puis de nouveau le test de!

OriginalL'auteur RC_Cleland
0

Façon vieille question, mais je pense que je viens de trouver la réponse si quelqu'un d'autre vient à travers elle. Certainement la réponse était pour moi.

Le problème est probablement qu'il y a un FK contrainte entre UserBalanceHistory et de l'Utilisateur. Dans ce cas, deux insertions simultanées à UserBalanceHistory peut impasse.

C'est parce que sur l'insert pour UserBalanceHistory la base de données sera un verrou partagé sur l'Utilisateur pour la recherche de l'ID de la FK. Puis, quand le déclencheur est activé, il va prendre un verrou exclusif sur l'Utilisateur.

Si cela se produit simultanément, c'est un classique de l'escalade de verrous de blocage, où ni la transaction peuvent dégénérer en un verrou exclusif parce que l'autre est la tenue d'un verrou partagé.

Ma solution a été de gratuitement se joindre à la table Utilisateur sur des mises à jour et les insertions et les utiliser AVEC (UPDLOCK) conseil sur la table.

OriginalL'auteur Darren Clark

Vous devez vous connecter pour publier un commentaire.