sql - 只更新单个表的事务是否总是隔离的？

Question

根据 UPDATE documentation , UPDATE总是获取整个表的排他锁。但是，我想知道排他锁是在确定要更新的行之前还是仅在实际更新之前获得。

我的具体问题是我有一个嵌套的 SELECT在我的 UPDATE像这样:

UPDATE Tasks
SET Status = 'Active'
WHERE Id = (SELECT TOP 1 Id 
            FROM Tasks
            WHERE Type = 1
                AND (SELECT COUNT(*) 
                     FROM Tasks 
                     WHERE Status = 'Active') = 0
            ORDER BY Id)

现在我想知道是否真的保证只有一个
与 Status = 'Active' 的任务之后，如果可以并行执行相同的语句，则可以使用另一种类型:

UPDATE Tasks
SET Status = 'Active'
WHERE Id = (SELECT TOP 1 Id 
            FROM Tasks
            WHERE Type = 2           -- <== The only difference
                AND (SELECT COUNT(*) 
                     FROM Tasks 
                     WHERE Status = 'Active') = 0
            ORDER BY Id)

如果对于这两个语句，要在获取锁之前确定要更改的行，我最终可能会执行两个必须阻止的事件任务。

如果是这种情况，我该如何预防？我可以在不将事务级别设置为 SERIALIZABLE 的情况下阻止它吗？或搞乱锁定提示？

来自对 Is a single SQL Server statement atomic and consistent?的回答我了解到问题出现在嵌套 SELECT 时访问另一个表。但是，如果只涉及更新的表，我不确定是否需要关心这个问题。

Answer 1

如果您只想要一个带有 static = active 的任务，那么设置表格以确保这是真的。使用过滤的唯一索引:

create unique index unq_tasks_status_filter_active on tasks(status)
    where status = 'Active';

第二个并发 update可能会失败，但您将确保唯一性。您的应用程序代码可以处理此类失败的更新，然后重试。

依赖更新的实际执行计划可能很危险。这就是为什么让数据库进行此类验证更安全的原因。基础实现细节可能会有所不同，具体取决于 SQL Server 的环境和版本。例如，在单线程、单处理器环境中有效的方法在并行环境中可能无效。适用于一种隔离级别的方法可能不适用于另一种隔离级别。

编辑:

而且，我无法抗拒。出于效率目的，请考虑将查询编写为:

UPDATE Tasks
    SET Status = 'Active'
    WHERE NOT EXISTS (SELECT 1
                      FROM Tasks
                      WHERE Status = 'Active'
                     ) AND
          Id = (SELECT TOP 1 Id 
                FROM Tasks
                WHERE Type = 2           -- <== The only difference
                ORDER BY Id
               );

然后将索引放在 Tasks(Status) 上和 Tasks(Type, Id) .事实上，使用正确的查询，您可能会发现查询速度如此之快(尽管更新了索引)，以至于您对当前更新的担忧大大减轻了。这不会解决竞争条件，但至少可能会使其变得罕见。

如果您正在捕获错误，那么使用唯一的过滤索引，您可以这样做:

UPDATE Tasks
    SET Status = 'Active'
    WHERE Id = (SELECT TOP 1 Id 
                FROM Tasks
                WHERE Type = 2           -- <== The only difference
                ORDER BY Id
               );

如果一行已经处于事件状态，这将返回一个错误。

注意:所有这些查询和概念都可以应用于“每组一个事件”。这个答案正在解决您提出的问题。如果您有“每组一个事件”的问题，请考虑提出另一个问题。