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

问：

我遇到了与当前事务无法提交且无法支持写入日志文件的操作类似的问题，但我有一个后续问题。

那里的答案参考了使用 TRY...Transact-SQL 中的 CATCH，我稍后会回到...

我的代码（当然是继承的）具有简化形式：

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_message：当前事务无法提交，并且不支持写入日志文件的操作。回滚事务。error_severity： 16 error_state 1 error_line：217

所以很明显，错误来自第二个捕获块

根据我在使用 TRY 中读到的内容...Transact-SQL中的CATCH，我认为正在发生的事情是，当抛出异常时，使用XACT_ABORT会导致事务“终止并回滚”......然后第一行盲目地试图再次回滚。BEGIN CATCH

我不知道为什么原始开发人员启用了，所以我认为更好的解决方案（而不是删除它）是仅在有事务（）时才用于回滚。这听起来合理吗？我错过了什么吗？XACT_ABORTXACT_STATE()<>0

此外，错误消息中提到登录使我想知道：是否存在另一个问题，可能与配置有关？我们在这种情况下的使用是否导致了这个问题？在某种无法记录的情况下，是否会像错误消息所暗示的那样被记录下来？RAISEERROR()

sql-server sql-server-2005 事务

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

@Adam：这是关于你如何处理 CATCH 区块中的交易。您可以使用此模板在一笔交易中拥有多个尝试块。这个想法是了解事务和捕获块如何交互，作为奖励，您还可以处理嵌套事务和保存点，这在批处理中非常有用，因为它提供了恢复批处理其余部分的能力，即使一个条目失败。XACT_STATE

1赞 mwigdahl 4/22/2017

这个答案值得比它更多的赞成票。该模板非常清晰和有用，其适用性超出了这个问题。

1赞 Remus Rusanu 9/20/2018

@DevinLamothe，因为稍后会再次检查该变量

2赞 Adam Plocher 1/16/2020

我们有一个过程，它有一些错误记录，但没有明确的事务本身概念。它是从另一个包含事务的进程调用的（特别是 tSQLt 单元测试）。第一个进程中的错误日志记录收到错误“无法提交当前事务...”。您是否建议我们让第一个进程意识到它可以在事务中使用以解决此问题的事实？谢谢catch

20赞 Donna_123_a 5/8/2012 #2

在上面的讨论中存在一些误解。

首先，您始终可以回滚事务...无论交易的状态如何。因此，您只需要在提交之前检查XACT_STATE，而不是在回滚之前检查。

至于代码中的错误，您需要将事务放在 TRY 中。然后在您的 CATCH 中，您应该做的第一件事是：

 IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION @transaction

然后，在上面的声明之后，您可以发送电子邮件或任何需要的东西。（仅供参考：如果您在回滚之前发送电子邮件，那么您肯定会收到“无法...写入日志文件“错误。

这个问题是从去年开始的，所以我希望你现在已经解决了这个问题：-）莱姆斯为你指明了正确的方向。

根据经验...当出现错误时，TRY 将立即跳转到 CATCH。然后，当您处于 CATCH 中时，您可以使用XACT_STATE来决定是否可以提交。但是，如果您总是想在捕获中回滚，那么您根本不需要检查状态。

虽然这个主题非常微妙;“您始终可以回滚交易”的说法是不正确的 - 至少使用给定的示例。如果 XACT_ABORT 标志为 ON，则应在回滚之前检查XACT_STATE。例如，如果在 XACT_ABORT ON 的情况下创建命名事务，则随后回滚命名事务（如示例所示）将导致错误。在这种情况下，必须检查XACT_STATE以衡量要执行的命令。如图所示简单地检查@@TRANCOUNT将导致错误。

-2赞 lucian 10/19/2020 #3

在程序中出现完全相同的错误。事实证明，运行它的用户（在我们的例子中是技术用户）没有足够的权限来创建临时表。

EXEC sp_addrolemember 'db_ddladmin'， 'username_here';

成功了

0赞 chanakya 12/30/2020 #4

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

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

0赞 durkules 1/6/2022 #5

我遇到了与上述类似的问题，并收到了相同的错误消息。上面的答案很有帮助，但不是我需要的，这实际上更简单一些。

我有一个存储过程，其结构如下：

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用于处理存储过程逻辑中的错误。该过程只有一个部分包含一个事务，如果在此过程中发生错误，它不会被块拾取，而是会出错并显示 SQL 事务错误消息。CATCH

这是通过添加另一个包装器来解决的，该包装器将处理事务和错误。这意味着此步骤中的任何错误都可以在主块中正常处理，就像存储过程的其余部分一样。TRY...CATCHROLLBACKTHROWCATCH

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

我们收到这个错误，并认为它与开始和结束块有关，以及过程中的 Try 和 Commit。当我们从过程块和开始/结束块中取出部分并分别运行它们时，我们发现这是一个简单的不兼容数据转换问题，因为馈线系统改变了它一直以来的输入。所以可以肯定的是，它可能是你的开始/结束/尝试的编码，但在我们的例子中，根本原因不是错误消息中的日志记录错误。为什么数据转换错误没有先出现，我不知道。如果您的进程已经运行了很长时间并且突然开始抛出海报的错误消息，而不是在开发周期中，我会特别怀疑我的根本原因。

0赞 SQLpro 11/8/2023 #8

事务的定义是，事务范围内的所有代码都必须是原子的，它由一个代码单元组成，要完成直到结束（COMMIT）或反转到开始（ROLLBACK）。

有了这个定义，是否有可能在原子事务中有一个部分事务???

当然不是！

但问题是，当一个具有事务的过程调用另一个同样具有自己的事务的过程时。会发生什么？我该如何管理？以伪代码为例：

CREATE PROCEDURE P_ADD_PHONE ....
...
BEGIN TRANSACTION
....
COMMIT
...
GO

CREATE PROCEDURE P_ADD_ADDRESS ...
...
BEGIN TRANSACTION
....
COMMIT
...
GO

CREATE PROCEDURE P_ADD_PERSONNE ...
...
BEGIN TRAN
...
EXEC P_ADD_PHONE .... 
...
EXEC P_ADD_ADDRESS ...
...
COMMIT
...
GO

你必须明白，关于交易有两个概念：

隐式事务：放置 BEGIN TRAN / { COMMIT / ROLLBACK } 任何修改或读取（1）数据库的命令（CREATE、ALTER、DROP、选择、插入、更新、删除、授予、撤销......
EXPLICIT 事务：您必须编写自己的 BEGIN TRAN 和 finalyze 随心所欲地使用 COMMIT 或 ROLLBACK。

使用嵌套事务时，BEGIN TRANSACTION 将事务状态从 IMPLICIT 提升为 EXPLICIT，并将 @@TRANCOUNT 设置为 1。任何新的 BEGIN TRANSACTION 都执行几乎相同的操作，但在本例中，从 EXPLICIT 提升到 EXPLICIT 除了将 @@TRANCOUNT递增 1 之外，不会执行其他操作。

完成 COMMIT 后，@@TRANCOUNT减少 1，如果 @@TRANCOUNT si 0，则完成有效的 COMMIT。在其他情况下，COMMIT 除了 decraese 之外什么都不做......

完成 ROLLBACK 后，将立即完成 ROLLBACK，并将 @@TRANCOUNT 设置为 0。但是，当事务状态不在 EXPLICIT 级别（@@TRANCOUNT = 0）时，ROLLBACK 会发出错误，因为没有要 ROOLBACK 的事务。

这种行为被称为 RDBMS litterature 作为嵌套事务的非对称模型（2）...在这个模型中，如果你想提交所有嵌套的事务，你需要执行与你执行的BEGIN TRANSACTION一样多的COMMIT，但第一个ROLLBACK立即结束了excplicit事务状态...通过比较，想想一列必须经过伦敦、巴黎、柏林、维也纳的火车。如果每个间隔都是一个子事务，那么当一切顺利（COMMIT）时，火车就可以安全到达终点......但是，如果在到车站之间的任何间隔内出现任何问题，您是否想继续在失效的火车中运输???

顺便说一句，认为事务的构想（impicit vs explicit）只是用户会话的一个状态，可以通过函数 XACT_STATE（）...XACT 是指用户的标准 ISO SQL 伪名 eXplicit transACTion。

（1）是的，事务也用于读取数据。例如，如果在一个巨大的数据集（数十亿行）中只在一行中完成除以零，则结果将根本没有行！但是在 READ 事务的情况下，不使用事务日志...

（2）在嵌套事务的对称模型中，每个BEGIN TRANSACTION必须有其对应的COMMIT或ROLLBACK...然后只有最终的 COMMIT 或 ROLLBACK 才真正应用......这是无稽之谈！

上一个：Snowflake // 如何计算 DEV 和 PROD 数据库给定架构中每个视图的记录

下一个：自定义光标滚动，除非移动鼠标 HTML、CSS、JS

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

SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file

评论

评论

评论

评论