事务隔离级别和子查询 [英] Transaction isolation levels and subqueries

问题描述

如果我们有一个带有子选择的UPDATE，那么子查询可以在READ COMMITTED隔离下并发执行吗？

换句话说，是否存在竞争条件出现在以下内容中：

 更新列表集[state] ='active'
其中
 id = （从列表中选择前1个ID，其中[state] =就绪的ID）
  

换句话说，如果许多连接正在同时执行此SQL，我们是否可以保证实际上每次调用都会更新一行（只要存在处于就绪状态的行）？

解决方案

答案为是，存在竞争条件，两个事务可能同时执行子查询，导致同一行随后被更新两次。 p>

可以通过将更新重写为

  update TEMP $ b来解决此问题。 $ b set [state] ='active'
 from 
（从列表中选择前1 *，其中[state] ='rea dy'按id排序）TEMP 
  

坦率地说，我不知道为什么这应该有所不同，但是是。现在，SQL Server在执行子查询时将获取更新锁定（意图更新），从而防止并发事务选择同一行。

if we have an UPDATE with a sub-SELECT, can the subquery execute concurrently or not under READ COMMITTED isolation?

In other words, is there a race condition present in the following:

update list set [state] = 'active' 
where
    id = (select top 1 id from list where [state] = 'ready' order by id)

In yet other words, if many connections are simulataneously executing this SQL, can we guarantee that one row is in fact updated per invocation (so long as rows in 'ready' state exist)?

解决方案

The answer is yes, there is a race condition and two transactions may execute the subquery concurrently, leading to the same row being subsequently updated twice.

This can be fixed by rewriting the update as

update TEMP 
set [state] = 'active' 
from 
    (select top 1 * from list where [state] = 'ready' order by id) TEMP

I frankly don't know why this should be different, but it is. SQL Server will now take update locks ("intent to update") when executing the subquery, preventing concurrent transactions from picking the same row.

这篇关于事务隔离级别和子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！