时间戳

首页 > 解决方案 > 当从属过程失败时执行进入（从存储过程插入）后出现神秘错误：“当前事务无法提交”

sql-server - 当从属过程失败时执行进入（从存储过程插入）后出现神秘错误：“当前事务无法提交”

问题描述

我在使用 T-SQL / SQL server (2017/140) 时遇到问题：

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

上级过程（= 运行过程）一个接一个地调用工作过程，并使用 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

标签： sql-serverstored-proceduresinsertsql-insert

解决方案

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

This other question有一些很好的解释为什么即使您发现错误也会发生不可提交的事务。

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

例如 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在这里充实了一些在 proc 之间传递数据的更多选项。

推荐阅读