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

您不需要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:

INSERT tbl_A (col, col2)  
SELECT col, col2   
FROM tbl_B   
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);  

目前的案例更简单，只涉及一张表:

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

为什么会出现僵局？

服务器必须锁定行以确保事务可重复。选择时，服务器对检索或扫描的行采用共享 (S) 锁。这就是为什么有一个索引会导致 更少 锁 - 服务器可以立即找到它需要的行。这些共享锁将在事务期间保持不变。如果没有显式事务，根据隔离模式，共享锁可能会在连接期间保留。这就是 REPEATABLE READ 发生的情况。

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

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

NOLOCK 并不意味着没有锁，它意味着不尊重其他人的锁。该操作仍会锁定，但会导致脏结果、重影或丢失更新。

这就是在这种情况下引起 dealock 的原因:

1. 两个连接执行 IF(SELECT) 并在行 S1 和 S2 上获得共享锁。
2. 连接 1 尝试将锁升级为 UPGRADE，但发现 S2 锁在它上面并阻塞等待它被释放。
3. 连接 2 尝试升级到 U，但发现 S1 并阻塞。无法继续连接，导致死锁。

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

快照隔离

您可以使用 snapshot isolation level避免读者和作者互相阻塞，类似于 Oracle 和 PostgreSQL 所做的。这在这种 情况下无济于事，因为您有一个作者阻止了另一个作者。