SQL TRANSACTION 左连接结果为 null

我遇到一个问题，我在事务中插入用户和地址有 10 秒的延迟，如果在事务执行期间运行我的 select 语句，它将等待事务完成，但我会在连接上得到一个空值。为什么我的选择不等待用户/地址数据都被提交。 如果我在事务完成后运行 select 语句，我将得到正确的结果。为什么我会收到此错误以及使此工作正常进行的通用解决方案是什么

BEGIN TRANSACTION   
 insert into user(dummy) values('text')
 WAITFOR DELAY '00:00:10';
 insert into address(ID_FK) values((SELECT SCOPE_IDENTITY()))
COMMIT TRANSACTION

在事务期间运行导致 join 中为 null

select * from user u left join address a on u.id = a.ID_FK order by id desc

| ID | dummy | ID_FK | 
| 101 | 'text' | null |

交易结果正确后运行

select * from user u left join address a on u.id = a.ID_FK order by id desc

| ID | dummy |  ID_FK|
| 101 | 'text' | 101 |

这种类型的事情在本地 SQL Server 的默认读提交级别下是完全可能的，因为它使用读提交锁定。然后将发生什么取决于执行计划。

下面是一个例子

CREATE TABLE [user]
  (
     id    INT IDENTITY PRIMARY KEY,
     dummy VARCHAR(10)
  );

CREATE TABLE [address]
  (
     ID_FK INT REFERENCES [user](id),
     addr  VARCHAR(30)
  ); 

连接一

BEGIN TRANSACTION

INSERT INTO [user]
            (dummy)
VALUES     ('text')

WAITFOR DELAY '00:00:20';

INSERT INTO address
            (ID_FK,
             addr)
VALUES     (SCOPE_IDENTITY(),
            'Address Line 1')

COMMIT TRANSACTION

连接二（在连接一等待 20 秒时运行此命令）

SELECT *
FROM   [user] u
       LEFT JOIN [address] a
              ON u.id = a.ID_FK
ORDER  BY id DESC
OPTION (MERGE JOIN) 

Returns

执行计划如下

扫描开启User被连接 1 中已插入行的打开事务阻止。这必须等到事务提交，然后最终才能读取新插入的行。

同时，排序运算符已经请求了行address至此，它消耗了其中的所有行Open方法（即在操作员初始化期间）。这不会被阻止，因为尚未插入任何行address然而。它读取 0 行address这解释了最终结果。

如果您切换到使用读提交快照而不是读提交锁定，您将不会遇到此问题，因为它只会读取语句开头的提交状态，因此不可能出现这种异常。