sql-server - 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语句开始时获取的锁是否继续存在直到语句完成，或者语句是否可以释放锁并重新获取锁对于同一行，如果在同一条语句中使用两次？

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

Answer 1

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

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

A statement in RCSI sees everything committed before the start of the statement. Each new statement in the transaction picks up the most recent committed changes.

还有see this MSDN blog post

安装脚本

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 中的其他联接类型，似乎在该特定查询的合并联接或嵌套循环计划下不会出现此问题(没有获得锁)释放直到获取所有数据)，但要点仍然是，已提交的读只是保证您不会读取脏数据，它对一致性没有任何 promise 。实际上，对于您发布的确切查询，您很可能不会遇到此问题，因为 SQL Server 默认情况下不会选择此联接类型。然而，您只能依靠实现细节来产生您想要的行为。

注意:如果您想知道为什么某些行级 S 锁似乎丢失，这是 an optimisation explained here .