SQL Server事务包裹SELECT查询无返回结果问题求解

我正在构建测试用例，用于统计加载到最终事实表的记录总数，执行步骤如下：

• 声明变量
• 加载维度模拟数据
• 加载事务源数据
• 执行事实表加载存储过程：关联维度数据提取所需业务键，将事务数据写入最终事实表；预期该步骤会因某分区存在唯一索引冲突触发报错，存储过程已做异常处理，会跳过异常事务继续处理剩余数据，直至全部事务处理完成
• 查询校验事实表中成功加载的记录数量

本测试用例的核心要求是不向数据库提交任何持久化变更，因此所有执行逻辑需要包裹在BEGIN TRANSACTION和ROLLBACK语句之间。

未开启事务时，上述5个步骤均能返回符合预期的结果；但将逻辑包裹在事务中后，首次执行触发报错：

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

在执行ROLLBACK前添加XACT_STATE<>0判断后，该报错不再出现，但步骤5的查询始终返回0条记录，需要调整逻辑让步骤5正常返回查询结果。

BEGIN TRANSACTION;
--#1

DECLARE @current_datetime DATETIME;
SET @current_datetime = GETDATE ();

WAITFOR DELAY '00:00:01';

DECLARE @usp_batch_load_control_info TABLE 
(
    batch_load_control_code BIGINT NOT NULL,
    is_batch_load_processed BIT NOT NULL,
    is_change_data_capture_complete BIT NOT NULL,
    change_data_capture_info NVARCHAR(MAX) NULL
);

DECLARE @batch_load_name NVARCHAR(100) = CAST(NEWID () AS NVARCHAR(100)); --generate a batch load name guaranteed to be unique
INSERT INTO @usp_batch_load_control_info(
batch_load_control_code,
is_batch_load_processed,
is_change_data_capture_complete,
change_data_capture_info)
EXEC load.usp_batch_load_control_info @batch_load_name = @batch_load_name;

DECLARE @batch_load_control_code BIGINT;

SELECT @batch_load_control_code = batch_load_control_code
FROM   @usp_batch_load_control_info;

DECLARE @data_datetime1 DATETIME;
SET @data_datetime1 = GETDATE ();

DECLARE @data_datetime2 DATETIME;
SET @data_datetime2 = DATEADD (HOUR, 1, GETDATE ());

DECLARE @data_datetime3 DATETIME;
SET @data_datetime3 = DATEADD (HOUR, 2, GETDATE ());

---dimension
--#2
DECLARE @source dbo.udt_dim_dimesion_source;

INSERT INTO @source (dim_business_key,effective_date,dim_name)
VALUES ('dim_bk1', GETDATE (), 'dim_name');

EXEC base.usp_load_dim_insert @source = @source,@disable_output = 1;

DELETE @source;

INSERT INTO @source (dim_business_key,effective_date,dim_name)
VALUES ('dim_bk1', '2021-02-01', 'dim_name2');

EXEC base.usp_load_dim_insert @source = @source,@disable_output = 1;

UPDATE base.dim_dimension 
SET end_timestamp = '2021-02-01' 
WHERE  dim_name = 'dim_name' 
AND create_timestamp >= @current_datetime;

SELECT * 
FROM base.dim_dimension 
WHERE create_timestamp >= @current_datetime;

--CDC
--#3
INSERT INTO load.base_fact_transaction_source (batch_load_control_code,data_datetime,transaction_id,is_transaction_deleted,trade_date,dim_business_key) 
VALUES (@batch_load_control_code, @data_datetime1, 'transaction_id1', 0, '2021-01-01', 'dim_bk1'),
(@batch_load_control_code, @data_datetime2, 'transaction_id2', 0, '2021-02-01', 'dim_bk1'),
(@batch_load_control_code, @data_datetime3, 'transaction_id3', 0, '2021-03-01', 'dim_bk1');

--#4 
BEGIN TRY
    EXEC base.usp_load_fact_transaction @batch_load_control_code = @batch_load_control_code ,@disable_output = 1;
END TRY

--#5
BEGIN CATCH
    SELECT   *
    FROM     final.fact_transaction
    WHERE    create_timestamp >= @current_datetime;
END CATCH;

IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION
    ;

1. 存储过程base.usp_load_fact_transaction内部的异常处理逻辑不规范：捕获到唯一索引冲突后，直接执行了无校验的ROLLBACK语句，会直接回滚所有层级的事务，把外层测试脚本开启的事务也清空，这就是最初报“找不到对应BEGIN TRANSACTION”的原因。
2. 加了XACT_STATE()<>0判断后虽然规避了报错，但事务早就被存储过程内部提前回滚，之前插入的维度模拟数据、事务源数据、已经写入事实表的合法记录全被清除，所以查询永远返回0。
3. 校验查询的位置错误：把步骤5的查询写在了CATCH块里，而存储过程本身已经做了异常捕获、会跳过错误继续执行，不会把异常抛到外层，CATCH块根本不会触发，查询逻辑压根不会执行。

• 首先修改事实表加载存储过程的异常处理逻辑，禁止在存储过程内部直接执行无条件ROLLBACK。存储过程内部只需要做错误记录、跳过异常数据的逻辑即可，如果需要回滚存储过程内部的操作，使用事务保存点实现，不影响外层开启的事务，参考写法：

  -- 存储过程逻辑起始位置，创建事务保存点
  SAVE TRANSACTION sp_fact_load_save;
  BEGIN TRY
    -- 原有事实表加载、数据写入的业务逻辑
  END TRY
  BEGIN CATCH
    -- 仅回滚到当前存储过程的保存点，不触碰外层事务
    IF XACT_STATE() <> 0
      ROLLBACK TRANSACTION sp_fact_load_save;
    -- 记录错误日志，跳过异常行继续处理剩余数据
  END CATCH
  

• 调整测试脚本的查询位置，把步骤5的事实表校验查询从CATCH块移出来，放在存储过程调用完成之后、最终ROLLBACK之前，不管存储过程有没有抛出异常都执行查询。
• 调整后测试脚本的执行顺序：
  1. 开启显式事务
  2. 执行变量声明、维度模拟数据加载、事务源数据加载逻辑
  3. 调用事实表加载存储过程
  4. 执行事实表记录查询校验，此时事务未提交也未回滚，所有写入的数据均可见，可以拿到正确的加载记录数
  5. 校验完成后判断事务状态，执行ROLLBACK清空所有测试数据，不产生任何持久化变更

内容的提问来源于stack exchange，提问作者KahLeon