SQL Server SQL事务错误：当前事务无法提交，并且不支持写入日志文件的操作

hgtggwj0 于 2023-01-29 发布在其他

关注(0)|答案(6)|浏览(1131)

我遇到了与The current transaction cannot be committed and cannot support operations that write to the log file类似的问题，但我有一个后续问题。
这里的答案引用了Using TRY...CATCH in Transact-SQL，我稍后会回到这个问题上...
我的代码（当然是继承的）具有简化形式：

SET NOCOUNT ON
SET XACT_ABORT ON

CREATE TABLE #tmp

SET @transaction = 'insert_backtest_results'
BEGIN TRANSACTION @transaction

BEGIN TRY

    --do some bulk insert stuff into #tmp

END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION @transaction
    SET @errorMessage = 'bulk insert error importing results for backtest '
        + CAST(@backtest_id as VARCHAR) +
        '; check backtestfiles$ directory for error files ' + 
        ' error_number: ' + CAST(ERROR_NUMBER() AS VARCHAR) + 
        ' error_message: ' + CAST(ERROR_MESSAGE() AS VARCHAR(200)) +
        ' error_severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +
        ' error_state ' +  CAST(ERROR_STATE() AS VARCHAR) + 
        ' error_line: ' + CAST(ERROR_LINE() AS VARCHAR)
    RAISERROR(@errorMessage, 16, 1)
    RETURN -666
END CATCH

BEGIN TRY

    EXEC usp_other_stuff_1 @whatever

    EXEC usp_other_stuff_2 @whatever

    -- a LOT of "normal" logic here... inserts, updates, etc...

END TRY

BEGIN CATCH

    ROLLBACK TRANSACTION @transaction
    SET @errorMessage = 'error importing results for backtest '
        + CAST(@backtest_id as VARCHAR) +
        ' error_number: ' + CAST(ERROR_NUMBER() AS VARCHAR) + 
        ' error_message: ' + CAST(ERROR_MESSAGE() AS VARCHAR(200)) +
        ' error_severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +
        ' error_state ' +  CAST(ERROR_STATE() AS VARCHAR) + 
        ' error_line: ' + CAST(ERROR_LINE() AS VARCHAR)
    RAISERROR(@errorMessage, 16, 1)
    RETURN -777

END CATCH

RETURN 0

我想我已经有足够的信息来处理它，然后自己解决它......不幸的是，重现错误几乎是不可能的。所以我希望在这里提问能帮助我澄清对问题和解决方案的理解。
此存储过程间歇性地抛出如下错误：
导入回溯测试结果时出错9649 error_number：3930错误消息：无法提交当前事务，并且无法支持写入日志文件的操作。请回滚事务。error_severity：16错误状态1错误行：217
很明显，错误来自第二个catch块
根据我在Using TRY...CATCH in Transact-SQL中读到的内容，我认为发生的情况是，当抛出异常时，使用XACT_ABORT会导致事务“终止并回滚”...然后BEGIN CATCH的第一行盲目地尝试再次回滚。
我不知道为什么最初的开发人员启用了XACT_ABORT，所以我认为更好的解决方案（比删除它）是使用XACT_STATE()只在有事务时回滚（<>0）。
还有，提到记录错误消息让我想知道：是否存在另一个问题，可能与配置有关？我们在此场景中使用RAISEERROR()是否导致了该问题？是否会记录该问题，在某些不可能记录的情况下，如错误消息所暗示的那样？

sql-server

来源：https://stackoverflow.com/questions/7488149/sql-transaction-error-the-current-transaction-cannot-be-committed-and-cannot-su

6条答案

按热度按时间

pb3skfrl1#

您总是需要检查XACT_STATE()，这与XACT_ABORT的设置无关。我有一个存储过程模板的示例，它需要在Exception handling and nested transactions的TRY/CATCH上下文中处理事务：

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(),
               @message = ERROR_MESSAGE(), 
               @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end

赞(0）回复(0）举报 2023-01-29

nimxete22#

在上面的讨论中有一些误解。
首先，无论事务的状态如何，您都可以ROLLBACK事务，因此只需在COMMIT之前检查XACT_STATE，而不必在回滚之前检查。
至于代码中的错误，您可能希望将事务放在TRY中，然后在CATCH中，您应该做的第一件事是：

IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION @transaction

然后，在上面的声明之后，您可以发送电子邮件或任何需要的东西。（仅供参考：如果您在回滚之前发送电子邮件，那么您肯定会收到“无法...写入日志文件”错误。）
这个问题是从去年开始的，所以我希望你现在已经解决了这个问题：-）雷穆斯为你指明了正确的方向。
根据经验......当有错误时，TRY会立即跳转到CATCH。然后，当你在CATCH中时，你可以使用XACT_STATE来决定你是否可以提交。但是如果你总是想在捕获中ROLLBACK，那么你根本不需要检查状态。

赞(0）回复(0）举报 2023-01-29

kdfy810k3#

更新启用了触发器的表中的记录时遇到此错误。例如，我在表“Table1”上具有触发器“Trigger1”。当我尝试使用更新查询更新“Table1”时，它将引发相同的错误。这是因为如果您要更新查询中的多个记录，则“Trigger1”将引发此错误，因为如果在同一个表上启用触发器，则它不支持更新多个条目。我尝试在更新前禁用触发器，然后执行更新操作，该操作已完成，没有任何错误。

DISABLE TRIGGER Trigger1 ON Table1;
Update query --------
Enable TRIGGER Trigger1 ON Table1;

赞(0）回复(0）举报 2023-01-29

q3qa4bjr4#

我遇到了与上面类似的问题，并收到了相同的错误信息。上面的答案很有帮助，但不完全是我所需要的，这实际上简单了一点。
我有一个结构如下的存储过程：

SET XACT_ABORT ON

BEGIN TRY

    --Stored procedure logic
    
    BEGIN TRANSACTION
      --Transaction logic
    COMMIT TRANSACTION

    --More stored procedure logic

END TRY

BEGIN CATCH

    --Handle errors gracefully

END CATCH

TRY...CATCH用于处理存储过程逻辑中的错误。该过程只有一部分包含事务，如果在此期间发生错误，CATCH块不会发现该错误，但会显示SQL事务错误消息。
这个问题通过添加另一个TRY...CATCH Package 器来解决，该 Package 器将ROLLBACK事务和THROW错误。这意味着此步骤中的任何错误都可以按照存储过程的其余部分在主CATCH块中优雅地处理。

SET XACT_ABORT ON

BEGIN TRY

    --Stored procedure logic
    
    BEGIN TRY
      BEGIN TRANSACTION;
        --Transaction logic
      COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
      ROLLBACK;
      THROW;
    END CATCH

    --More stored procedure logic

END TRY

BEGIN CATCH

    --Handle errors gracefully

END CATCH

赞(0）回复(0）举报 2023-01-29

fjnneemd5#

这些都没有帮助我，所以这里是什么解决了我的问题。一个队友配置了一个服务器触发器来监视DDL的变化。一旦我禁用它，我可以安装包，然后我再次启用它，包仍然工作。

赞(0）回复(0）举报 2023-01-29

uqzxnwby6#

在一个过程中出现了完全相同的错误。原来运行它的用户（在我们的例子中是一个技术用户）没有足够的权限来创建一个临时表。
执行sp_addrolember '数据库_ddladmin'，'此处为用户名';
成功了

赞(0）回复(0）举报 2023-01-29