从python调用SQLServer事务时出错

Question

我面临着一个令人难以置信的令人困惑的情况，我们有一个最近更新的SQL Server，从2016年到2019年，在该SQL Server上，一个通常在一些数据集成后从python脚本调用的存储过程现在失败并出现错误。

"[25000][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]无法回滚TR_NL。找不到该名称的事务或保存点。(6401(

存储过程本身遵循相当标准的TRY/CATCH结构

USE [MYBASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_myproc] 
@error NVARCHAR(MAX)= 'Success' OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @idMarche;
DECLARE @TranName VARCHAR(20);
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'SOME_TABLE')
BEGIN 
SELECT @TranName = 'TR_NL';
BEGIN TRANSACTION @TranName;
BEGIN TRY
/*
Bunch of updates, inserts etc. Some of which conditionals with nested IF BEGIN END etc.
*/
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 @@TRANCOUNT > 0
ROLLBACK TRANSACTION @TranName;
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION @TranName;
END
/*
A few more similar blocks of conditional transactions
*/
IF @error = 'Success' OR @error IS NULL
BEGIN
/*
Drop some tables
*/
END
END

以下调用在SSMS中运行良好，但在从我的python脚本发送时失败，并出现所述错误

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;

Answer 1

问题是Python默认运行事务中的所有命令，除非您设置了autocommit=true。这意味着它正在尝试回滚您的事务，但您的错误处理程序已经完成了。

你的错误处理程序在很多方面都有缺陷：

• 如前所述，它不能很好地处理嵌套事务
• 它吞下异常，然后选择错误消息。这意味着客户端代码没有识别出发生了异常
• 如果同时存在多个错误(DBCC和BACKUP命令常见(，则只返回一个错误

相反，只需在过程的顶部使用SET XACT_ABORT ON;，如果需要进行任何清理，请确保使用THROW;重新抛出原始异常。如果不回滚事务，它将自动回滚。

CREATE OR ALTER PROCEDURE [dbo].[p_myproc]
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY;
IF EXISTS(SELECT 1
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'SOME_TABLE')
BEGIN 
BEGIN TRANSACTION;
/*
Bunch of updates, inserts etc. Some of which conditionals with nested IF BEGIN END etc.
*/
COMMIT TRANSACTION;
END;
/*
A few more similar blocks of conditional transactions
*/
/*
Drop some tables
*/
END TRY
BEGIN CATCH
-- do cleanup. Do NOT rollback
;
THROW;   -- rethrows the original exception
END CATCH;

如果不需要清理，则根本不要使用CATCH。XACT_ABORT无论如何都会回滚事务。

CREATE OR ALTER PROCEDURE [dbo].[p_myproc]
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

IF EXISTS(SELECT 1
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'SOME_TABLE')
BEGIN 
BEGIN TRANSACTION;
/*
Bunch of updates, inserts etc. Some of which conditionals with nested IF BEGIN END etc.
*/
COMMIT TRANSACTION;
END
/*
A few more similar blocks of conditional transactions
*/
/*
Drop some tables
*/

另请参阅以下链接

• 如何正确使用SET XACT_ABORT
• 当XACT_ABORT打开时，TRY CATCH块的点是什么
• 在存储过程中使用多个相关语句