SQL Server 2012在选择或插入时潜在的竞争条件

Question

我需要运行一个SQL Server查询，要么"获取"或"创建"一个记录。非常简单，但我不确定这是否会产生任何竞争条件。

我读过几篇关于插入/更新期间锁定的文章，例如http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx，但我不确定这是否与我有关，因为我使用SQL Server 2012，而且我对修改本身没有争议，只确保"一次性创建"我的记录。

另外，我将在Id列(不是标识列)上有一个主键约束，所以我知道竞争条件在最坏的情况下只会创建一个错误，而不是无效的数据，但我也不希望命令抛出错误。

谁能告诉我怎么解决这个问题?或者我想太多了，我可以简单地做一些像:

IF EXISTS(SELECT * FROM Table WHERE Id = @Id)
BEGIN
      SELECT Id, X, Y, Z FROM Table WHERE Id = @Id
END
ELSE
BEGIN
      INSERT INTO Table (Id, X, Y, Z)
      VALUES (@Id, @X, @Y, @Z);
      SELECT @Id, @x, @Y, @Z;
END

我在文档数据库领域工作了几年，我的SQL很生疏。

Answer 1

GET比CREATE出现得更频繁，因此对它进行优化是有意义的。

IF EXISTS (SELECT * FROM MyTable WHERE Id=@Id)
    SELECT Id, X, Y, Z FROM MyTable WHERE Id=@Id
ELSE
BEGIN
    BEGIN TRAN
        IF NOT EXISTS (SELECT * FROM MyTable WITH (HOLDLOCK, UPDLOCK) WHERE Id=@Id )
        BEGIN
        --  WAITFOR DELAY '00:00:10'
            INSERT INTO MyTable (Id, X, Y, Z) VALUES (@Id, @X, @Y, @Z)
        END
        SELECT Id, X, Y, Z FROM MyTable WHERE Id=@Id
    COMMIT TRAN
END

如果您的记录不存在，则开始显式事务。都需要HOLDLOCK和UPDLOCK(感谢@MikaelEriksson)。表提示在事务期间持有共享锁和更新锁。这将正确地序列化INSERT并可靠地避免竞争条件。

要验证，取消注释WAITFOR并同时运行两个或多个查询。

Answer 2

你的代码肯定有竞争条件。

一种方法是将条件合并到一个insert中，但我不能100%确定这可以防止所有竞态条件，但这可能有效:

INSERT INTO Table(Id, X, Y, Z)
    SELECT @Id, @X, @Y, @Z
    WHERE NOT EXISTS (SELECT 1 FROM Table WHERE ID = @ID);
SELECT Id, X, Y, Z FROM Table WHERE ID = @Id;

问题是关于重复检测与插入期间的底层锁定机制。您可以尝试使用更显式的锁，但是表锁肯定会减慢处理速度(要了解锁，您可以参阅文档或类似的博客文章)。

我能想到的最简单的方法是使用try/catch块:

BEGIN TRY
    INSERT INTO Table(Id, X, Y, Z)
        SELECT @Id, @X, @Y, @Z;
END TRY
BEGIN CATCH
END CATCH;
SELECT Id, X, Y, Z FROM Table WHERE ID = @Id;

也就是说，试试insert。如果失败(可能是因为有重复的键，但您可以显式地检查)，则忽略该失败。返回id的值。

Answer 3

为什么需要两个不同的SELECT ?

IF NOT EXIST (SELECT TOP 1 1 FROM Table WHERE Id = @Id)
    INSERT INTO Table(Id, X, Y, Z)
    VALUES (@Id, @X, @Y, @Z)
SELECT Id, X, Y, Z FROM Table WHERE ID = @Id

只要你在检查存在性时不指定WITH (NOLOCK)，你应该没问题。