SQL Server 2019中EXEC语句内事务提交后回滚的行为咨询

核心行为解释 #

你的问题本质是跨批次错误传播导致的事务状态异常，结合TRY/CATCH块的不当ROLLBACK操作共同作用的结果，具体拆解：

1. 错误触发的连锁反应
   你在内层动态SQL中执行的THROW 1;是不符合语法规范的调用（THROW语句要求错误号≥50000、错误消息、状态码三个参数），会触发运行时错误。这个错误会跨越两层嵌套EXEC的边界，直接传递到最外层的TRY块，触发CATCH逻辑。

2. 事务状态的“不可提交”标记
   当错误从嵌套EXEC抛出后，SQL Server会将当前会话的事务上下文标记为**“不可提交（Doomed）”**——即使你已经显式提交了插入2的事务，这个标记会导致会话中所有后续事务操作被强制终止，且SQL Server会误认为当前存在未完成事务。

3. CATCH块中不必要的ROLLBACK
   你的CATCH块中执行了EXEC ('ROLLBACK TRANSACTION;');，此时会话中并没有活跃事务（插入2的事务已提交，插入3的事务未开始就被错误终止），但这个ROLLBACK会触发SQL Server的隐式事务回滚机制——它会回滚当前会话中所有最近的“逻辑事务单元”，包括已经提交的插入2操作，这就是你看到整个EXEC内容被回滚的原因。

验证与修正 #

如果将THROW 1;改为符合语法的调用（比如THROW 50001, '测试错误', 1;），并移除CATCH块中不必要的ROLLBACK，插入2的操作会被持久化，插入3的操作不会执行，符合预期：

修正后的代码示例：

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (test int);

INSERT INTO my_table VALUES (1);

BEGIN TRY
    SELECT 'start'
    EXEC(' EXEC(  ''
    BEGIN TRANSACTION; 
    SET IMPLICIT_TRANSACTIONS OFF;
    INSERT INTO my_table VALUES (2); 
    COMMIT; 
    THROW 50001, ''测试错误'', 1;
    BEGIN TRANSACTION; 
    INSERT INTO my_table VALUES (3); 
    COMMIT; 
    '' )' )
    SELECT 'after EXEC'
    THROW
END TRY
BEGIN CATCH
     -- 移除不必要的ROLLBACK，仅处理错误
     SELECT 'In CATCH Block', ERROR_MESSAGE() AS ErrorMsg
END CATCH

SELECT 'After END CATCH'
 
SELECT * FROM my_table;

运行此代码后，my_table中会包含1和2，符合事务提交的预期。

关键结论 #

• 避免在CATCH块中盲目执行ROLLBACK，应先通过XACT_STATE()函数检查当前事务状态：IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
• 确保THROW语句的语法正确，避免触发不必要的严重错误
• 嵌套动态SQL的错误会跨批次传播，需注意事务上下文的状态管理

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