Je me request si je suis la bonne approche et ai besoin de votre aide pour comprendre
Voici ma requête non protégée
DECLARE @cl_WordId bigint = NULL SELECT @cl_WordId = cl_WordId FROM tblWords WHERE cl_Word = @cl_Word AND cl_WordLangCode = @cl_WordLangCode IF (@cl_WordId IS NULL) BEGIN INSERT INTO tblWords (cl_Word, cl_WordLangCode, cl_SourceId) VALUES (@cl_Word, @cl_WordLangCode, @cl_SourceId) SET @cl_WordId = SCOPE_IDENTITY() SELECT @cl_WordId END ELSE BEGIN SELECT @cl_WordId END
Et pour le protéger, je le modifie comme ci-dessous
DECLARE @cl_WordId bigint = NULL SELECT @cl_WordId = cl_WordId FROM tblWords WITH (HOLDLOCK) WHERE cl_Word = @cl_Word AND cl_WordLangCode = @cl_WordLangCode BEGIN IF (@cl_WordId IS NULL) BEGIN INSERT INTO tblWords (cl_Word, cl_WordLangCode, cl_SourceId) VALUES (@cl_Word, @cl_WordLangCode, @cl_SourceId) SET @cl_WordId = SCOPE_IDENTITY() SELECT @cl_WordId END ELSE BEGIN SELECT @cl_WordId END END
J'ai donc ajouté WITH (HOLDLOCK)
à la requête select et ajouté begin
et end
à la requête select
Cette approche est-elle correcte pour empêcher l'état de course conditionnel INSERT / UPDATE?
Comme mentionné dans les articles que j'ai postés à votre dernière question ( conditions de course conditionnelles INSERT / UPDATE et conditions de course "UPSERT" avec MERGE ) en utilisant MERGE
avec HOLDLOCK
est thread safe, donc votre requête serait:
MERGE tblWords WITH (HOLDLOCK) AS w USING (VALUES (@cl_Word, @cl_WordLangCode, @cl_SourceId)) AS s (cl_Word, cl_WordLangCode, cl_SourceId) ON s.cl_Word = w.cl_Word AND s.cl_WordLangCode = w.cl_WordLangCode WHEN NOT MATCHED THEN INSERT (cl_Word, cl_WordLangCode, cl_SourceId) VALUES (s.cl_Word, s.cl_WordLangCode, s.cl_SourceId);
Il semble également que cela puisse être une procédure stockée et que vous utilisez SELECT @cl_WordId
pour renvoyer l'ID à l'appelant. Cela tombe sous l'une des mauvaises habitudes de Aaron Bertrand à lancer , à la place, vous devriez utiliser un paramètre de sortie, quelque chose comme:
CREATE PROCEDURE dbo.SaveCLWord @cl_Word VARCHAR(255), @cl_WordLangCode VARCHAR(255), @cl_SourceId INT, @cl_WordId INT OUTPUT AS BEGIN MERGE tblWords WITH (HOLDLOCK) AS w USING (VALUES (@cl_Word, @cl_WordLangCode, @cl_SourceId)) AS s (cl_Word, cl_WordLangCode, cl_SourceId) ON s.cl_Word = w.cl_Word AND s.cl_WordLangCode = w.cl_WordLangCode WHEN NOT MATCHED THEN INSERT (cl_Word, cl_WordLangCode, cl_SourceId) VALUES (s.cl_Word, s.cl_WordLangCode, s.cl_SourceId); SELECT @cl_WordId = w.cl_WordId FROM tblWords AS w WHERE s.cl_Word = @cl_Word AND s.cl_WordLangCode = @cl_WordLangCode; END
ADDEDNUM
Vous pouvez le faire sans MERGE
comme suit.
BEGIN TRAN INSERT tblWords (cl_Word, cl_WordLangCode, cl_SourceId) SELECT @cl_Word, @cl_WordLangCode, @cl_SourceId WHERE NOT EXISTS ( SELECT 1 FROM tblWords WITH (UPDLOCK, HOLDLOCK) WHERE cl_Word = @cl_Word AND l_WordLangCode = @cl_WordLangCode ); COMMIT TRAN; SELECT @cl_WordId = w.cl_WordId FROM tblWords AS w WHERE s.cl_Word = @cl_Word AND s.cl_WordLangCode = @cl_WordLangCode;
Si vous n'utilisez pas la fusion parce que vous êtes préoccupé par ses bogues , ou parce que dans ce cas vous ne faites pas de mise à jour, MERGE
est trop puissant et un INSERT
suffira, alors c'est assez juste. Mais ne pas l'utiliser parce que la syntaxe n'est pas familière n'est pas la meilleure raison, prenez le time de lire à ce sujet, en savoir plus, et append une autre string à votre arc SQL.
MODIFIER
De documents en ligne
HOLDLOCK
Est équivalent à SERIALIZABLE. Pour plus d'informations, voir SERIALIZABLE plus loin dans cette rubrique. HOLDLOCK s'applique uniquement à la table ou à la vue pour laquelle elle est spécifiée et uniquement pour la durée de la transaction définie par l'instruction dans laquelle elle est utilisée . HOLDLOCK ne peut pas être utilisé dans une instruction SELECT qui inclut l'option FOR BROWSE.
Donc, dans votre requête, vous avez 6 déclarations:
-- STATETMENT 1 DECLARE @cl_WordId bigint = NULL --STATEMENT 2 SELECT @cl_WordId = cl_WordId FROM tblWords WITH (HOLDLOCK) WHERE cl_Word = @cl_Word AND cl_WordLangCode = @cl_WordLangCode BEGIN --STATEMENT 3 IF (@cl_WordId IS NULL) BEGIN -- STATEMENT 4 INSERT INTO tblWords (cl_Word, cl_WordLangCode, cl_SourceId) VALUES (@cl_Word, @cl_WordLangCode, @cl_SourceId) SET @cl_WordId = SCOPE_IDENTITY() --STATEMENT 5 SELECT @cl_WordId END ELSE BEGIN -- STATEMENT 6 SELECT @cl_WordId END END
Puisque vous n'avez pas de transactions explicites, chaque instruction s'exécute dans sa propre transaction implicite, donc en se concentrant sur l'instruction 2, cela équivaut à:
BEGIN TRAN SELECT @cl_WordId = cl_WordId FROM tblWords WITH (HOLDLOCK) WHERE cl_Word = @cl_Word AND cl_WordLangCode = @cl_WordLangCode COMMIT TRAN
Par conséquent, étant donné que HOLDLOCK
s'applique pour la durée de la transaction dans laquelle il est utilisé, le verrou est libéré, le verrou est libéré dès que ce code se termine, donc au moment où vous avez progressé vers les instructions 3 et 4 à la table.