SQL Server Nested Transaction in a loop can not committed nor rolled back to savepoint in a loop

muk1a3rh  于 2023-04-19  发布在  其他
关注(0)|答案(3)|浏览(134)

I have the following function that reads JSON files and parses them. Sometimes these files are not correct and so I don't want to take them. I tried to handle the problem with a try catch .

...
OPEN cursor_id;

FETCH NEXT FROM cursor_id INTO  @ID,@Data;
BEGIN TRAN INNEN

WHILE @@FETCH_STATUS = 0
BEGIN

    BEGIN TRY 
        SAVE TRANSACTION  INNENKLEIN
        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 INNENKLEIN
    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,
            XACT_State() as  XACT_State,
            @@TRANCOUNT  as TRANCOUNT

            IF (XACT_STATE()) = -1  ROLLBACK TRANSACTION  INNENKLEIN
            IF (XACT_STATE()) = 1   COMMIT TRANSACTION  INNENKLEIN

            PRINT '>> ERROR docid ' + cast(@ID as nvarchar) + ' Rollback'
    END CATCH

END;

COMMIT TRANSACTION

The parsing fails with the following error

Msg 13609, Level 16, State 4, Line 15
JSON text is not properly formatted. Unexpected character '<' is found at position 0.

This is what I want to handle.
Currently I already know, that this error/insert not trigger XACT_State being <> -1 and this cause the following error

Msg 3931, Level 16, State 1, Procedure dbo.ParseEinzelObjekte, Line 65
[Batch Start Line 18] The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

I also tried double nested try catch and explicitly generated errors to trigger XACT_State . That did not work either.

I need a solution handle the json error but only skip (Rollback) the faulty entry.

6jjcrrmo

6jjcrrmo1#

COMMIT TRANSACTION has an optional parameter transaction_name , but it is ignored by the SQL Server Database Engine.

Your code should have only one COMMIT TRAN after the whole loop. It is not possible to commit part of the transaction. You can only rollback part of the transaction. So, inside the loop you should be able to rollback to the savepoint.

So, remove both calls to COMMIT TRAN INNENKLEIN in your code. And add another call to

FETCH NEXT FROM cursor_id INTO  @ID,@Data;

inside the loop right before the end. The way it is written now, it will process the first row from the cursor over and over again.

...
OPEN cursor_id;

FETCH NEXT FROM cursor_id INTO  @ID,@Data;
BEGIN TRAN INNEN

WHILE @@FETCH_STATUS = 0
BEGIN

    BEGIN TRY 
        SAVE TRANSACTION  INNENKLEIN
        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) 

        -- remove this
        -- COMMIT TRAN INNENKLEIN
    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,
            XACT_State() as  XACT_State,
            @@TRANCOUNT  as TRANCOUNT

            -- rollback to the savepoint
            ROLLBACK TRANSACTION  INNENKLEIN

            -- remove this
            --IF (XACT_STATE()) = 1   COMMIT TRANSACTION  INNENKLEIN

            PRINT '>> ERROR docid ' + cast(@ID as nvarchar) + ' Rollback'
    END CATCH
    
    -- add this
    FETCH NEXT FROM cursor_id INTO  @ID,@Data;
END;

COMMIT TRANSACTION
igetnqfo

igetnqfo2#

For this stuff, i would remove all transaction code, create a @t-variable, do the inserts in a loop to the @t-variable, and just do one big insert to the real table at the end wrapped in a transaction if you really need it (i'm not sure why).

js5cn81o

js5cn81o3#

As mentioned by others, you should just remove the transaction handling altogether.

If you want to insert all the JSON in one go, you can simply use one big INSERT . Just use ISJSON to check the JSON is valid before parsing.

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
    t.ID , [element_id], [Depth], [Thepath], [ValueType], [TheValue],
    POS1, POS2, POS3, POS4, POS5, POS6, POS7, POS8, POS9, POS10, POS11, POS12  FROM (
    SELECT *
    FROM YourTable t
    WHERE ISJSON(t.Data) = 1
) t
CROSS APPLY dbo.JSONPathsAndValues(t.Data) j;

If you want to avoid blowing out your transaction log then you can continue using a cursor, but you should still remove all the transaction handling. If you have the whole thing in a transaction then it would come out to the same result as above anyway, so it makes no sense to use one in that case.

SAVE TRANSACTION does not help with keeping the transaction log under control: the outer transaction is still held open.

You can avoid the errors still by using ISJSON to check for incorrect JSON data.

相关问题