查询导致SQL Server 2016死锁？

Question

我已经阅读了所有其他的死锁问题，但是这些问题似乎都是特定于查询的，所以无法用发布的答案来解决我的特定问题。在

我有一个Python脚本对这个数据库运行多个并发更新，当线程数设置得太高时，我会遇到死锁。在

下面的查询发生了死锁，我不确定应该使用什么组合的“表提示”，或者是否有更好的方法来执行这个UPDATE语句。在

以下是我的查询（为了简洁起见，修改了名称）：

BEGIN TRAN
IF EXISTS (SELECT BlahID FROM MyTable WITH (NOLOCK) WHERE BlahID = ?)
    BEGIN
        UPDATE MyTable SET
            Foo = ?,
            Bar = 1
        WHERE BlahID = ?
    END
ELSE
    BEGIN
        INSERT INTO MyTable (Foo, Bar)
        VALUES (1, ?,)
    END
COMMIT TRAN

Answer 1

您不希望多个会话为同一键值运行第一个SELECT。这就是导致僵局的原因。在

正确的模式是：

BEGIN TRAN
IF EXISTS (SELECT BlahID FROM MyTable WITH (UPDLOCK,HOLDLOCK) WHERE BlahID = ?)
    BEGIN
        UPDATE MyTable SET
            Foo = ?,
            Bar = 1
        WHERE BlahID = ?
    END
ELSE
    BEGIN
        INSERT INTO MyTable (Foo, Bar)
        VALUES (1, ?,)
    END
COMMIT TRAN

如果行存在，SELECT将锁定该行；如果该行不存在，则将对键范围获取更新范围锁。在这两种情况下，第二个会话将阻塞存在性检查，直到第一个会话完成插入或更新。在

如果不使用锁提示进行读取（无论是在SELECT、UPDATE、INSERT或MERGE中），那么如果该行不存在，则不会获取锁，并且多个会话可能会尝试插入。在

Answer 2

您不需要IF来检查记录是否已经存在。UPDATE语句中的WHERE子句可以做到这一点。您只需确保在插入新记录之前记录不存在，例如：

UPDATE MyTable 
SET
    Foo = @foo,
    Bar = 1
WHERE BlahID = @id;

INSERT MyTable (Bar,Foo)
values (1,@foo)
where not exists (select BlahID 
                  from MyTable 
                  where BlahID=@id)

如果可能，请使用命名参数，这样您只需要传递2个参数而不是4个参数，并且有可能混淆顺序。在

您可以在事务中包装这两个语句，但要确保BlahID被索引。这将允许服务器只锁定一行进行更新。如果没有索引，服务器将不得不扫描并锁定更多的数据以确保一致性。在

这也避免了插入重复的条目。无论使用多少锁，如果使用IF子句，使用相同的不存在的ID进行两次并发尝试都将导致两次插入，因为两个查询都会发现缺少的行，都将尝试无条件地插入。在

另一个选择是使用MERGE，尽管在这种情况下它的性能不好。从MERGE documentation

When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements. For example:

^{pr2}$

目前的情况更简单，只涉及一个表：

INSERT MyTable (Bar,Foo)
VALUES (1,@foo)
WHERE NOT EXISTS (SELECT BlahID FROM MyTable WHERE BlahID=@id);

为什么会出现僵局？

服务器必须锁定行以确保事务是可重复的。选择时，服务器对检索到的或扫描的行执行共享锁。这就是为什么拥有索引会导致更少的锁-服务器可以立即找到它需要的行。这些共享锁将在事务期间保留。如果没有显式事务，根据隔离模式，共享锁可能会在连接期间保留。可重复阅读就是这样。在

当您尝试更新一行时，服务器将尝试获取更新锁。如果一行具有共享锁，则更新操作将被阻止。如果一个事务已经持有一个行上的共享锁，它将尝试将其升级为升级锁。如果其他人在行上有S锁，则事务将被阻止。为了使读操作可重复，服务器必须锁定它所接触的行。在

如果服务器因为缺少索引而找不到一行，情况会更糟。在

NOLOCK并不意味着不取锁，它意味着别人的锁不受尊重。该操作仍将获取锁，但会导致脏结果、重影或丢失更新。在

在这种情况下，解除锁定的原因如下：

1. 两个连接执行IF(SELECT)，并在行S1和S2上获得共享锁。在
2. 连接1尝试将锁升级到升级，但发现它上有S2锁，并阻止它等待释放。在
3. 连接2尝试升级到U，但发现S1和块。没有连接可以继续导致死锁。在

您可以在SQL Server Transaction Locking and Row Versioning Guide的Locking in the Database Engine部分找到有关锁定、锁类型、兼容性和范围的更多信息

快照隔离

可以使用snapshot isolation level来避免读写器互相阻塞，类似于Oracle和PostgreSQL的做法。这对这个案例没有帮助，因为您有一个编写器阻塞另一个。在

Answer 3

我最终在“BlahID”字段中添加了一个“unique constraint”，因为它似乎在对我的第一个UPDATE语句执行整个表级锁。一旦我添加了这个约束，我相信它只正确地执行了一个行级锁，它为我解决了死锁问题。在

我还取消了更新时的“IF/ELSE”格式，只需执行以下操作：

UPDATE MyTable SET
    Foo = ?
WHERE BlahID = ?
IF @@ROWCOUNT=0
    INSERT INTO MyTable (Foo)
    VALUES (1)

我学到了很多关于“表提示”和锁定其他提供的答案，所以如果你是一个任性的谷歌搜索者，它们值得一读！在