Preventing Conditional Insert/update Race Condition In Ms-sql

I wonder that do i follow correct approach and need your help to figure out Here my non-protected query DECLARE @cl_WordId bigint = NULL SELECT @cl_WordId = cl_WordId FROM tblWor

Solution 1:

As alluded to in the articles I posted to your last question (Conditional INSERT/UPDATE Race Condition and “UPSERT” Race Condition With MERGE) using MERGE along with HOLDLOCK is thread safe, so your query would be:

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
WHENNOT MATCHED THENINSERT (cl_Word, cl_WordLangCode, cl_SourceId)
    VALUES (s.cl_Word, s.cl_WordLangCode, s.cl_SourceId);

It also looks like this might be a stored procedure and you are using SELECT @cl_WordId to return the ID to the caller. This falls under one of Aaron Bertrand's bad habits to kick, instead you should use an output parameter, something like:

CREATEPROCEDURE dbo.SaveCLWord
        @cl_Word            VARCHAR(255), 
        @cl_WordLangCode    VARCHAR(255), 
        @cl_SourceId        INT,
        @cl_WordId          INT OUTPUT
ASBEGINMERGE 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
    WHENNOT MATCHED THENINSERT (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

You can do this without MERGE as follows.

BEGIN TRAN

INSERT tblWords (cl_Word, cl_WordLangCode, cl_SourceId)
SELECT@cl_Word, @cl_WordLangCode, @cl_SourceId
WHERENOTEXISTS
        (   SELECT1FROM    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;

If you are not using merge because you are concerned about its bugs, or because in this case you don't actually do an UPDATE, so MERGE is overkill and an INSERT will suffice, then that is fair enough. But not using it because it is unfamiliar syntax is not the best reason, take the time to read about it, learn more, and add another string to your SQL bow.

---

EDIT

From online docs

HOLDLOCK

Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

So in your query, you have 6 statements:

-- STATETMENT 1DECLARE@cl_WordId bigint=NULL--STATEMENT 2SELECT@cl_WordId = cl_WordId
FROM tblWords WITH (HOLDLOCK)
WHERE cl_Word =@cl_Word
AND cl_WordLangCode =@cl_WordLangCode

BEGIN--STATEMENT 3
  IF (@cl_WordId ISNULL)
  BEGIN-- STATEMENT 4INSERTINTO tblWords (cl_Word, cl_WordLangCode, cl_SourceId)
      VALUES (@cl_Word, @cl_WordLangCode, @cl_SourceId)
    SET@cl_WordId = SCOPE_IDENTITY()

    --STATEMENT 5SELECT@cl_WordId
  ENDELSEBEGIN-- STATEMENT 6SELECT@cl_WordId
  ENDEND

Since you don't have explicit transactions, each statement runs within its own implicit transaction, so concentrating on statement 2, this is equivalent to:

BEGIN TRAN

SELECT@cl_WordId = cl_WordId
FROM tblWords WITH (HOLDLOCK)
WHERE cl_Word =@cl_Word
AND cl_WordLangCode =@cl_WordLangCode

COMMIT TRAN

Therefore, since HOLDLOCK applies for the duration of the transaction in which it is used, the lock is released, the lock is released as soon as this code finishes, so by the time you have progressed to statement 3 and 4 another thread could have inserted to the table.