You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

嵌套事务场景下SQL存储过程报Msg 3930错误求助

avatar
AI 小北
2026-7-6
问题

编写的数据迁移SQL存储过程在使用嵌套事务时触发错误，错误信息如下：

Msg 3930, Level 16, State 1, Line 144
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

存储过程代码：

/*
DROP TABLE #Temp
DROP TABLE #Inserted
*/
--SELECT * FROM HeapTempLogStep AS htls
--select * FROM TempLogStep AS tls

BEGIN
    SET NOCOUNT ON
    DECLARE @Id bigint ,
    @TrackToken uniqueidentifier ,
    @BusinessKey varchar(50) ,
    @BusinessKeyName varchar(50) ,
    @ActionCode tinyint ,
    @StepCode tinyint ,
    @AspectLevelCode tinyint ,
    @CreationDate datetime2(7) ,
    @BrokerId int,
    @UserId int ,
    @CustomerId int ,
    @IpAddress varchar(50) ,
    @Body Nvarchar(MAX) ,
    @Result varchar(MAX) ,
    @PriorityCode tinyint ,
    @HashCode VARBINARY(MAX),
    @TempLogStepId BIGINT,
    @LastHashCode VARBINARY(MAX),
    @LastLogStepId BIGINT
    
    SELECT [Id]
      ,[TrackToken]
      ,[BusinessKey]
      ,[BusinessKeyName]
      ,[ActionCode]
      ,[StepCode]
      ,[AspectLevelCode]
      ,[CreationDate]
      ,[BrokerId]
      ,[UserId]
      ,[CustomerId]
      ,[IpAddress]
      ,Body = [Body] COLLATE Persian_100_CI_AI
      ,[Result]
      ,[PriorityCode]
    INTO #Temp 
    FROM dbo.HeapTempLogStep 
    ORDER BY Id
    
    CREATE TABLE #Inserted
    (
        Id bigint,
        TrackToken uniqueidentifier
    )
    set XACT_ABORT  ON 
    
    BEGIN TRANSACTION Transport_data_from_Heap
    
    SET @TempLogStepId = ISNULL((SELECT MAX(LastId) FROM dbo.LastRecordTempLogStep),0)
    SET @LastLogStepId = ISNULL((SELECT MAX(Id) FROM dbo.LogStep),0)
    
    IF (@TempLogStepId < @LastLogStepId)
        BEGIN
            IF(@TempLogStepId = 0)
                BEGIN
                    INSERT INTO dbo.LastRecordTempLogStep (LastUpdateTime,LastId) VALUES (sysdatetime(), @LastLogStepId)
                    SET @LastHashCode = 0   
                END
            ELSE
                BEGIN
                    UPDATE dbo.LastRecordTempLogStep SET LastUpdateTime = sysdatetime(), LastId = @LastLogStepId
                    SELECT @LastHashCode = CONVERT(VARBINARY(MAX),HashCode,0)  FROM LogStep WHERE Id = @TempLogStepId
                END
            SET @TempLogStepId = @LastLogStepId
        END
    ELSE
        BEGIN
            SELECT @LastHashCode = CONVERT(VARBINARY(MAX),HashCode,0)  FROM TempLogStep WHERE Id = @TempLogStepId
            IF(LEN(@LastHashCode) <= 0)
            BEGIN
                SELECT @LastHashCode = CONVERT(VARBINARY(MAX),HashCode,0)  FROM LogStep WHERE Id = @TempLogStepId
            END
        END
    
    DECLARE trasport_cursor CURSOR FOR
    SELECT [Id]
      ,[TrackToken]
      ,[BusinessKey]
      ,[BusinessKeyName]
      ,[ActionCode]
      ,[StepCode]
      ,[AspectLevelCode]
      ,[CreationDate]
      ,[BrokerId]
      ,[UserId]
      ,[CustomerId]
      ,[IpAddress]
      ,[Body] COLLATE Persian_100_CI_AI
      ,[Result]
      ,[PriorityCode]
    FROM #Temp
    ORDER BY Id
    
    OPEN trasport_cursor
    
    FETCH NEXT FROM trasport_cursor
    INTO @Id, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
         @UserId ,@CustomerId ,@IpAddress ,@Body ,@Result ,@PriorityCode 
         
    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            BEGIN TRAN Insert_Tran
            SAVE TRANSACTION SavePoint1

            SET @HashCode = HASHBYTES('SHA2_256', CONCAT(@LastHashCode , CAST(@ActionCode AS VARCHAR(MAX)) , CAST(@StepCode AS VARCHAR(MAX)) , CAST(@AspectLevelCode AS VARCHAR(MAX)) , CAST(@PriorityCode AS VARCHAR(MAX))
            , CAST(@BrokerId AS VARCHAR(MAX)) , @BusinessKey , @BusinessKeyName , CAST(@CustomerId AS VARCHAR(MAX)) , CAST(@UserId AS VARCHAR(MAX)) , CAST(@IpAddress AS VARCHAR(MAX)) , CAST(@TrackToken AS VARCHAR(MAX)) ,
            (@Body COLLATE SQL_Latin1_General_CP1_CI_AS) , (@Result COLLATE SQL_Latin1_General_CP1_CI_AS))) 
            
            SET @TempLogStepId = @TempLogStepId + 1
            
            INSERT INTO [dbo].[TempLogStep]
            VALUES(@TempLogStepId, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
                   @UserId ,@CustomerId ,@IpAddress ,@Body COLLATE Persian_100_CI_AI ,@Result,sysdatetime() ,@PriorityCode ,CONVERT(VARCHAR(MAX),@HashCode,1))
            PRINT 'successful insert ' + CONVERT(VARCHAR(MAX),@TrackToken)
            INSERT INTO #Inserted
            VALUES (@Id,@TrackToken)
            
            UPDATE dbo.LastRecordTempLogStep SET LastUpdateTime = sysdatetime(), LastId = @TempLogStepId
            
            SET @LastHashCode = @HashCode   

            COMMIT TRAN Insert_Tran
        END TRY
        BEGIN CATCH
            PRINT 'Get error ' + CONVERT(VARCHAR(MAX),@TrackToken)
            PRINT @@TRANCOUNT
            PRINT XACT_STATE()
            IF ((XACT_STATE() = -1) OR (XACT_STATE() = 1 AND @@TRANCOUNT >= 0))
            BEGIN
                COMMIT TRAN Insert_Tran
                ROLLBACK TRAN SavePoint1
                
                PRINT 'Rollback Succeed ' + CONVERT(VARCHAR(MAX),@TrackToken)
                PRINT @@TRANCOUNT
            END

            FETCH NEXT FROM trasport_cursor
            INTO @Id, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
                 @UserId ,@CustomerId ,@IpAddress ,@Body ,@Result ,@PriorityCode
            
        END CATCH
        
        FETCH NEXT FROM trasport_cursor
        INTO @Id, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
             @UserId ,@CustomerId ,@IpAddress ,@Body ,@Result ,@PriorityCode
    END
    CLOSE trasport_cursor
    DEALLOCATE trasport_cursor
    
    
    
    DELETE A
    FROM dbo.HeapTempLogStep A 
    INNER JOIN #Inserted B ON A.Id = B.Id AND A.TrackToken = B.TrackToken
    
    --RAISERROR('test error',16,1)
    
    PRINT @@TRANCOUNT
    PRINT XACT_STATE()
    IF @@ERROR <> 0
        IF (@@TRANCOUNT > 0)    
            ROLLBACK TRAN
    ELSE IF ((XACT_STATE() = -1) OR (XACT_STATE() = 1 AND @@TRANCOUNT >= 0))
        COMMIT TRAN 
        
    DROP TABLE #Temp
    DROP TABLE #Inserted
END;
问题分析与解决方案

核心问题点

  • 嵌套事务逻辑错误：SQL Server没有真正的嵌套事务，BEGIN TRAN只是增加事务计数。当外部事务存在时，内部事务的提交/回滚操作仅改变计数，无法独立控制。一旦内部操作导致事务进入不可提交状态（XACT_STATE() = -1），后续任何写日志的操作都会触发3930错误。
  • 错误处理操作无效：CATCH块中尝试提交内部事务、回滚到保存点的逻辑矛盾，事务不可提交时只能回滚整个外部事务，无法单独处理局部操作。
  • 游标重复FETCH：CATCH块和主循环末尾各执行一次FETCH，导致错误时跳过一条数据，逻辑不符合预期。

修正后的存储过程代码 

/*
DROP TABLE #Temp
DROP TABLE #Inserted
*/
--SELECT * FROM HeapTempLogStep AS htls
--select * FROM TempLogStep AS tls

BEGIN
    SET NOCOUNT ON
    DECLARE @Id bigint ,
    @TrackToken uniqueidentifier ,
    @BusinessKey varchar(50) ,
    @BusinessKeyName varchar(50) ,
    @ActionCode tinyint ,
    @StepCode tinyint ,
    @AspectLevelCode tinyint ,
    @CreationDate datetime2(7) ,
    @BrokerId int,
    @UserId int ,
    @CustomerId int ,
    @IpAddress varchar(50) ,
    @Body Nvarchar(MAX) ,
    @Result varchar(MAX) ,
    @PriorityCode tinyint ,
    @HashCode VARBINARY(MAX),
    @TempLogStepId BIGINT,
    @LastHashCode VARBINARY(MAX),
    @LastLogStepId BIGINT,
    @FetchStatus INT
    
    SELECT [Id]
      ,[TrackToken]
      ,[BusinessKey]
      ,[BusinessKeyName]
      ,[ActionCode]
      ,[StepCode]
      ,[AspectLevelCode]
      ,[CreationDate]
      ,[BrokerId]
      ,[UserId]
      ,[CustomerId]
      ,[IpAddress]
      ,Body = [Body] COLLATE Persian_100_CI_AI
      ,[Result]
      ,[PriorityCode]
    INTO #Temp 
    FROM dbo.HeapTempLogStep 
    ORDER BY Id
    
    CREATE TABLE #Inserted
    (
        Id bigint,
        TrackToken uniqueidentifier
    )
    SET XACT_ABORT ON 
    
    BEGIN TRANSACTION Transport_data_from_Heap
    
    SET @TempLogStepId = ISNULL((SELECT MAX(LastId) FROM dbo.LastRecordTempLogStep),0)
    SET @LastLogStepId = ISNULL((SELECT MAX(Id) FROM dbo.LogStep),0)
    
    IF (@TempLogStepId < @LastLogStepId)
        BEGIN
            IF(@TempLogStepId = 0)
                BEGIN
                    INSERT INTO dbo.LastRecordTempLogStep (LastUpdateTime,LastId) VALUES (sysdatetime(), @LastLogStepId)
                    SET @LastHashCode = 0   
                END
            ELSE
                BEGIN
                    UPDATE dbo.LastRecordTempLogStep SET LastUpdateTime = sysdatetime(), LastId = @LastLogStepId
                    SELECT @LastHashCode = CONVERT(VARBINARY(MAX),HashCode,0)  FROM LogStep WHERE Id = @TempLogStepId
                END
            SET @TempLogStepId = @LastLogStepId
        END
    ELSE
        BEGIN
            SELECT @LastHashCode = CONVERT(VARBINARY(MAX),HashCode,0)  FROM TempLogStep WHERE Id = @TempLogStepId
            IF(LEN(@LastHashCode) <= 0)
            BEGIN
                SELECT @LastHashCode = CONVERT(VARBINARY(MAX),HashCode,0)  FROM LogStep WHERE Id = @TempLogStepId
            END
        END
    
    DECLARE trasport_cursor CURSOR FOR
    SELECT [Id]
      ,[TrackToken]
      ,[BusinessKey]
      ,[BusinessKeyName]
      ,[ActionCode]
      ,[StepCode]
      ,[AspectLevelCode]
      ,[CreationDate]
      ,[BrokerId]
      ,[UserId]
      ,[CustomerId]
      ,[IpAddress]
      ,[Body] COLLATE Persian_100_CI_AI
      ,[Result]
      ,[PriorityCode]
    FROM #Temp
    ORDER BY Id
    
    OPEN trasport_cursor
    
    FETCH NEXT FROM trasport_cursor
    INTO @Id, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
         @UserId ,@CustomerId ,@IpAddress ,@Body ,@Result ,@PriorityCode 
    SET @FetchStatus = @@FETCH_STATUS
    
    WHILE @FetchStatus = 0
    BEGIN
        BEGIN TRY
            -- 用保存点替代内部事务，依托外部事务实现局部回滚
            SAVE TRANSACTION SavePoint1

            SET @HashCode = HASHBYTES('SHA2_256', CONCAT(@LastHashCode , CAST(@ActionCode AS VARCHAR(MAX)) , CAST(@StepCode AS VARCHAR(MAX)) , CAST(@AspectLevelCode AS VARCHAR(MAX)) , CAST(@PriorityCode AS VARCHAR(MAX))
            , CAST(@BrokerId AS VARCHAR(MAX)) , @BusinessKey , @BusinessKeyName , CAST(@CustomerId AS VARCHAR(MAX)) , CAST(@UserId AS VARCHAR(MAX)) , CAST(@IpAddress AS VARCHAR(MAX)) , CAST(@TrackToken AS VARCHAR(MAX)) ,
            (@Body COLLATE SQL_Latin1_General_CP1_CI_AS) , (@Result COLLATE SQL_Latin1_General_CP1_CI_AS))) 
            
            SET @TempLogStepId = @TempLogStepId + 1
            
            INSERT INTO [dbo].[TempLogStep]
            VALUES(@TempLogStepId, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
                   @UserId ,@CustomerId ,@IpAddress ,@Body COLLATE Persian_100_CI_AI ,@Result,sysdatetime() ,@PriorityCode ,CONVERT(VARCHAR(MAX),@HashCode,1))
            PRINT 'successful insert ' + CONVERT(VARCHAR(MAX),@TrackToken)
            INSERT INTO #Inserted
            VALUES (@Id,@TrackToken)
            
            UPDATE dbo.LastRecordTempLogStep SET LastUpdateTime = sysdatetime(), LastId = @TempLogStepId
            
            SET @LastHashCode = @HashCode   
        END TRY
        BEGIN CATCH
            PRINT 'Get error ' + CONVERT(VARCHAR(MAX),@TrackToken)
            PRINT @@TRANCOUNT
            PRINT XACT_STATE()
            
            -- 根据事务状态分支处理
            IF XACT_STATE() = -1
            BEGIN
                -- 事务不可提交，直接回滚整个外部事务并终止流程
                PRINT 'Transaction is uncommittable, rolling back entire transaction'
                ROLLBACK TRAN Transport_data_from_Heap
                BREAK
            END
            ELSE IF XACT_STATE() = 1
            BEGIN
                -- 事务可提交，仅回滚到保存点，跳过当前错误数据
                ROLLBACK TRAN SavePoint1
                PRINT 'Rollback to savepoint succeed ' + CONVERT(VARCHAR(MAX),@TrackToken)
            END
        END CATCH
        
        -- 统一在循环末尾执行FETCH，避免重复操作导致跳行
        FETCH NEXT FROM trasport_cursor
        INTO @Id, @TrackToken, @BusinessKey,@BusinessKeyName ,@ActionCode ,@StepCode ,@AspectLevelCode ,@CreationDate ,@BrokerId ,
             @UserId ,@CustomerId ,@IpAddress ,@Body ,@Result ,@PriorityCode 
        SET @FetchStatus = @@FETCH_STATUS
    END
    CLOSE trasport_cursor
    DEALLOCATE trasport_cursor
    
    -- 仅当事务处于可提交状态时，执行删除并提交主事务
    IF XACT_STATE() = 1
    BEGIN
        DELETE A
        FROM dbo.HeapTempLogStep A 
        INNER JOIN #Inserted B ON A.Id = B.Id AND A.TrackToken = B.TrackToken
        
        COMMIT TRAN Transport_data_from_Heap
        PRINT 'Main transaction committed'
    END
    ELSE
    BEGIN
        PRINT 'Main transaction rolled back due to errors'
    END
        
    DROP TABLE #Temp
    DROP TABLE #Inserted
END;

关键修改说明

  • 移除内部事务：依托外部主事务，用保存点实现单条数据操作的局部回滚，避免事务计数混乱。
  • 修正错误处理逻辑：根据XACT_STATE()的返回值精准处理：不可提交时直接回滚整个事务；可提交时仅回滚到保存点，继续处理后续数据。
  • 统一游标FETCH：将FETCH操作移到循环末尾，解决错误时跳行的问题。
  • 优化最终提交判断：仅在事务可提交时执行删除和提交操作，避免无效的事务操作。

内容的提问来源于stack exchange，提问作者Mohammad Safyar

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.06 15:25:54