admin

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

sql

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

这是我不受保护的查询

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

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


阅读 90

收藏
2021-07-01

共1个答案

admin

正如我在您最后一个问题(条件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

ADDEDNUM

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

2021-07-01