SQL Server 中单个语句的读提交隔离级别

Question

假设我有一个人员表，它只有 1 行 -

id = 1, name = 'foo'

在一个连接上

select p1.id, p1.name, p2.name
from person p1 
join person p2 on p1.id = p2.id

同时在另一个连接上：

update person set name = 'bar' where person.id = 1

Q1： 我的选择是否有可能返回这样的结果更新语句的时间：

id = 1, p1.name = 'foo', p2.name = 'bar'

两个连接都没有使用显式事务，并且都使用默认事务隔离级别 READ COMMITTED。

问题实际上是为了帮助我理解，在sql语句开始时获取的锁是否继续存在直到语句完成，或者语句是否可以释放锁并重新获取相同的锁row 如果在同一个语句中使用两次？

问题2：如果在数据库上设置set read_comfilled_snapshot on，问题的答案会改变吗？

Answer 1

Q1：是的，至少在理论上这是完全可能的。 read commited 只是保证您不会读取脏数据，它不保证一致性。在读提交级别，一旦读取数据（不是在事务结束，甚至语句结束时），共享锁就会释放。

问题 2：是的，如果 read_comfilled_snapshot，这个问题的答案将会改变。已开启。这样您就可以保证语句级一致性。我发现很难找到一个明确说明这一点的在线资源，但引用了“Microsoft SQL Server 2008 Internals”的第 648 页

RCSI 中的声明可以看出一切
在开始之前提交
陈述。中的每个新语句
交易选取最新的
已提交更改。

另请查看此 MSDN 博客文章< /a>

设置脚本

CREATE TABLE person 
(
id int primary key,
name varchar(50)
)

INSERT INTO person
values(1, 'foo');

连接 1

while 1=1
update person SET name = CASE WHEN name='foo' then 'bar' ELSE 'foo' END

连接 2

DECLARE @Results TABLE (
  id    int primary key,
  name1 varchar(50),
  name2 varchar(50))

while( NOT EXISTS(SELECT *
                  FROM   @Results) )
  BEGIN
      INSERT INTO @Results
      Select p1.id,
             p1.name,
             p2.name
      from   person p1
             INNER HASH join person p2
               on p1.id = p2.id
      WHERE  p1.name <> p2.name
  END

SELECT *
FROM   @Results  

结果

id          name1 name2
----------- ----- -----
1           bar   foo

查看 Profiler 中的其他连接类型，在合并连接或嵌套循环计划下似乎不会出现此问题这个特定的查询（在获取所有锁之前不会释放锁），但要点仍然是读取已提交仅保证您不会读取脏数据，它不保证一致性。实际上，对于您发布的确切查询，您很可能不会遇到此问题，因为 SQL Server 默认情况下不会选择此联接类型。然而，您只能依靠实现细节来产生您想要的行为。

注意：如果您想知道为什么某些行级 S 锁似乎丢失，请参阅 此处解释了优化。