在MS-SQL中防止条件INSERT / UPDATE竞争条件 [英] 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 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

为了保护它，我将其修改如下

And to protect it, i modify it as below

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

所以我已将 WITH（HOLDLOCK）添加到选择查询中，并添加了开始和 end 到选择查询

So i have added WITH (HOLDLOCK) to the select query and added begin and end to the select query

此方法是否正确，以防止条件插入/更新竞争条件

Is this approach correct to prevent Conditional INSERT/UPDATE Race Condition

推荐答案

正如我在您最后一个问题上发表的文章中所提到的（条件INSERT / UPDATE竞争条件和使用MERGE的 UPSERT竞态条件）usi ng MERGE 和 HOLDLOCK 是线程安全的，因此您的查询将是：

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
WHEN NOT MATCHED THEN 
    INSERT (cl_Word, cl_WordLangCode, cl_SourceId)
    VALUES (s.cl_Word, s.cl_WordLangCode, s.cl_SourceId);

它看起来也可能是存储过程，并且您正在使用 SELECT @cl_WordId 将ID返回给调用者。这属于亚伦·贝特朗（Aaron Bertrand）的不良习惯来踢，而应该使用输出参数，例如：

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:

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

您可以执行以下操作，而无需 MERGE ，如下所示。

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
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;

如果因为有关其错误的信息，或者因为在这种情况下您实际上没有执行 UPDATE ，所以 MERGE 是过大的，而 INSERT 就足够了，那就是很公平。但最好不要使用它，因为它不熟悉语法，这不是最好的原因，请花一些时间阅读它，了解更多信息，然后在SQL弓中添加另一个字符串。

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.

编辑

来自在线文档

保持锁定

等效于SERIALIZABLE。有关更多信息，请参见本主题后面的SERIALIZABLE。 HOLDLOCK仅适用于为其指定表或视图，并且仅适用于在其中使用该语句的语句所定义的事务期间。 HOLDLOCK不能在包含FOR BROWSE选项的SELECT语句中使用。

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.

因此，在查询中，您有6条语句：

So in your query, you have 6 statements:

-- 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

由于您没有显式事务，因此每个语句都以其自身的隐含方式运行交易，因此集中于语句2，这等效于：

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

因此，由于 HOLDLOCK 在使用它的交易期间适用，因此该锁被释放，该代码完成后立即释放该锁，因此，当您进入语句3和4时，可能已在该表中插入了另一个线程。

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.

这篇关于在MS-SQL中防止条件INSERT / UPDATE竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！