从属过程失败时执行 (从存储过程插入)后出现神秘错误："current transaction cannot be committed"

Question

我在使用T-SQL/SQL server(2017/140)时遇到了一个问题:

在我的场景中，有许多存储过程(=工作过程)，它们依次执行不同的任务，并在最后用select语句输出结果。

上级过程(= runner过程)一个接一个地调用worker过程，并使用insert-into语句将它们的结果收集到一个表中。

工作过程中的处理使用try-/catch语句进行保护，以便在其中一个工作过程失败时不会终止运行程序。它还确保工作过程总是返回一个结果，即使这个过程在处理过程中遇到错误。

但是，如果在工作过程中发生了错误，尽管该错误已经在工作过程中被拦截和处理，但运行过程会遇到错误。错误消息是:"当前事务不能提交，不能支持写日志文件的操作。回滚事务。"从我的示例脚本中可以看到，这里没有使用显式事务。

该问题仅在使用Select-Into语句将工作过程的结果传输到表时发生。作为一个例子，我创建了另一个运行程序过程，它只执行工作过程，但不将其结果传输到表中。在这种情况下，将没有错误。

CREATE SCHEMA bugchase;
GO
DROP PROCEDURE IF EXISTS bugchase.worker_1;
DROP PROCEDURE IF EXISTS bugchase.worker_2;
DROP PROCEDURE IF EXISTS bugchase.runner_selectOnly;
DROP PROCEDURE IF EXISTS bugchase.runner_selectAndInsert;
GO
CREATE PROCEDURE bugchase.worker_1
AS
BEGIN
-- this worker just returns a value
SELECT
'Result Worker 1';
END;
GO
CREATE PROCEDURE bugchase.worker_2
AS
BEGIN
-- this worker encounters an error while processing.
-- the error will be catched and some data will be returned
DECLARE @result INT;
BEGIN TRY
-- this will force an error, because 'ABCD' could not be casted as float
SET @result = CAST('ABCD' AS FLOAT);
END TRY
BEGIN CATCH
-- just catch, don't do anything else
END CATCH;
SELECT
'Result Worker 2';
END;
GO
CREATE PROC bugchase.runner_selectAndInsert
AS
BEGIN
SET NOCOUNT ON;
DECLARE @result AS TABLE (value NVARCHAR(64));
-- exec worker_1 (no error will be thrown inside worker_1)
PRINT 'Select&Insert: Worker 1 start';
INSERT INTO @result (value)
EXEC bugchase.worker_1;
PRINT 'Select&Insert: Worker 1 end';
-- exec worker_2 (will throw and catch an error inside)
PRINT 'Select&Insert: Worker 2 start';
INSERT INTO @result (value)
EXEC bugchase.worker_2;
PRINT 'Select&Insert: Worker 2 end';
END;
GO
CREATE PROC bugchase.runner_selectOnly
AS
BEGIN
SET NOCOUNT ON;
-- exec worker_1 (no error will be thrown inside worker_1)
PRINT 'SelectOnly: Worker 1 start';
EXEC bugchase.worker_1;
PRINT 'SelectOnly: Worker 1 end';
-- exec worker_2 (will throw and catch an error inside)
PRINT 'SelectOnly: Worker 2 start';
EXEC bugchase.worker_2;
PRINT 'SelectOnly: Worker 2 end';

END;
GO
BEGIN TRY
-- because all errors are catched within the worker-procedures, there should no error occur by this call
EXEC bugchase.runner_selectAndInsert;
END TRY
BEGIN CATCH
-- indeed an error will occur while running worker 2
PRINT CONCAT('Select&Insert: ERROR:', ERROR_MESSAGE());
END CATCH;
GO
BEGIN TRY
-- for demonstration only, this will run as expected
EXEC bugchase.runner_selectOnly;
END TRY
BEGIN CATCH
-- No error will occur
PRINT CONCAT('SelectOnly: ERROR:', ERROR_MESSAGE());
END CATCH;
GO
DROP PROCEDURE bugchase.worker_2;
DROP PROCEDURE bugchase.worker_1;
DROP PROCEDURE bugchase.runner_selectOnly;
DROP PROCEDURE bugchase.runner_selectAndInsert;
DROP SCHEMA bugchase;

上面的脚本将显示以下结果:

Select&Insert: Worker 1 start
Select&Insert: Worker 1 end
Select&Insert: Worker 2 start
Select&Insert: ERROR:
The current transaction cannot be committed and cannot support operations that write to the log file.
Roll back the transaction.

当结果没有存储到表中时，没有错误发生:

SelectOnly: Worker 1 start
SelectOnly: Worker 1 end
SelectOnly: Worker 2 start
SelectOnly: Worker 2 end

Answer 1

正如Anton在评论中提到的，这是SQL Server自动提交功能的副作用，在SET IMPLICIT_TRANSACTIONS的文档中提到过。基本上，对于IMPLICIT_TRANSACTIONS OFF(默认)，有一堆语句自动封装在一个看不见的事务中。INSERT是其中之一，所以在INSERT INTO EXEC中发生的任何事情都在这些自动提交事务之一中。你可以通过在其中一个工作进程中打印或选择@@trancount来自己看到这一点。

另一个问题很好地解释了为什么即使您捕获了错误，也会出现不可提交的事务。

在您的情况下，您可以通过重写代码来解决这个问题，以从工作进程返回数据，而不使用insert into。一个简单的选择是将运行进程中的表变量替换为临时表，然后插入到工作进程中的临时表中，而不仅仅是选择结果集。

e。g runner_selectAndInsert将变成:

CREATE PROC bugchase.runner_selectAndInsert
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #result (value NVARCHAR(64));
-- exec worker_1 (no error will be thrown inside worker_1)
PRINT 'Select&Insert: Worker 1 start';
EXEC bugchase.worker_1;
PRINT 'Select&Insert: Worker 1 end';
-- exec worker_2 (will throw and catch an error inside)
PRINT 'Select&Insert: Worker 2 start';
EXEC bugchase.worker_2;
PRINT 'Select&Insert: Worker 2 end';
END;

和worker_1将变成:

CREATE PROCEDURE bugchase.worker_1
AS
BEGIN
-- this worker just returns a value
INSERT INTO #result (value)
SELECT
'Result Worker 1';
END;

像这样在进程之间传递数据的更多选项由Erland Sommarskog在这里详细说明。