是仅仅更新单个表的事务是否始终隔离？

根据UPDATE文档,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或搞乱锁定提示的情况下阻止它吗？

从答案到单个SQL Server语句是原子的还是一致的？我了解到嵌套SELECT访问另一个表时会出现问题.但是,如果只关注更新的表,我不确定是否必须关心这个问题.

如果您只需要一个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
               );

如果某行已处于活动状态,则会返回错误.

注意:所有这些查询和概念都可以应用于"每个组一个活动".这个答案正在解决你提出的问题.如果您有"每组一个活动"问题,请考虑提出另一个问题.