SQL TRANSACTION 左连接结果为空 [英] SQL TRANSACTION Left join result in null

问题描述

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

I have a problem where I insert User and Address in a transaction with a 10 second delay and if run my select statement during the execution of the transaction it will wait for transaction to finish but I will get a null on the join. Why don't my select wait for both User/Address data to be committed. If I run the select statement after the transaction is finish I will get the correct result. Why do i get this error and what is the generic solution to make this work

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

Running during transaction result in null in join

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 |

在事务后运行得到正确结果

Running after transaction result in correct result

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 的默认读提交级别完全可能，因为它使用读提交锁定.然后执行计划取决于会发生什么.

This type of thing is entirely possible at default read committed level for on premise SQL Server as that uses read committed locking. It is then execution plan dependent what will happen.

示例如下

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) 

退货

<头>

id	虚拟	ID_FK	地址
1	文字	NULL	NULL

执行计划如下

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

The scan on User is blocked by the open transaction in Connection 1 that has inserted the row there. This has to wait until that transaction commits and then eventually gets to read the newly inserted row.

与此同时，Sort 运算符此时已经从 address 请求了行，因为它在其 Open 方法中(即在运算符初始化期间)消耗了所有行.这不会被阻止，因为尚未向 address 插入任何行.它从解释最终结果的 address 读取 0 行.

Meanwhile the Sort operator has already requested the rows from address by this point as it consumes all its rows in its Open method (i.e. during operator initialisation). This is not blocked as no row has been inserted to address yet. It reads 0 rows from address which explains the final result.

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

If you switch to using read committed snapshot rather than read committed locking you won't get this issue as it will only read the committed state at the start of the statement so it isn't possible to get this kind of anomaly.

这篇关于SQL TRANSACTION 左连接结果为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！