循环中嵌套事务无法提交/回滚至保存点，求JSON错误处理方案

你的核心问题是：处理JSON解析错误时，外部大事务+保存点的方式会导致整个事务进入不可提交状态，无法仅跳过单个错误条目。这是因为JSON格式错误（错误13609）属于严重级别错误，会直接污染整个外层事务，导致无法通过保存点回滚局部操作，进而触发3931错误。

解决方案1：提前验证JSON有效性（推荐） #

用ISJSON()函数在解析前先检查JSON格式是否合法，直接过滤掉错误条目，从根源避免触发解析错误。修改后的代码如下：

OPEN cursor_id;

FETCH NEXT FROM cursor_id INTO @ID,@Data;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 先验证JSON格式是否有效
    IF ISJSON(@Data) = 1
    BEGIN
        BEGIN TRY
            INSERT INTO EinzelObjekteGeparst WITH (TABLOCK HOLDLOCK)
                (docid, [element_id], [Depth], [Thepath], [ValueType], [TheValue], POS1,POS2,POS3,POS4,POS5,POS6,POS7,POS8,POS9,POS10,POS11,POS12)
            SELECT  @ID , [element_id], [Depth], [Thepath], [ValueType], [TheValue], POS1,POS2,POS3,POS4,POS5,POS6,POS7,POS8,POS9,POS10,POS11,POS12  
            FROM dbo.JSONPathsAndValues(@Data) 
        END TRY
        BEGIN CATCH
            -- 记录解析过程中的其他非格式类错误
            SELECT
                @ID AS DocID,
                ERROR_NUMBER() AS ErrorNumber,
                ERROR_SEVERITY() AS ErrorSeverity,
                ERROR_STATE() AS ErrorState,
                ERROR_PROCEDURE() AS ErrorProcedure,
                ERROR_LINE() AS ErrorLine,
                ERROR_MESSAGE() AS ErrorMessage

            PRINT '>> ERROR docid ' + CAST(@ID AS NVARCHAR) + ' 解析失败，已跳过'
        END CATCH
    END
    ELSE
    BEGIN
        -- 记录JSON格式错误
        SELECT
            @ID AS DocID,
            13609 AS ErrorNumber,
            16 AS ErrorSeverity,
            4 AS ErrorState,
            NULL AS ErrorProcedure,
            NULL AS ErrorLine,
            'JSON text is not properly formatted.' AS ErrorMessage

        PRINT '>> ERROR docid ' + CAST(@ID AS NVARCHAR) + ' JSON格式错误，已跳过'
    END

    FETCH NEXT FROM cursor_id INTO @ID,@Data;
END;

解决方案2：每个条目使用独立事务 #

如果需要处理ISJSON()无法检测到的解析错误，可将每个条目的操作放到独立事务中，这样单个条目的错误只会回滚自身事务，不会影响后续条目：

OPEN cursor_id;

FETCH NEXT FROM cursor_id INTO @ID,@Data;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        BEGIN TRAN; -- 为单个条目开启独立事务
        INSERT INTO EinzelObjekteGeparst WITH (TABLOCK HOLDLOCK)
            (docid, [element_id], [Depth], [Thepath], [ValueType], [TheValue], POS1,POS2,POS3,POS4,POS5,POS6,POS7,POS8,POS9,POS10,POS11,POS12)
        SELECT  @ID , [element_id], [Depth], [Thepath], [ValueType], [TheValue], POS1,POS2,POS3,POS4,POS5,POS6,POS7,POS8,POS9,POS10,POS11,POS12  
        FROM dbo.JSONPathsAndValues(@Data) 
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        -- 回滚当前条目的事务，不影响后续处理
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN;

        SELECT
            @ID AS DocID,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage

        PRINT '>> ERROR docid ' + CAST(@ID AS NVARCHAR) + ' 处理失败，已回滚并跳过'
    END CATCH

    FETCH NEXT FROM cursor_id INTO @ID,@Data;
END;

为什么原方案失效？ #

原代码中你在外层开启了一个大事务BEGIN TRAN INNEN，当JSON解析错误触发13609错误时，SQL Server会将整个外层事务标记为不可提交状态（XACT_STATE() = -1），此时无法通过回滚保存点来恢复事务，只能回滚整个外层事务，导致后续所有条目都无法处理。

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