在MS-SQL中防止条件INSERT / UPDATE竞争条件

  • Z时代
  • 2024-01-10
  • 分类：问答

我想知道我是否遵循正确的方法，并且需要您的帮助才能弄清楚

这是我不受保护的查询

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

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

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)到选择查询并添加begin与end该选择查询

此方法是否正确以防止有条件的INSERT / UPDATE竞争条件

回答：

正如我在您最后一个问题（条件INSERT /

UPDATE竞赛条件和[MERGE的`PSERT''竞赛条件](http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-

Race-Condition-With-

MERGE.aspx)）中提到的文章中提到的那样，使用MERGEwithHOLDLOCK`是线程安全的，因此您的查询将是：

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）的坏习惯之一，相反，您应该使用输出参数，例如：

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

您可以执行MERGE以下操作。

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;

如果您不使用合并是因为您担心它的bug，或者因为在这种情况下实际上并没有执行an

UPDATE，那么过大MERGE的杀伤力和an

INSERTwill就足够了，那么这就足够了。但是最好不要使用它，因为它不熟悉语法，这不是最好的原因，请花一些时间阅读它，了解更多信息，然后在SQL弓中添加另一个字符串。

来自在线文档

等效于SERIALIZABLE。有关更多信息，请参见本主题后面的SERIALIZABLE。HOLDLOCK仅适用于为其指定了表或视图，并且

。在包含FOR BROWSE选项的SELECT语句中不能使用HOLDLOCK。

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

-- 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，这等效于：

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时，可能已将另一个线程插入到该语句中。桌子。

以上是 在MS-SQL中防止条件INSERT / UPDATE竞争条件 的全部内容， 来源链接： utcz.com/qa/420123.html

回到顶部