SQL Server 2019存储过程Python调用时事务回滚失败求助

出现该错误的核心原因分为三个层面：

1. 事务嵌套与命名回滚的兼容性冲突 #

Python的ODBC驱动默认处于**非自动提交（autocommit=False）**状态，会导致存储过程执行时外部已存在未提交事务，存储过程内的BEGIN TRANSACTION @TranName会变为嵌套事务：

• 此时命名事务实际是一个保存点，而非独立事务
• 若TRY块内的错误导致整个外部事务被标记为不可提交（doomed transaction），ROLLBACK TRANSACTION @TranName会因找不到对应保存点报错
• SSMS默认开启自动提交，不会出现嵌套事务，因此执行正常

2. SQL Server 2019的事务行为变更 #

升级到2019版本后，SQL Server对事务错误的处理更严格：当TRY块中发生严重错误（如约束冲突、数据类型错误）时，事务会被立即标记为不可提交状态，此时@@TRANCOUNT虽仍大于0，但已无法通过命名回滚操作指定保存点，必须回滚整个事务。

3. 存储过程的逻辑缺陷 #

当前代码依赖@@TRANCOUNT > 0判断是否回滚命名事务，但@@TRANCOUNT仅能反映事务嵌套层数，无法判断事务是否处于可操作状态。若事务已被标记为不可提交，指定名称回滚必然失败。

方法一：修正存储过程的事务处理逻辑 #

用XACT_STATE()替代@@TRANCOUNT判断事务状态，并统一使用无名称的事务回滚/提交：

修改后的核心代码：

ALTER PROCEDURE [dbo].[p_myproc] 
    @error NVARCHAR(MAX)= 'Success' OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @idMarche INT; -- 补充数据类型，修复语法错误
DECLARE @TranName VARCHAR(20);

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'SOME_TABLE')
BEGIN 
    SELECT @TranName = 'TR_NL';
    BEGIN TRANSACTION; -- 去掉命名，使用匿名事务
    BEGIN TRY
        /* 业务逻辑：更新、插入等操作 */
    END TRY
    BEGIN CATCH
        SELECT @error ='Error Number: ' + ISNULL(CAST(ERROR_NUMBER() AS VARCHAR(10)), 'NA')     + '; ' + Char(10) + 
                    'Error Severity '   + ISNULL(CAST(ERROR_SEVERITY() AS VARCHAR(10)), 'NA')   + '; ' + Char(10) +
                    'Error State '      + ISNULL(CAST(ERROR_STATE() AS VARCHAR(10)), 'NA')      + '; ' + Char(10) +
                    'Error Line '       + ISNULL(CAST(ERROR_LINE() AS VARCHAR(10)), 'NA')       + '; ' + Char(10) +
                    'Error Message '    + ISNULL(ERROR_MESSAGE(), 'NA')
        
        -- 判断事务状态，仅当事务可回滚时执行操作
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    END CATCH
    
    -- 仅当事务处于正常可提交状态时执行提交
    IF XACT_STATE() = 1
        COMMIT TRANSACTION;
END

/* 其他事务块同样按此逻辑修改 */

IF @error = 'Success' OR @error IS NULL
BEGIN
    /* 删除表的逻辑 */
END
END

XACT_STATE()取值说明：

• 1：事务正常，可提交
• -1：事务已失败，必须回滚
• 0：无活动事务

方法二：调整Python连接的自动提交设置 #

确保Python的ODBC连接开启自动提交，避免外部事务嵌套：
以pyodbc为例：

import pyodbc

# 连接时开启autocommit
conn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=MYBASE;UID=user;PWD=password",
    autocommit=True
)

# 执行存储过程
cursor = conn.cursor()
cursor.execute("""
SET NOCOUNT ON;
DECLARE @return_value INTEGER;
DECLARE @error NVARCHAR(MAX);
EXEC  @return_value = [dbo].[p_myproc] @error = @error OUTPUT;
SELECT  @error AS erreur, @return_value AS retour;
""")
result = cursor.fetchone()
print(f"Error: {result.erreur}, Return Value: {result.retour}")

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